LINQ: group by aggregate, but still get information from the last line?

Question

Say I have a table that stores shipping history. I would like to write a query that calculates the number of shipments for each user and receives the latest record in the table for this user on behalf of the delivery.

Table structure for simplicity: ShipmentID User ID ShippingName ShippingDate

How to write a LINQ C # query for this?

Answer 1

It seems like 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 }; 

Answer 2

Not really LINQ's answer, but personally I would choose SQL for this to make sure that it doesn't do any N + 1, etc .; eg:

 select s1.MemberID, COUNT(1) as [Count], (select top 1 ShippingName from Shipping s2 where s2.MemberID = s1.MemberID order by s2.ShippingDate desc) as [LastShippingName] from Shipping s1 group by s1.MemberID 

Perhaps you can LINQ something like (untested):

 var qry = from row in data group row by row.MemberId into grp select new { MemberId = grp.Key, Count = grp.Count(), LastShippingName = grp.OrderByDescending(x => x.ShippingDate).First().ShippingName };