Entity Framework Core性能问题：Linq生成冗余SQL致查询缓慢

冗余SQL生成原因分析 #

对比你的原生SQL和EF Core生成的SQL，核心差异导致性能损耗的原因如下：

1. 不必要的分组键：Linq分组中包含了docAc.ACTION_PERFORMED，但原生SQL仅按文档、状态、部门字段分组。这会让EF生成更细粒度的分组，后续还要额外计算MAX值，增加了SQL嵌套层级和计算量。
2. 类型转换的复杂处理：Linq中pr.RequestedForDepartamentId.ToString()触发EF生成了包含CASE WHEN的空值转换逻辑，而原生SQL直接匹配字段值，避免了额外的类型转换开销。
3. 过滤时机与JOIN逻辑冗余：EF将部分WHERE条件提前到子查询中，且对doc.DOC_NUMBER != "YENI"生成了NOT(('YENI' = [Extent2].[DOC_NUMBER]) AND ([Extent2].[DOC_NUMBER] IS NOT NULL))的复杂判断，同时原本的LEFT JOIN因WHERE条件被隐式转为INNER JOIN，但EF仍保留了冗余的子查询结构。
4. 交叉连接写法导致的JOIN结构冗余：使用from x in _db.Table.Where(...).DefaultIfEmpty()的交叉连接写法，让EF生成了嵌套子查询的JOIN结构，而非原生SQL中直接的LEFT JOIN。

性能优化方案 #

1. 修正分组逻辑 #

移除分组键中的docAc.ACTION_PERFORMED，仅按需要聚合的维度分组，MAX值在SELECT中计算即可：

group new { doc, st, dep, docAc } 
by new { doc.DOCID, doc.DOC_NUMBER, st.SHORT_NAME, dep.DisplayName } into g1
orderby g1.Max(g => g.docAc.ACTION_PERFORMED) descending
select new LastActiveDocumentViewModel
{
    DocId = g1.Key.DOCID,
    DocNumber = g1.Key.DOC_NUMBER,
    DocStatus = g1.Key.SHORT_NAME,
    DocType = DocumentType.PR.ToString(),
    Supplier = g1.Key.DisplayName,
    Date = g1.Max(g => g.docAc.ACTION_PERFORMED)
};

2. 使用显式LEFT JOIN替代交叉连接 #

将from x in _db.Table.Where(...).DefaultIfEmpty()改为标准的LEFT JOIN写法，让EF生成更接近原生SQL的JOIN结构：

from pr in _db.ex_DocumentExt1_PR
join doc in _db.ex_Document on pr.DOCID equals doc.DOCID into docJoin
from doc in docJoin.DefaultIfEmpty()
join docAc in _db.ex_DOCAction on pr.DOCID equals docAc.DOCID into docAcJoin
from docAc in docAcJoin.DefaultIfEmpty()
join st in _db.ex_Status on doc.DOC_STATUS equals st.STATUS_ID into stJoin
from st in stJoin.DefaultIfEmpty()
join dep in _db.SSO_Entities 
    on new { Type = SSO_EntityTypes.COMPANY_STRUCTURE, EntityCode = pr.RequestedForDepartamentId } 
    equals new { dep.Type, dep.EntityCode } into depJoin
from dep in depJoin.DefaultIfEmpty()

注意：这里移除了ToString()，如果RequestedForDepartamentId与dep.EntityCode类型不匹配，改用EF Core的EF.Functions.ToString(pr.RequestedForDepartamentId)进行数据库端转换，避免客户端评估。

3. 简化WHERE条件 #

将doc.DOC_NUMBER != "YENI"直接保留，EF会生成简洁的<> 'YENI'判断；同时明确docAc非空判断，避免冗余的空值处理：

where docAc != null 
      && docAc.ISPERFORMED == 1
      && docAc.ACTOR_ID == uid
      && doc.DOC_NUMBER != "YENI"
      && doc.DOC_NUMBER.Contains(searchText)

4. 临时方案：使用原生SQL #

如果上述优化后仍达不到预期性能，直接使用EF Core执行原生SQL，映射到ViewModel：

var sql = @"SELECT TOP(50)
    doc.DOCID,
    doc.DOC_NUMBER,
    'PR' as DocType,
    st.SHORT_NAME as DocStatus,
    dep.DisplayName as Supplier,
    MAX(docAc.ACTION_PERFORMED) as Date
FROM ex_DocumentExt1_PR pr
LEFT JOIN ex_Document doc ON doc.DOCID = pr.DOCID
LEFT JOIN ex_DOCAction docAc ON docAc.DOCID = doc.DOCID
LEFT JOIN ex_Status st ON st.STATUS_ID = doc.DOC_STATUS
LEFT JOIN SSO_Entities dep ON dep.Type = 'COMPANY_STRUCTURE' AND dep.EntityCode = pr.RequestedForDepartamentId
WHERE docAc.ISPERFORMED = 1
  AND docAc.ACTOR_ID = @uid
  AND doc.DOC_NUMBER != 'Yeni'
  AND doc.DOC_NUMBER LIKE '%' + @searchText + '%'
GROUP BY doc.DOCID, doc.DOC_NUMBER, st.SHORT_NAME, dep.DisplayName
ORDER BY MAX(docAc.ACTION_PERFORMED) DESC";

return _db.Set<LastActiveDocumentViewModel>().FromSqlRaw(sql, new SqlParameter("@uid", uid), new SqlParameter("@searchText", searchText));

注意：需确保LastActiveDocumentViewModel已配置为EF Core的无实体查询类型，或直接使用Query<LastActiveDocumentViewModel>()。

5. 数据库层面优化 #

• 为ex_DOCAction(DOCID, ACTOR_ID, ISPERFORMED)创建复合索引
• 为ex_Document(DOCID, DOC_NUMBER, DOC_STATUS)创建索引
• 为SSO_Entities(Type, EntityCode)创建索引

内容的提问来源于stack exchange，提问作者Emil Abbas