CODEWITHSUNDEEP
c#sql-serverlinqentity-frameworkentity-framework-4
Jaxidian
Jaxidian

Reputation: 13509

How to query and sort by filtered Max query in EF4?

Context: EF4, C#, .NET4, SQL2008/R2

Tables/entities to repro problem:

Pseudocode (nearly real code) that would be ideal to work:

DateTime startDateInclusive = xxxx;
DateTime stopDateExclusive = yyy;

var query = Db.Accounts.Include(a => a.AccountOrders.Select(ao => ao.Order.Ordereditems.Select(oi => oi.Item)))
              .Where(account =>
                     account.AccountOrders.Where(ao => ao.OrderStatus != 42)
                            .Max(ao => ao.DateToExecute).IsBetween(startDateInclusive, stopDateExclusive))
              .OrderBy(account =>
                       account.AccountOrders.Where(ao => ao.OrderStatus != 42)
                              .Max(ao => ao.DateToExecute));

var results = query.Take(5).ToList();

In English, this is looking for the next 5 accounts that have their last order to be executed within a date range. However, there are also Orders that can be cancelled, so we must exclude OrderStatus of 42 when performing that Max.

The problem revolves around this filtered Max date across many-to-many tables. An added complexity is that we need to sort by that filtered max value and we must do all of the above without breaking our eager loading (i.e. joins must be done via projection in the Where and not using a .Join). I’m not sure how to do this query without the result being 10x’s more complex than it should be. I’d hate to do the joins to filter the ao.OrderStatus/Max the DateToExecute 3 times (once for startDate, once for stopDate, and once for the sort). And clearly the IsBetween isn’t functional.

Any ideas on how to perform this query, sorted this way, in a fairly-efficient way for the generated SQL?

Upvotes: 1

Views: 140

Answers (1)

PinnyM
PinnyM

Reputation: 35531

It may be helpful to use an anonymous type here:

DateTime startDateInclusive = xxxx;
DateTime stopDateExclusive = yyy;

var query = Db.Accounts
              .Select(account => new { 
                  Account = account,
                  MaxDate = account.AccountOrders.Select(ao => ao.Order).Where(o => o.OrderStatus != 42).Max(o => o.DateToExecute) 
              })
              .Where(a => a.MaxDate >= startDateInclusive && a.MaxDate < stopDateExclusive)
              .OrderBy(a => a.MaxDate)
              .Select(a => a.Account)
              .Include(a => a.AccountOrders.Select(ao => ao.Order.Ordereditems.Select(oi => oi.Item)));

var results = query.Take(5).ToList();

This is untested as I don't have any datasource to test against. But it's probably the simplest approach for what you need to do.

Upvotes: 1

Related Questions