EF Core 9 Group By and SUM causes wierd query - Stack Overflow

admin管理员组

文章数量:1400161

Trying to learn EF Core and C# but are struggling to create a correct aggregate query using Lambda.

I have two enitities

public class OrderDetail
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public int Qty { get; set; }
    public int ItemId { get; set; }
    public Items? Item { get; set; }
}

and

public class Item
{
    public int Id { get; set; }
    public decimal Weight { get; set; }
    public decimal Volume { get; set; }
}

And my code looks like this:

await context.OrderDetail
    .GroupBy(i => i.Date)
    .Select(s => new TestDto()
    {
        Date = s.Key,
        Volume = s.Sum(x => x.Item.Weight),
        Weight = s.Sum(x => x.Item.Volume),
    })
    .AsNoTracking()
    .ToListAsync();

And my output SQL looks like this:

SELECT [d].[Date], 
(
    SELECT COALESCE(SUM([i].[Weight]), 0.0)
    FROM [OrderDetails] AS [d0]
    INNER JOIN [Items] AS [i] ON [d0].[ItemId] = [i].[Id]
    WHERE [d].[Date] = [d0].[Date]) AS [Volume], 
(
    SELECT COALESCE(SUM([i0].[Volume]), 0.0)
    FROM [OrderDetails] AS [d1]
    INNER JOIN [Items] AS [i0] ON [d1].[ItemId] = [i0].[Id]
    WHERE [d].[Date] = [d1].[Date]
) AS [Weight]
FROM [OrderDetails] AS [d]
GROUP BY [d].[Date]

How can i get the Item to be joined directly with OrderDetails and SUM in the original Select? All my searches points to sollutions where i have to write manual joins using LINQ but there really has to be a better options for a simple query like this?

Trying to learn EF Core and C# but are struggling to create a correct aggregate query using Lambda.

I have two enitities

public class OrderDetail
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public int Qty { get; set; }
    public int ItemId { get; set; }
    public Items? Item { get; set; }
}

and

public class Item
{
    public int Id { get; set; }
    public decimal Weight { get; set; }
    public decimal Volume { get; set; }
}

And my code looks like this:

await context.OrderDetail
    .GroupBy(i => i.Date)
    .Select(s => new TestDto()
    {
        Date = s.Key,
        Volume = s.Sum(x => x.Item.Weight),
        Weight = s.Sum(x => x.Item.Volume),
    })
    .AsNoTracking()
    .ToListAsync();

And my output SQL looks like this:

SELECT [d].[Date], 
(
    SELECT COALESCE(SUM([i].[Weight]), 0.0)
    FROM [OrderDetails] AS [d0]
    INNER JOIN [Items] AS [i] ON [d0].[ItemId] = [i].[Id]
    WHERE [d].[Date] = [d0].[Date]) AS [Volume], 
(
    SELECT COALESCE(SUM([i0].[Volume]), 0.0)
    FROM [OrderDetails] AS [d1]
    INNER JOIN [Items] AS [i0] ON [d1].[ItemId] = [i0].[Id]
    WHERE [d].[Date] = [d1].[Date]
) AS [Weight]
FROM [OrderDetails] AS [d]
GROUP BY [d].[Date]

How can i get the Item to be joined directly with OrderDetails and SUM in the original Select? All my searches points to sollutions where i have to write manual joins using LINQ but there really has to be a better options for a simple query like this?

• entity-framework-core

Share Improve this question Follow edited Mar 24 at 14:05 Bengt asked Mar 24 at 13:26 BengtBengt 1511 silver badge33 bronze badges 1

• 1 Verified this to be the case, even using a .Select(o => new { o.Date, o.Item.Weight, o.Item.Volume }) prior to the GroupBy() did not alter the generated SQL. I don't know of any reason why the SQL cannot/should not be simplified. This is probably worth raising on the EF Core group in GitHub if there is an explanation or a possible bug/optimization that can be considered. The current query works, I didn't have the data volume to really test the execution plan, but it seems more complex than it needs to be. – Steve Py Commented Mar 24 at 21:10

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

It seems EF Core has a limitation in this scenario. You can rewrite your query as follows:

await context.OrderDetail
    .GroupBy(i => i.Date, i => i.Item)
    .Select(g => new TestDto
    {
        Date = g.Key,
        Volume = g.Sum(x => x.Weight),
        Weight = g.Sum(x => x.Volume),
    })
    .ToListAsync();

Note: There's no need for AsNoTracking() after the Select.

本文标签： EF Core 9 Group By and SUM causes wierd queryStack Overflow