staymen

回覆 10# 7h1r733n

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

7h1r733n

回覆  7h1r733n

但係唔知TREE, 有幾多層,唔知JOIN幾次ＴＡＢＬＥ
staymen 發表於 2021-1-5 16:31

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

antlee

回覆  7h1r733n

但係唔知TREE, 有幾多層,唔知JOIN幾次ＴＡＢＬＥ
staymen 發表於 2021-1-5 16:31

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

staymen

回覆 12# 7h1r733n

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

staymen

回覆 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

回覆 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

回覆  antlee

yes self join

self join Post Table,  如果唔知tree 有幾層.  幾多層Child 就有幾多層咁
...
staymen 發表於 2021-1-6 01:35

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