代码之家 › 专栏 › 技术社区 › sports Steven

提高LINQ到实体查询(聚合)的性能

linq-to-entities entity-framework

sports Steven · 技术社区 · 8 年前

我有这个EF模型:

class Reception
{
     public string Code { get; set; }
     public virtual List<Row> { get; set; }
}

class Row 
{
     public string Item { get; set; }
     public int Quantity { get; set; }
     public float Weight { get; set; }
}

dbContext.Receptions.Select(r => new 
{
     code = r.Code,
     quantitySum = r.Rows.Sum(e => e.Quantity),
     weightSum = r.Rows.Sum(e => e.Weight),
});

我担心做两次 r.Rows “部分。

2 回复 | 直到 8 年前

Ivan Stoev 8 年前

如果基于 GroupBy ,因为这是聚合的自然SQL构造。

因此,如果您想要更好的翻译并使代码可读性受到影响,可以将所讨论的查询转换为 left outer join + group by

var query = dbContext.Receptions
    .SelectMany(r => r.Rows.DefaultIfEmpty(), (r, e) => new
    {
        r.Code,
        Quantity = (int?)e.Quantity ?? 0,
        Weight = (float?)e.Weight ?? 0,
    })
    .GroupBy(e => e.Code, (key, g) => new
    {
        code = key,
        quantitySum = g.Sum(e => e.Quantity),
        weightSum = g.Sum(e => e.Weight),
    });

也就是说

SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [Code], 
    [GroupBy1].[A1] AS [C2], 
     CAST( [GroupBy1].[A2] AS real) AS [C3]
    FROM ( SELECT 
        [Join1].[K1] AS [K1], 
        SUM([Join1].[A1]) AS [A1], 
        SUM([Join1].[A2]) AS [A2]
        FROM ( SELECT 
            [Extent1].[Code] AS [K1], 
            CASE WHEN ([Extent2].[Quantity] IS NULL) THEN 0 ELSE [Extent2].[Quantity] END AS [A1], 
            CASE WHEN ([Extent2].[Weight] IS NULL) THEN cast(0 as real) ELSE [Extent2].[Weight] END AS [A2]
            FROM  [dbo].[Receptions] AS [Extent1]
            LEFT OUTER JOIN [dbo].[Rows] AS [Extent2] ON [Extent1].[Code] = [Extent2].[Reception_Code]
        )  AS [Join1]
        GROUP BY [K1]
    )  AS [GroupBy1]

对于这个特定的查询,您可以从EF6获得最好的结果。

H. Lowette 8 年前

通常,EF应该使用DB端的SUM函数将其转换为相当有效的DB查询。但如果您想确定,请使用SQL Server探查器分析查询。

我很快为你运行了这段代码,这就是EF所做的:

SELECT 
    [Project2].[Id] AS [Id], 
    [Project2].[Code] AS [Code], 
    [Project2].[C1] AS [C1], 
     CAST( [Project2].[C2] AS real) AS [C2]
FROM 
    (SELECT 
        [Project1].[Id] AS [Id], 
        [Project1].[Code] AS [Code], 
        [Project1].[C1] AS [C1], 
        (SELECT SUM([Extent3].[Weight]) AS [A1] FROM [dbo].[Rows] AS [Extent3] WHERE [Project1].[Id] = [Extent3].[Reception_Id]) AS [C2]
    FROM 
        (SELECT 
            [Extent1].[Id] AS [Id], 
            [Extent1].[Code] AS [Code], 
            (SELECT SUM([Extent2].[Quantity]) AS [A1] FROM [dbo].[Rows] AS [Extent2] WHERE [Extent1].[Id] = [Extent2].[Reception_Id]) AS [C1]
        FROM 
            [dbo].[Receptions] AS [Extent1]
        )AS [Project1]
    )AS [Project2]