0

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).

Community
  • 1
  • 1
Tarostar
  • 1,196
  • 1
  • 15
  • 27

1 Answers1

1

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

xanatos
  • 109,618
  • 12
  • 197
  • 280
  • Yes there is a relationship, every Order has a reference to a member, and every member has a list of all their orders (sorry. but my database terminology is a bit rusty). – Tarostar May 27 '15 at 08:33
  • From my initial testing this seems to work beautifully and has put me on the trail to learn more about SELECT INTO as that seems very useful. Any significant performance or resource usage penalty I should be aware of creating a temporary variable this way? – Tarostar May 27 '15 at 08:51
  • 1
    @Tarostar You aren't creating a temporary variable. You can look at the generated query in Visual Studio by looking at the `failedOrders` variable. It will be a nested query (a SELECT of a SELECT). If you don't need the last order (so you do a `select mlo.Member`) it is possible to remove the `select new ... into` and move the `m.Orders...FirstOrDefault()` directly in the `where`. Probably the performance are the same. – xanatos May 27 '15 at 08:53