Group By and Sum clauses in LINQ

问题:

I've written a simple linq query as follows:

var query = from c in context.ViewDeliveryClientActualStatus
            join b in context.Booking on c.Booking equals b.Id
            join bg in context.BookingGoods on c.Booking equals bg.BookingId
            select new { c, b, bg };

I have filtered the previous query with a number of premises and then needed to group by a set of fields and get the sum of some of them, as so:

var rows = from a in query
           group a by new {h = a.c.BookingRefex, b = a.c.ClientRefex, c = a.b.PickupCity, d = a.b.PickupPostalCode} into g
           select new
           {                     
               Booking_refex = g.Key.h,
               Client_refex = g.Key.b,
               //Local = g.
               Sum_Quan = g.Sum(p => p.bg.Quantity),
           };

I'd like to get a few values from a which I haven't included in the group by clause. How can I get those values? They're not accessible through g.

回答1:

The g in your LINQ expression is an IEnumerable containing a's with an extra property Key. If you want to access fields of a that are not part of Key you will have to perform some sort of aggregation or selection. If you know that a particular field is the same for all elements in the group you can pick the value of the field from the first element in the group. In this example I assume that c has a field named Value:

var rows = from a in query
  group a by new {
    h = a.c.BookingRefex,
    b = a.c.ClientRefex,
    c = a.b.PickupCity,
    d = a.b.PickupPostalCode
  } into g
  select new { 
    BookingRefex = g.Key.h, 
    ClientRefex = g.Key.b, 
    SumQuantity = g.Sum(p => p.bg.Quantity),
    Value = g.First().c.Value
  }; 

However, if c.Value is the same within a group you might as well include it in the grouping and access it using g.Key.cValue.

回答2:

Just add those field in the

new {h = a.c.BookingRefex, b = a.c.ClientRefex, c = a.b.PickupCity, d = a.b.PickupPostalCode}

they will be accessible in g then.