I’ve been using LINQ for my data access piece in .NET for the last few months and I thought I would write a quick post on how I’ve found to do grouping. I’ve seen some examples where a ‘group by new’ is done with a list several fields to group by. I’ve also seen similar examples in SQL, but this is often times not what is wanted. In SQL it generally requires a join to a second query that has the grouping field and any aggregates that are needed. In LINQ, a way that I’ve found to work for me is something like this:
var q = from invoice in context.Invoices where invoice.ManifestDate >= startDate && invoice.ManifestDate <= endDate group invoice by invoice.PoNumber into g select new { PoNumber = g.Key, g.First().InvoiceNumber, g.First().ManifestDate, ShippingCharge = g.Sum(p => p.ShippingCharge), ProductCharge = g.Sum(p => p.Quantity * p.NetPrice), g.First().CreateDate }
This allows me to group only by PoNumber in this example and calculate aggregates on ShippingCharge and ProductCharge from the group. In this example this is only going to use the first value found in the group for the value returned, which is an acceptable solution for me in this scenario.
In SQL this might look something like:
SELECT DISTINCT
inv.PoNumber,
inv.InvoiceNumber,
inv.ManifestDate,
g.ShippingCharge,
g.ProductCharge,
inv.CreateDate
FROM Invoice inv
JOIN (
SELECT
PoNumber,
SUM(Quantity * NetPrice) as ProductCharge,
SUM(ShippingCharge) as ShippingCharge
FROM Invoice
GROUP BY PoNumber) g
ON inv.PoNumber = g.PoNumber