有没有一种方法可以使用Entity Framework在C#中对大型表进行多个求和操作来优化LINQ查询？

7hiiyaii 于 2023-06-03 发布在 C#

关注(0)|答案(2)|浏览(263)

我正在对一个更大的表进行这个linq查询，在这个表中，我对几个列求和，需要很长时间

var returnList = context.Revenue_Consolidate.Select(b => new { b.field, b.GrsVol, b.Type, b.OwnNetVol, b.OwnNetVal, b.UnitPrice })
    .Where(r => r.FromProdDate > startDate && r.FromProdDate <= endDate)
    .GroupBy(b => b.FromProdDate.Month)
    .Select(r => new MiddelRQTGrid
    {
        Month = r.Select(b => b.FromProdDate.Month).FirstOrDefault(),
        Year = r.Select(b => b.FromProdDate.Year).FirstOrDefault(),
        Price_OIL = r.Where(b => b.Type == "OIL").Sum(b => b.OwnNetVol == 0 ? 0 : b.UnitPrice * (b.OwnNetVal / b.OwnNetVol)),
        Price_GAS = r.Where(b => b.Type == "GAS").Sum(b => b.OwnNetVol == 0 ? 0 : b.UnitPrice * (b.OwnNetVal / b.OwnNetVol)),
        Price_NGL = r.Where(b => b.Type == "NGL").Sum(b => b.OwnNetVol == 0 ? 0 : b.UnitPrice * (b.OwnNetVal / b.OwnNetVol))
    }).ToList();

我想知道是否有一种方法可以优化这个LINQ

linq

来源：https://stackoverflow.com/questions/76366623/is-there-a-way-to-optimize-a-linq-query-with-multiple-sum-operations-on-a-large

2条答案

按热度按时间

wkyowqbh1#

此查询的执行速度应该更快。我把它简化了一点：

var returnList = context.Revenue_Consolidate
    .Where(r => r.FromProdDate > startDate && r.FromProdDate <= endDate)
    .GroupBy(b => new { b.FromProdDate.Year, b.FromProdDate.Month })
    .Select(g => new MiddelRQTGrid
    {
        Year = g.Key.Year,
        Month = g.Key.Month,
        Price_OIL = g.Sum(b => b.Type != "OIL" || b.OwnNetVol == 0 ? 0 : b.UnitPrice * (b.OwnNetVal / b.OwnNetVol)),
        Price_GAS = g.Sum(b => b.Type != "GAS" || b.OwnNetVol == 0 ? 0 : b.UnitPrice * (b.OwnNetVal / b.OwnNetVol)),
        Price_NGL = g.Sum(b => b.Type != "NGL" || b.OwnNetVol == 0 ? 0 : b.UnitPrice * (b.OwnNetVal / b.OwnNetVol))
    }).ToList();

赞(0）回复(0）举报 2023-06-03

8hhllhi22#

必须删除“选择和编辑分组依据”
我的代码（我删除了表的额外两个子查询和过滤器）

var returnList = context.Revenue_Consolidate//.Select(b => new { b.FromProdDate, b.field, b.GrsVol, b.Type, b.OwnNetVol, b.OwnNetVal, b.UnitPrice })
    .Where(r => r.FromProdDate > startDate && r.FromProdDate <= endDate)
    .GroupBy(b => new { b.FromProdDate.Month, b.FromProdDate.Year })
    .Select(r => new MiddelRQTGrid
    {
        Month = r.Key.Month,
        Year = r.Key.Year,
     
        Price_OIL = r.Where(b => b.Type == "OIL").Sum(b => b.OwnNetVol == 0 ? 0 : b.UnitPrice * (b.OwnNetVal / b.OwnNetVol)),
        Price_GAS = r.Where(b => b.Type == "GAS").Sum(b => b.OwnNetVol == 0 ? 0 : b.UnitPrice * (b.OwnNetVal / b.OwnNetVol)),
        Price_NGL = r.Where(b => b.Type == "NGL").Sum(b => b.OwnNetVol == 0 ? 0 : b.UnitPrice * (b.OwnNetVal / b.OwnNetVol))
    }).ToList();

我获得了Query with Profiler

exec sp_executesql N'SELECT [t].[Month], [t].[Year], COALESCE(SUM(CASE
    WHEN [t].[Type] = N''OIL'' THEN CASE
        WHEN [t].[OwnNetVol] = 0 THEN 0
        ELSE [t].[UnitPrice] * ([t].[OwnNetVal] / [t].[OwnNetVol])
    END
END), 0) AS [Price_OIL], COALESCE(SUM(CASE
    WHEN [t].[Type] = N''GAS'' THEN CASE
        WHEN [t].[OwnNetVol] = 0 THEN 0
        ELSE [t].[UnitPrice] * ([t].[OwnNetVal] / [t].[OwnNetVol])
    END
END), 0) AS [Price_GAS], COALESCE(SUM(CASE
    WHEN [t].[Type] = N''NGL'' THEN CASE
        WHEN [t].[OwnNetVol] = 0 THEN 0
        ELSE [t].[UnitPrice] * ([t].[OwnNetVal] / [t].[OwnNetVol])
    END
END), 0) AS [Price_NGL]
FROM (
    SELECT [r].[OwnNetVal], [r].[OwnNetVol], [r].[Type], [r].[UnitPrice], DATEPART(month, [r].[FromProdDate]) AS [Month], DATEPART(year, [r].[FromProdDate]) AS [Year]
    FROM [Revenue_Consolidate] AS [r]
    WHERE [r].[FromProdDate] > @__startDate_0 AND [r].[FromProdDate] <= @__endDate_1
) AS [t]
GROUP BY [t].[Month], [t].[Year]',N'@__startDate_0 datetime2(7),@__endDate_1 datetime2(7)',@__startDate_0='2023-05-31 00:19:00.8655195',@__endDate_1='2023-05-31 00:19:04.1553372'

