LINQ和Entity Framework - 避免子查询

Question

LINQ和Entity Framework - 避免子查询

13

我在我的应用程序中调整一个由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而不是嵌套子查询。

- veturi

使用 LINQ 时，您可能正在使用 DataContext。请查看此处的 DataContext.LoadOptions 属性：https://msdn.microsoft.com/zh-cn/library/system.data.linq.datacontext.loadoptions(v=vs.110).aspx。 - Jan Unld

将 context.ObjectTrackingEnabled 设置为 false，您将获得巨大的改进。 - Nikita Shrivastava

@JanUnld 谢谢你的建议，但我不确定它如何帮助解决这个具体的问题？你能详细解释一下吗？ - veturi

@Nikita，问题不在于跟踪。问题在于EF生成的查询非常低效，我想知道如何更好地控制EF/ling2sql生成的实际SQL。那么，我该如何让它使用连接而不是低效的子查询呢？ - veturi

Entity Framework和LINQ To SQL不是同一种东西。生成的SQL看起来像是由Entity Framework生成的。如果这是正确的，我建议您删除[linq-to-sql]标签并添加[entity-framework]标签。 - Martin Liversage

显示剩余4条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Martin Liversage · Accepted Answer

EF会根据您提供的LINQ表达式生成SQL，但不能指望此转换完全展开表达式中放入任何内容的结构以优化它。在您的情况下，您已创建了一个表达式树，对于每个项目，它将使用导航属性来汇总与项目相关的一些子值。这会导致嵌套的子查询，正如您所发现的那样。

要改进生成的SQL，您需要在执行所有子值操作之前避免从项目到子值进行导航，并且可以通过创建连接来实现此目的（这也是您手工编写的SQL所做的）。

var query = from proj in context.Project
            join s in context.SubValue.Where(s => s.Created >= startDate && s.Created <= endDate && (s.StatusId == 1 || s.StatusId == 2)) on proj.Id equals s.ProjectId into s2
            from subv in s2.DefaultIfEmpty()
            select new { proj, subv } into x
            group x by new { x.proj.Id, x.proj.Name } into g
            select new {
              g.Key.Id,
              g.Key.Name,
              Total = g.Select(y => y.subv.SubValueSum).Sum()
            } into y
            orderby y.Total descending
            select y;
var result = query.Take(10);

基本思想是通过 where 子句限制的子值来连接项目。要执行左连接，需要使用 DefaultIfEmpty()，但您已经知道了。

然后，连接的值（x）被分组，并在每个组中执行 SubValueSum 的总和。

最后，应用排序和 TOP(10)。

生成的 SQL 仍然包含子查询，但我希望它与您的查询生成的 SQL 相比更有效。

SELECT TOP (10)
    [Project1].[Id] AS [Id],
    [Project1].[Name] AS [Name],
    [Project1].[C1] AS [C1]
    FROM ( SELECT
        [GroupBy1].[A1] AS [C1],
        [GroupBy1].[K1] AS [Id],
        [GroupBy1].[K2] AS [Name]
        FROM ( SELECT
            [Extent1].[Id] AS [K1],
            [Extent1].[Name] AS [K2],
            SUM([Extent2].[SubValueSum]) AS [A1]
            FROM  [dbo].[Project] AS [Extent1]
            LEFT OUTER JOIN [dbo].[SubValue] AS [Extent2] ON ([Extent2].[Created] >= @p__linq__0) AND ([Extent2].[Created] <= @p__linq__1) AND ([Extent2].[StatusId] IN (1,2)) AND ([Extent1].[Id] = [Extent2].[ProjectId])
            GROUP BY [Extent1].[Id], [Extent1].[Name]
        )  AS [GroupBy1]
    )  AS [Project1]
    ORDER BY [Project1].[C1] DESC