CODEWITHSUNDEEP
linqentity-frameworkentity-framework-4
Roger
Roger

Reputation: 2316

EF query that includes additional column with aggregated data

I have a table OrderDetail like this:

ID OrderNumber LineItem Qty
---------------------------
1  10000         1       2
2  10000         2       5
3  10000         3       1
4  10001         1       10

I would like to return this table with an extra column containing the max qty for each order (grouped on OrderNumber), like this:

ID OrderNumber LineItem Qty  MaxQty
-----------------------------------
1  10000         1       2     5
2  10000         2       5     5
3  10000         3       1     5
4  10001         1       10    10

I've been struggling with how to put together the EF syntax to make it happen. I imagine the result will be some kind of anonymous type with the OrderDetail record as the first property and the quantity as a second property, like select new { OrderDetail = ??, MaxQty = ?? }.

Thanks, Roger Martin

Upvotes: 1

Views: 282

Answers (1)

nemesv
nemesv

Reputation: 139758

You can get the MaxQty with a sub-query. Using the EF DBContext API:

public class MyContext : DbContext
{
    public DbSet<OrderDetail> OrderDetails { get; set; }
}

var context = new MyContext();
var result = context.OrderDetails.Select(od =>
    new { 
          OrderDetail = od, 
          MaxQty = context.OrderDetails
             .Where(o => o.OrderNumber == od.OrderNumber).Max(a => a.Qty) });

It will generate the following SQL query:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[OrderNumber] AS [OrderNumber], 
[Extent1].[LineItem] AS [LineItem], 
[Extent1].[Qty] AS [Qty], 
(SELECT 
    MAX([Extent2].[Qty]) AS [A1]
    FROM [dbo].[OrderDetails] AS [Extent2]
    WHERE [Extent2].[OrderNumber] = [Extent1].[OrderNumber]) AS [C1]
FROM [dbo].[OrderDetails] AS [Extent1]}

Upvotes: 2

Related Questions