我使用Profiler获取您的查询

exec sp_executesql N'SELECT COALESCE((
    SELECT TOP(1) DATEPART(month, [t0].[FromProdDate])
    FROM (
        SELECT [r0].[Id], [r0].[FromProdDate], [r0].[GrsVol], [r0].[OwnNetVal], [r0].[OwnNetVol], [r0].[Type], [r0].[UnitPrice], [r0].[field], DATEPART(month, [r0].[FromProdDate]) AS [Key]
        **FROM [Revenue_Consolidate] AS [r0]**
        WHERE [r0].[FromProdDate] > @__startDate_0 AND [r0].[FromProdDate] <= @__endDate_1
    ) AS [t0]
    WHERE [t].[Key] = [t0].[Key] OR (([t].[Key] IS NULL) AND ([t0].[Key] IS NULL))), 0) AS [Month], COALESCE((
    SELECT TOP(1) DATEPART(year, [t1].[FromProdDate])
    FROM (
        SELECT [r1].[Id], [r1].[FromProdDate], [r1].[GrsVol], [r1].[OwnNetVal], [r1].[OwnNetVol], [r1].[Type], [r1].[UnitPrice], [r1].[field], DATEPART(month, [r1].[FromProdDate]) AS [Key]
        **FROM [Revenue_Consolidate] AS [r1]**
        WHERE [r1].[FromProdDate] > @__startDate_0 AND [r1].[FromProdDate] <= @__endDate_1
    ) AS [t1]
    WHERE [t].[Key] = [t1].[Key] OR (([t].[Key] IS NULL) AND ([t1].[Key] IS NULL))), 0) AS [Year], COALESCE(SUM(CASE
    WHEN [t].[Type] = N''OIL'' THEN CASE
        WHEN [t].[OwnNetVol] = 0 THEN 0
        ELSE [t].[UnitPrice] * ([t].[OwnNetVal] / [t].[OwnNetVol])
    END
END), 0) AS [Price_OIL], COALESCE(SUM(CASE
    WHEN [t].[Type] = N''GAS'' THEN CASE
        WHEN [t].[OwnNetVol] = 0 THEN 0
        ELSE [t].[UnitPrice] * ([t].[OwnNetVal] / [t].[OwnNetVol])
    END
END), 0) AS [Price_GAS], COALESCE(SUM(CASE
    WHEN [t].[Type] = N''NGL'' THEN CASE
        WHEN [t].[OwnNetVol] = 0 THEN 0
        ELSE [t].[UnitPrice] * ([t].[OwnNetVal] / [t].[OwnNetVol])
    END
END), 0) AS [Price_NGL]
FROM (
    SELECT [r].[OwnNetVal], [r].[OwnNetVol], [r].[Type], [r].[UnitPrice], DATEPART(month, [r].[FromProdDate]) AS [Key]
    **FROM [Revenue_Consolidate] AS [r]**
    WHERE [r].[FromProdDate] > @__startDate_0 AND [r].[FromProdDate] <= @__endDate_1
) AS [t]
GROUP BY [t].[Key]',N'@__startDate_0 datetime2(7),@__endDate_1 datetime2(7)',@__startDate_0='2023-05-31 00:19:00.8655195',@__endDate_1='2023-05-31 00:19:04.1553372'

赞(0）回复(0）举报 2023-06-03

我来回答

有没有一种方法可以使用Entity Framework在C#中对大型表进行多个求和操作来优化LINQ查询？

2条答案

相关问题

热门标签

最新问答