使用 linq + lambda + entity framework 查詢資料效能緩慢

1. 避免先撈出資料放在陣列中再進行比對
錯誤範例
var 沖銷支票號碼 = db.傳票.SelectMany(a=>a.傳票明細).Where(a => a.類別 == "K3" && a.借貸 == "1").Select(a => a.支票號碼).ToList();
var list=db.應付票據.Where(a => !沖銷支票號碼.Contains(a.支票號碼));
改良寫法
var list=db.應付票據.Where(a => !db.傳票.Any(b=>b.傳票明細.Any(c => c.類別 == "K3" && c.借貸 == "1" && c.支票號碼==a.支票號碼));

2. 資料量少且存在會重複讀取的資料的時候先 tolist 再 select 會比直接select 快，否則避免先 tolist
資料量少且傳票明細會重複讀取
db.傳票.Select(a => new { 傳票= a, 明細s = a.傳票明細 }).ToList().Select(a => new { id = a.傳票.id, 借方合計 = a.明細s.DefaultIfEmpty().Sum(b => b.借方金額), 貸方合計 = a.明細s.DefaultIfEmpty().Sum(b => b.貸方金額) })
資料量多
db.傳票.Select(a => new { id = a.傳票.id, 借方合計 = a.傳票明細.DefaultIfEmpty().Sum(b => b.借方金額), 貸方合計 = a.傳票明細.DefaultIfEmpty().Sum(b => b.貸方金額) })

3. DefaultIfEmpty() 效能不佳，且用法有限制(某些情況會造成執行錯誤)， 請改用 ToList()

4. 避免 select 中使用到無法直接轉成 sql 語法的指令，避免執行時自動tolist再select造成效能緩慢
ex:
public enum enum類別   { 進貨驗收, 進貨退回,}
list = db.進貨驗收明細.Where(a =>... ).Select(a => new { 類別 = enum類別.進貨驗收 }).ToList(); // enum類別.進貨驗收 無法轉換成sql 語法
             
5. 查詢結果一律轉為list，否則之後使用時會重複執行查詢指令產生結果，造成效能低落

var aaa=db.訂單.Where(a=>...).Select(a =>...).ToList();

foreach (var a in aaa) 

...

var bbb=aaa.Where(a=>...); // 若不先tolist 會重新查詢產生aaa

6. 透過 partial class 另外存取關聯資料時，不要直接存取，必須重頭查詢，因為直接存取時會先把關聯資料表全部從資料庫抓到記憶體，造成效能下降，資料越多越明顯

舉例說明:

public partial class 訂單 {

  public bool 包含3C => 訂單明細.Any(b =>b.類別 == "3C"); // 會先把"訂單明細"所有資料抓到記憶體，再找出該訂單相關的明細

}

請改為以下標準寫法

public bool 包含3C(Entities db)

{

  return db.訂單明細.Any(b =>b.訂單編號==訂單編號 && b.類別 == "3C");

}

7. 查詢明細資料直接查詢，不要先查詢主檔再用 .selectmany() 取得明細

8. groupby 之前先把需要的欄位抓出來，且之後盡量不要再用 .FirstOrDefault() 去抓欄位，可以納入 groupby 就納入

9. .find() 會把資料全部都先抓到記憶體，且會自動呼叫DetectChanges (參考這裡)，若頻繁呼叫或資料量大(例如用到image 型態儲存圖片)則改用 .where()+.select()+.single() 只抓需要的欄位，或設定AutoDetectChangesEnabled 為 false，或用 .single(a=>a.id==?)

10. 避免這樣處理 : select 分兩次，第一次抓需要的欄位，第二次針對欄位做其他運算 (例如sum)

11. 不要這樣寫 : (產品進出明細s ?? db.產品進出明細).Where(a=>...)

12. 盡量避免把陣列很大的資料透過 sql server 判斷是否存在其中，而是先抓出資料後在記憶體中比對，且不要用 Any() 否則會造成 stackoverflow (對應 sql 的 in 固有限制?)

db.aaa.Where(a => !bbb.Contains(a.id)).ToList(); 

↓

db.aaa.Select(a => a.id).ToList().Except(bbb).ToList(); // 如果只需要單一欄位值

or

db.aaa.Where(a => !bbb.Contains(a.id)).ToList(); 

↓

db.aaa.Where(a => xxx).ToList().Where(a => !bbb.Contains(a.id)).Select(a=>new {xxx}).ToList(); // 需要抓多個欄位，把 contains 放在 memory 中處理

13. 有時查詢條件會跨多個table，造成效能下降，不用table join改用view 會改善

14. 有時會遇到select 關聯table 效能低落甚至 timeout，必須在記憶體中使用二次查詢來處理關聯table，原因不明，若兩個table 資料量都很大可能會遇到

(await db.aaa.Select(a=>new {a.name,a.id}).ToListAsync()).Select(async a=>new {a.name,a.id,list=await db.bbb.Where(b=>b.pid==a.id).Select(b=>new {b.name,b.id}).ToListAsync()}).Select(a=>a.Result)