Geek asks and answers

LINQ, Unions, Groups, and Aggregates

Using either Join or GroupJoin, is there a way to generate aggregate values ​​for fields in both the parent and child tables. Given the Orders table and OrderDetails table, using the two steps below, I can get the aggregate (MAX) from Orders and the aggregate (SUM) from OrderDetails.

STEP 1:

var query = from o in orders
                    join d in details on o.OrderId equals d.OrderId
                    select new
                    {
                        order = o.OrderId,
                        maximum = o.UserId,
                        quantity = d.Quantity
                    };

Step 2:

var result = (from q in query
                      group q by q.order into g
                      select new 
                      {
                          OrderId = g.Key,
                          MaxUnits = g.Max(q => q.maximum),
                          Available = (g.Max(q => q.maximum) - g.Sum(q => q.quantity))
                      });

However, when I try to combine them, as in:

var finalresult   = orders  
                    .GroupJoin(  details,  
                    o => o.OrderId,  
                    d => d.OrderDetailId,  
                    (o, grp) => new {
                        OrderId = o.OrderId,
                        MaxUnits = grp.Max(o => o.maximum),
                        Available = (grp.Max(o => o.maximum) - grp.Sum(d => d.Quantity))
                    });

.. The value of 'o' is outside the scope of the grouped set 'grp'. Therefore grp.Max (o => o.maximum) leads to an error. It appears that only aggregate values ​​are available for the child table (OrderDetail).

So does anyone know if aggregates can be obtained from Child and Parent tables in the same query?

+3
source share
1 answer

result - . LINQ - , 1 , . 2 ontop . result, .

, , . . , orderQuantities. from q in query , from oq in orderQuantities , .

, :

var query = orders.Join(details, o => o.OrderId, d => d.OrderId, 
    (o, d) => new {
        order = o.OrderId, 
        maximum = o.UserId, 
        quantity = d.Quantity
    }).GroupBy(oq => oq.order)
    .Select(g => new {
        OrderId = g.Key, 
        MaxUnits = g.Max(q => q.maximum), 
        Available = (g.Max(q => q.maximum) - g.Sum(q => q.quantity))
    });

...

+1

More articles:


All Articles