LINQ: Group by aggregate but still get information from the most recent row?

Question

Let's say I have a table that holds shipping history. I'd like to write a query that counts the amount of shipments per user and gets the shipping name from the most recent entry in the table for that user.

Table structure for simplicity: ShipmentID MemberID ShippingName ShippingDate

How do I write a LINQ C# query to do this?

Answer 1

It sounds like might want something like:

var query = from shipment in context.ShippingHistory
            group shipment by shipment.MemberID into g
            select new { Count = g.Count(),
                     MemberID = g.Key,
                     MostRecentName = g.OrderByDescending(x => x.ShipmentDate)
                                       .First()
                                       .ShipmentName };