我正在尝试从旧的经典ASP项目中复制任何现有查询的结果。该查询非常简单。
SELECT DISTINCT TOP 10 MAX(H.HistID) as MaxHID, C.CompanyID,C.CompanyName, P.ProsID, P.ProsName, P.Online
FROM HISTORY H, PROSPECTUS P, COMPANY C
WHERE H.ProsID = P.ProsID
and P.CompanyID = C.CompanyID
and H.UserID = 2712
GROUP BY C.CompanyID, C.CompanyName, P.ProsID, P.ProsName, P.Online
ORDER BY MaxHID DESC
我（至少尝试过）使用实体框架通过以下查询复制它：
MyContext.HistoryItems.Where(h => h.UserId == userId)
.GroupBy(h => new { h.Prospectus.Family.Id, h.ProsId })
.Select(h => new { Max = h.Max(i => i.Id), Item = h.FirstOrDefault() })
.OrderByDescending(h => h.Max)
.Take(10)
.Select(h => h.Item);
（GroupBy()旨在复制DISTINCT原始查询中的行为。）
此查询得到的数据完全相同，但是执行大约需要10秒。我查看了EF生成的查询，它是一个庞然大物。
SELECT TOP (10)
[Project6].[HistId] AS [HistId],
[Project6].[UserID] AS [UserID],
[Project6].[ProsID] AS [ProsID],
[Project6].[HDate] AS [HDate],
[Project6].[ProsDocId] AS [ProsDocId]
FROM ( SELECT
[Project5].[HistId] AS [HistId],
[Project5].[UserID] AS [UserID],
[Project5].[ProsID] AS [ProsID],
[Project5].[HDate] AS [HDate],
[Project5].[ProsDocId] AS [ProsDocId],
[Project5].[C1] AS [C1]
FROM ( SELECT
[Project4].[HistId] AS [HistId],
[Project4].[UserID] AS [UserID],
[Project4].[ProsID1] AS [ProsID],
[Project4].[HDate] AS [HDate],
[Project4].[ProsDocId] AS [ProsDocId],
(SELECT
MAX([Extent5].[HistId]) AS [A1]
FROM [dbo].[History] AS [Extent5]
INNER JOIN [dbo].[Prospectus] AS [Extent6] ON [Extent5].[ProsID] = [Extent6].[ProsId]
WHERE ([Extent5].[UserID] = @p__linq__0) AND (([Project4].[CompanyId] = [Extent6].[CompanyId]) OR (1 = 0)) AND ([Project4].[ProsID] = [Extent5].[ProsID])) AS [C1]
FROM ( SELECT
[Project2].[ProsID] AS [ProsID],
[Project2].[CompanyId] AS [CompanyId],
[Limit1].[HistId] AS [HistId],
[Limit1].[UserID] AS [UserID],
[Limit1].[ProsID] AS [ProsID1],
[Limit1].[HDate] AS [HDate],
[Limit1].[ProsDocId] AS [ProsDocId]
FROM (SELECT
@p__linq__0 AS [p__linq__0],
[Distinct1].[ProsID] AS [ProsID],
[Distinct1].[CompanyId] AS [CompanyId]
FROM ( SELECT DISTINCT
[Extent1].[ProsID] AS [ProsID],
[Extent2].[CompanyId] AS [CompanyId]
FROM [dbo].[History] AS [Extent1]
INNER JOIN [dbo].[Prospectus] AS [Extent2] ON [Extent1].[ProsID] = [Extent2].[ProsId]
WHERE [Extent1].[UserID] = @p__linq__0
) AS [Distinct1] ) AS [Project2]
OUTER APPLY (SELECT TOP (1)
[Extent3].[HistId] AS [HistId],
[Extent3].[UserID] AS [UserID],
[Extent3].[ProsID] AS [ProsID],
[Extent3].[HDate] AS [HDate],
[Extent3].[ProsDocId] AS [ProsDocId]
FROM [dbo].[History] AS [Extent3]
INNER JOIN [dbo].[Prospectus] AS [Extent4] ON [Extent3].[ProsID] = [Extent4].[ProsId]
WHERE ([Extent3].[UserID] = @p__linq__0) AND (([Project2].[CompanyId] = [Extent4].[CompanyId]) OR (1 = 0)) AND ([Project2].[ProsID] = [Extent3].[ProsID]) ) AS [Limit1]
) AS [Project4]
) AS [Project5]
) AS [Project6]
ORDER BY [Project6].[C1] DESC
我的Linq查询显然存在问题，会导致EF产生此类混乱。我以某种方式创建的EF查询效率不高吗？
每当进行分组时，我都希望查询语法优于流利的语法。
当你做...
(
from h in MyContext.HistoryItems
where h.UserId == userId)
group h by new {
h.CompanyId,
Comapany = h.Company.Name,
Prospectus = h.Prospectus.Name,
h.Prospectus.Online,
h.ProsId
} into grp
select new {
Max = grp.Max(i => i.Id),
grp.Key.CompanyId,
grp.Key.Comapany,
grp.Key.Prospectus,
grp.Key.Online,
grp.Key.ProsId,
} into proj
orderby proj.Max descending
select proj
).Distinct().Take(10)
...该查询应该更加简洁，因为现在您只选择将出现在最终投影中的属性，而FirstOrDefault()不会混淆其中。联接甚至不存在，因为我让EF通过使用导航来找出联接属性如h.Company。
请注意，我尝试重现SQL查询。LINQ查询在我看来不一样。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句