实体框架核心:性能问题

Question

我对EF Core有一些问题。每当我在C#中编写一些linq以从数据库中获取数据时，它都会添加一个无用的select * from语句。我不明白它为什么会这样。

原始SQL查询运行速度相当快-使用linq 时为100ms，而不是300ms

这是C#**:中的方法

return (from pr in _db.ex_DocumentExt1_PR
from doc in _db.ex_Document.Where(doc => doc.DOCID == pr.DOCID).DefaultIfEmpty()
from docAc in _db.ex_DOCAction.Where(docAc => docAc.DOCID == pr.DOCID).DefaultIfEmpty()
from st in _db.ex_Status.Where(st => st.STATUS_ID == doc.DOC_STATUS).DefaultIfEmpty()
from dep in _db.SSO_Entities.Where(dep => dep.Type == SSO_EntityTypes.COMPANY_STRUCTURE && dep.EntityCode == pr.RequestedForDepartamentId.ToString()).DefaultIfEmpty()
where docAc.ISPERFORMED == 1
&& docAc.ACTOR_ID == uid
&& doc.DOC_NUMBER != "YENI"
&& doc.DOC_NUMBER.Contains(searchText)
group new { doc, st, dep, docAc } 
by new { doc.DOCID, doc.DOC_NUMBER, st.SHORT_NAME, dep.DisplayName, docAc.ACTION_PERFORMED } into g1
orderby g1.Key.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)
});

这是EF Core生成的SQL查询：

SELECT TOP (50) 
[Project1].[C2] AS [C1], 
[Project1].[DOCID] AS [DOCID], 
[Project1].[DOC_NUMBER] AS [DOC_NUMBER], 
[Project1].[SHORT_NAME] AS [SHORT_NAME], 
[Project1].[C3] AS [C2], 
[Project1].[DisplayName] AS [DisplayName], 
[Project1].[C1] AS [C3]
FROM ( SELECT 
[GroupBy1].[A1] AS [C1], 
[GroupBy1].[K1] AS [DOCID], 
[GroupBy1].[K2] AS [DOC_NUMBER], 
[GroupBy1].[K3] AS [ACTION_PERFORMED], 
[GroupBy1].[K4] AS [SHORT_NAME], 
[GroupBy1].[K5] AS [DisplayName], 
1 AS [C2], 
N'PR' AS [C3]
FROM ( SELECT 
[Filter1].[DOCID1] AS [K1], 
[Filter1].[DOC_NUMBER] AS [K2], 
[Filter1].[ACTION_PERFORMED] AS [K3], 
[Filter1].[SHORT_NAME] AS [K4], 
[Extent5].[DisplayName] AS [K5], 
MAX([Filter1].[ACTION_PERFORMED]) AS [A1]
FROM   (SELECT [Extent1].[RequestedForDepartamentId] AS [RequestedForDepartamentId], [Extent2].[DOCID] AS [DOCID1], [Extent2].[DOC_NUMBER] AS [DOC_NUMBER], [Extent3].[ACTOR_ID] AS [ACTOR_ID], [Extent3].[ACTION_PERFORMED] AS [ACTION_PERFORMED], [Extent4].[SHORT_NAME] AS [SHORT_NAME]
FROM    [dbo].[ex_DocumentExt1_PR] AS [Extent1]
LEFT OUTER JOIN [dbo].[ex_Document] AS [Extent2] ON [Extent2].[DOCID] = [Extent1].[DOCID]
INNER JOIN [dbo].[ex_DOCAction] AS [Extent3] ON [Extent3].[DOCID] =  CAST( [Extent1].[DOCID] AS bigint)
LEFT OUTER JOIN [dbo].[ex_Status] AS [Extent4] ON [Extent4].[STATUS_ID] = [Extent2].[DOC_STATUS]
WHERE ( NOT (('YENI' = [Extent2].[DOC_NUMBER]) AND ([Extent2].[DOC_NUMBER] IS NOT NULL))) AND (1 = [Extent3].[ISPERFORMED]) ) AS [Filter1]
LEFT OUTER JOIN [dbo].[SSO_Entities] AS [Extent5] ON ('COMPANY_STRUCTURE' = [Extent5].[Type]) AND (([Extent5].[EntityCode] = (CASE WHEN ([Filter1].[RequestedForDepartamentId] IS NULL) THEN N'' ELSE  CAST( [Filter1].[RequestedForDepartamentId] AS nvarchar(max)) END)) OR (([Extent5].[EntityCode] IS NULL) AND (CASE WHEN ([Filter1].[RequestedForDepartamentId] IS NULL) THEN N'' ELSE  CAST( [Filter1].[RequestedForDepartamentId] AS nvarchar(max)) END IS NULL)))
WHERE ([Filter1].[ACTOR_ID] = 1018) AND ([Filter1].[DOC_NUMBER] LIKE '%%' ESCAPE '~')
GROUP BY [Filter1].[DOCID1], [Filter1].[DOC_NUMBER], [Filter1].[ACTION_PERFORMED], [Filter1].[SHORT_NAME], [Extent5].[DisplayName]
)  AS [GroupBy1]
)  AS [Project1]
ORDER BY [Project1].[ACTION_PERFORMED] DESC

