Aggregate functions in LINQ

Question

Hello all

I have the following LINQ conditional where clause query that produces a result of weights:

From this, I'd like to take the result set and join on another table, tblPurchases

var result = weights.Join(getsuppliersproducts.tblPurchases,
    w => new { w.MemberId, w.MemberName, w.LocationId, w.UnitId },
    p => new { p.MemberId, p.MemberName, p.LocationId, p.UnitId },
    (w, p) => p);

In this second table, I have two columns I would like to perform an aggreagte function on, a sum on PurchaseQuantity and a count of UnitID.

So in its raw format, tblPurchases would look like so:

MemberID    LocationID  UnitId SupplierID  SupplierStatus Purchases
1           1          ab        Sup1            Live         10
1           1          abc       Sup1            Live         10
1           1          abcd      Sup2            Dead         50

From my results data set, I would like the output to look like so:

MemberID LocationID  SupplierID SupplierStatus UnitIdCount Total Purchases
1            1         Sup1        Live           2               50

Also, with these amendments, can I still return this to a List?

How do I implement this using LINQ? I have tried, and failed miserably.

(To those who have seen my previous posts, I'm trying to cover all angles so I can fully understand the concept of what is going on in both SQL and LINQ)

Answer 1

That query will return an IEnumerable where each of the Purchases matches the MemberId, MemberName, LocationId and UnitId in the original Weights query. You can only easily do one aggregate at a time, so

var  result = weights.Join(getsuppliersproducts.tblPurchases,
  w => new { w.MemberId, w.MemberName, w.LocationId, w.UnitId },
  p => new { p.MemberId, p.MemberName, p.LocationId, p.UnitId },
  (w, p) => p).ToList();

Int32 count = result.Count();
Double quantity = result.Sum(p => p.PurchaseQuantity);

Is that what you're trying to do?

EDIT, after your reply of I would like to reutrn a list of tblPurchases with two new columns, the sum of Purchase Quantity and count of unit ID.

This gives a flat output:

var query = Weights.GroupJoin(
  Purchases,
  w => new {w.MemberId, w.LocationId},
  p => new {p.MemberId, p.LocationId},
  (w,p) => new {w.MemberId, w.LocationId, Count = p.Count(), Sum = p.Sum(x => x.Purchases)} );

Note that at the point we do the (w, p) => new {} that w is a single Weight and p is a list of Purchases matching that weight, so you can still keep all of teh (hierarchical) data:

var query = Weights.GroupJoin(
  Purchases,
  w => new {w.MemberId, w.LocationId},
  p => new {p.MemberId, p.LocationId},
  (w,p) => new {w.MemberId, w.LocationId, Count = p.Count(), Sum = p.Sum(x => x.Purchases), Purchases = p} );