Is there a way to optimize a LINQ query with multiple sum operations on a large table in C# using Entity Framework?

Question

I'm making this linq query to a bigger table in which I make several columns sum and takes so long

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();

I wonder if there is a way to optimize this linq

Answer 1

You Must Remove Select and Edit Group by

my Code(I Removed Extra Two Sub Query and Filter of Table)

     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();

I get 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'

I get Your Query with 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'