標題: LingQ to sql analysis [打印本頁]

---

作者: staymen    時間: 2020-12-25 23:10     標題: LingQ to sql analysis

Linqpad 好似入唔到include咁

Context.post
       .Include(x => x.Children)
       .AsEnumerable()
       .Where(x => x.id == 5)
       .ToList();

我想知佢係load 所有post 先找Id 5.
還是點?
最好就是先找到id 5 post, 再找下邊children.

唔太熟linq .

---

作者: 7h1r733n    時間: 2020-12-28 14:46


= select Children from Post Where Id=5... EF Framework其實到尾都係gen一句sql出黎.. 一個linq statement出一個sql query..

---

作者: staymen    時間: 2020-12-31 12:42

回覆 2# 7h1r733n

咁寫會成個tree 拎黎出黎.        .AsEnumerable(), 但就唔知佢做左咩.
Context.post
       .Include(x => x.Children)
       .AsEnumerable()
       .Where(x => x.id == 5)
       .ToList();

---

作者: blue01299    時間: 2020-12-31 14:58

你咁寫其實就係即係select+join哂所有野去client, 之後client做filtering
咁寫一多data爆硬

你del左個AsEnumerable就得

---

作者: 7h1r733n    時間: 2021-1-3 05:16

本帖最後由 7h1r733n 於 2021-1-3 05:17 編輯


https://stackoverflow.com/questi ... ata-from-the-server

其中一個reply 有講到呢樣嘢...

In fact, even the calls to IEnumerable<T>.Where() and IEnumerable<T>.Take() don't actually start fetching any rows at that moment. They simply setup wrapping IEnumerables that will filter results as they are iterated on. The fetching and iterating of the results really only begins when ToList() is called.

---

作者: 7h1r733n    時間: 2021-1-3 05:59




The AsEnumerable() operator, unlike ToList() and ToArray(), does not cause execution of the query. It is still deferred. The AsEnumerable() operator merely changes the static typing of the query, turning a IQueryable<T> (IQueryable (ofT) in Visual Basic) into an IEnumerable<T> (IEnumerable (ofT) in Visual Basic), tricking the compiler into treating the rest of the query as locally executed.

source: https://docs.microsoft.com/en-us ... redirectedfrom=MSDN

---

作者: 7h1r733n    時間: 2021-1-4 17:32

本帖最後由 7h1r733n 於 2021-1-4 17:33 編輯


帶返個頭盔先... 上面佢咁寫我咁睇....
不過一般黎講... 唔會咁寫..
通常都係context.enities.Where(x => x.Id == 1).ToList(); 就算..

---

作者: antlee    時間: 2021-1-5 00:30



Linq根基: extension method, 可以被overload 多個版本: IEnumerable<T>/IQueryable<T>/IOrderedQueryable<T>/etc.
呢個AsEnumerable只係確保後面Where嘅extension method係IEnumerable<T>.Where method, 副作用會斷開 linq SQL query, 導致實際執行ToList時向SQL Server取得全部資料(!)後再自己行IEnumerable<T>.Where逐個record去filter id=5嘅record入list...

正解就係移除 .AsEnumerable()個行令Where行其他overload版本(IQueryable<T>/IOrderedQueryable<T>/etc.)
p.s. .Include 嗰part...: https://stackoverflow.com/questi ... -include-do-in-linq

---

作者: staymen    時間: 2021-1-5 16:17

回覆 8# antlee

但係如果要成個TREE 拎, 如ID=5 以下TREE ,有無更好寫法?

---

作者: 7h1r733n    時間: 2021-1-5 16:25


https://entityframeworkcore.com/querying-data-joining

---

作者: staymen    時間: 2021-1-5 16:31

回覆 10# 7h1r733n

但係唔知TREE, 有幾多層,唔知JOIN幾次ＴＡＢＬＥ

---

作者: 7h1r733n    時間: 2021-1-5 16:43



吓... 你自己data都唔知有幾多層...

---

作者: antlee    時間: 2021-1-5 20:33


有無要self-join嘅部分? 要搞清楚原本條SQL query係乜先
https://stackoverflow.com/questi ... recursive-self-join

---

作者: staymen    時間: 2021-1-6 01:35

回覆 12# 7h1r733n

self join Post Table,  如果唔知tree 有幾層.  幾多層Child 就有幾多層咁

---

作者: staymen    時間: 2021-1-6 01:35

本帖最後由 staymen 於 2021-1-6 01:51 編輯

回覆 13# antlee

yes self join

self join Post Table,  如果唔知tree 有幾層.  幾多層Child 就有幾多層咁
找就找到這個:
但就係以上有人講會全部 RECORD, All record of Table Post, 用SQL拎曬出黎, store in memory,再filter?  Then ToList(). 建立Object然後多RECORD 就死得.
https://entityframeworkcore.com/ ... e-to-ef-core-entity

public override IEnumerable<Post> GetAll()
{
    return Table
       .Include(x => x.Children)
       .AsEnumerable()
       .Where(x => x.Parent == null)
       .ToList();
}

所以都係要直接run SQL 比較好? LINQ 處理 self ref parent/child  tree structure 就死得?

---

作者: antlee    時間: 2021-1-6 13:50

本帖最後由 antlee 於 2021-1-6 14:14 編輯

回覆 15# staymen
只係recursive self join唔覺有SQL標準可跟, linq to SQL就...
MS SQLServer/T-SQL做法係recursive CTE(寫得唔好會infinite loop)
https://docs.microsoft.com/en-us ... iew=sql-server-2017
p.s.坊間或者有其他解法,不過未必apply到 ... e.g. https://blog.darkthread.net/blog/col-merge-benchmark/

你個case(成個TREE 拎, 如ID=5 以下TREE/self join [Post] table(id, parentid))我暫時理解做Graph Reachability problem, 咁bottleneck可能喺個方法本身(speed/space in big O notation) 咁樣就算攞曬所有data在自己嘅code砌返個tree都可以接受

update: 甚至有人寫新extension method解決... http://www.scip.be/index.php?Page=ArticlesNET23

---

作者: 7h1r733n    時間: 2021-1-6 15:56



如果recursive self-join我會用stored procedure去做...
linq冇乜好方法可以行得快.. 如果data多既話client side做會食左屎咁..