这是我编写的原始SQL查询，它的作用与Linq查询相同：

SELECT TOP(50)
doc.DOCID,
doc.DOC_NUMBER,
'PR',
st.SHORT_NAME,
dep.DisplayName,
MAX(docAc.ACTION_PERFORMED)
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 = 1018 
AND doc.DOC_NUMBER != 'Yeni'
GROUP BY doc.DOCID, doc.DOC_NUMBER, st.SHORT_NAME, dep.DisplayName
ORDER BY MAX(docAc.ACTION_PERFORMED) DESC

Answer 1

EF不是SQL的包装器。我没有看到任何"；SELECT*"；在生成的SQL中，尽管您将遇到EF构建的一系列内部SELECT语句，这些语句允许它联接通常没有建立相互引用的表。对于EF来说，这是一个必要的缺点，因为它能够根据您想要如何关联数据来查询数据。

EF的优势在于，当使用适当规范化的数据结构时，简化了数据访问，这些关系可以通过约定或配置来解决。我不同意EF不处理多个表；"好"；，只要它们有适当的关联和索引，它就可以很快地处理它们。然而，现实是，许多野外数据系统没有遵循适当的规范化，最终需要在松散相关的数据中进行查询。EF可以做到，但它不会是最有效的

如果这是一个新的项目/数据库，无论是利用Code First还是Schema First，我的建议都是与FK以及表之间的索引/约束建立适当的规范化关系。

如果这是一个现有的数据库，您不能选择修改模式，那么我建议使用View来绑定所需的实体模型，从中可以使用更直接优化的SQL表达式来获得所需的数据。这将是一组不同的实体，而不是用于更新数据的按表实体。目标是更大的、开放式的读取操作，其松散的关系导致昂贵的查询，可以向下优化，其中更新操作应该是"；触摸"；通过基于表的实体一次可以管理的记录要少得多。

Answer 2

查询看起来不完全相同。例如，您的查询按4列分组，而EF查询按5列分组——除了其他四列之外，它的group by子句中还有[Filter1].[ACTION_PERFORMED]。根据您的测试数据样本，它们的行为可能相似，但通常结果会有所不同。

正如@allmhuran在评论中指出的那样，EF有生成低效查询的趋势，尤其是当涉及2个以上表时。就我个人而言，当我发现自己处于这种情况时，我会创建一个数据库视图，将查询放在那里，将视图添加到DbContext中，然后直接从中进行选择。在极端情况下，这甚至可能是一个存储过程。但那就是我，我比C#更了解SQL，总是使用数据库优先的方法，并将我的数据库放在附带的SSDT项目中。

如果使用EF Code First并使用EF Migrations，添加视图可能会有点问题，但这应该是可能的。这个问题可能是一个好的开始。