我在我的应用程序中调整一个由Entity Framework
生成的查询时遇到了很大的困难。这是一个非常基本的查询,但由于EF
使用多个内部子查询而表现极差,而不是使用联接。
这是我的 LINQ 代码:
Projects.Select(proj => new ProjectViewModel()
{
Name = proj.Name,
Id = proj.Id,
Total = proj.Subvalue.Where(subv =>
subv.Created >= startDate
&& subv.Created <= endDate
&&
(subv.StatusId == 1 ||
subv.StatusId == 2))
.Select(c => c.SubValueSum)
.DefaultIfEmpty()
.Sum()
})
.OrderByDescending(c => c.Total)
.Take(10);
EF生成非常复杂的查询,其中包含多个子查询,这些查询性能极差,例如:
SELECT TOP (10)
[Project3].[Id] AS [Id],
[Project3].[Name] AS [Name],
[Project3].[C1] AS [C1]
FROM ( SELECT
[Project2].[Id] AS [Id],
[Project2].[Name] AS [Name],
[Project2].[C1] AS [C1]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
(SELECT
SUM([Join1].[A1]) AS [A1]
FROM ( SELECT
CASE WHEN ([Project1].[C1] IS NULL) THEN cast(0 as decimal(18)) ELSE [Project1].[SubValueSum] END AS [A1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN (SELECT
[Extent2].[SubValueSum] AS [SubValueSum],
cast(1 as tinyint) AS [C1]
FROM [dbo].[Subvalue] AS [Extent2]
WHERE ([Extent1].[Id] = [Extent2].[Id]) AND ([Extent2].[Created] >= '2015-08-01') AND ([Extent2].[Created] <= '2015-10-01') AND ([Extent2].[StatusId] IN (1,2)) ) AS [Project1] ON 1 = 1
) AS [Join1]) AS [C1]
FROM [dbo].[Project] AS [Extent1]
WHERE ([Extent1].[ProjectCountryId] = 77) AND ([Extent1].[Active] = 1)
) AS [Project2]
) AS [Project3]
ORDER BY [Project3].[C1] DESC;
使用EF生成的查询的执行时间为~10秒
。但是当我像这样手动编写查询时:
select
TOP (10)
Proj.Id,
Proj.Name,
SUM(Subv.SubValueSum) AS Total
from
SubValue as Subv
left join
Project as Proj on Proj.Id = Subv.ProjectId
where
Subv.Created > '2015-08-01' AND Subv.Created <= '2015-10-01' AND Subv.StatusId IN (1,2)
group by
Proj.Id,
Proj.Name
order by
Total DESC
执行时间几乎瞬间完成;低于30ms
。
问题显然在于我的能力不足,无法使用LINQ编写良好的EF
查询语句,但是无论我尝试什么(使用测试),我都无法像手写的那样编写出类似的高性能查询。 我已经尝试过查询SubValue表和Project表,但最终结果大多相同:多个无效的嵌套子查询,而不是一个单一的连接完成工作。
如何编写一个查询语句,以模仿上面手写的SQL
?如何控制EF
生成的实际查询语句?最重要的是:如何在需要时让Linq2SQL
和Entity Framework
使用Joins
而不是嵌套子查询。
DataContext
。请查看此处的DataContext.LoadOptions
属性:https://msdn.microsoft.com/zh-cn/library/system.data.linq.datacontext.loadoptions(v=vs.110).aspx。 - Jan Unld