CODEWITHSUNDEEP
asp.netlinqentity-framework
Tarostar
Tarostar

Reputation: 1316

LINQ: Get all members with LAST order failed

I'm learning LINQ, and I'm trying to figure out how to get all members with the last order failed (each member can have many orders). For efficiency reasons I'd like to do it all in LINQ before putting it into a list, if possible.

So far I believe this is the right way to get all the members with a failed order which joined recently (cutoffDate is current date -10 days).

var failedOrders =
 from m in context.Members
 from o in context.Orders
 where m.DateJoined > cutoffDate
 where o.Status == Failed
 select m;

I expect I need to use Last or LastOrDefault, or possibly I need to use 

orderby o.OrderNumber descending

and then get the First or FirstOrDefault as suggested in this stackoverflow answer.

Note that I want to look at ONLY the last order for a given member and see if that has failed (NOT just find last failed order).

Upvotes: 0

Views: 43

Answers (1)

xanatos
xanatos

Reputation: 111870

Normally you would write something like:

var failedOrders = from m in context.Members
                   where m.DateJoined > cutoffDate
                   select new
                   {
                       Member = m,
                       LastOrder = m.Orders.OrderByDescending(x => x.OrderNumber).FirstOrDefault()
                   } into mlo
                   // no need for null checks here, because the query is done db-side
                   where mlo.LastOrder.Status == Failed 
                   select mlo; // or select mlo.Member to have only the member

This if there is a Members.Orders relationship

Upvotes: 1

Related Questions