How to Filter Records After GroupBy with LINQ + EF Core 6

Question

Given this simple Contract entity and assuming that a customer can have many Contracts with different start dates and statuses.

public class Contract {
 [Key]
 int ContractId { get; set; }
 int CustomerId { get; set; } 
 string Status { get; set; }
 DateTime ContractStartDate { get; set; }
}

I'm trying to write a query to find that latest contract for each customer and then filter out certain statuses. I need to filter the status after finding the latest contract, to ensure I'm getting their current status.

ContractID	CustomerID	ContractStartDate	Status
1	1	2022-01-01	Active
2	1	2022-31-05	Inactive
3	2	2022-01-03	Active
4	2	2022-31-07	Inactive

From the above data set, I would expect to get contracts 2 and 4 in the results. This is why I can't filter on status before grouping, because then the latest inactive row would be eliminated before I group them by customer to find the latest row.

I've tried something like, this:

var latestContracts = Query<Contract>()
    .GroupBy(grp => grp.CustomerId)
    .Select(s => s.OrderByDescending(s => s.ContractStartDate).First())
    .Where(w => w.Status == "Active");

but once I execute the query by calling ToListAsync(), etc. I get an error like this:

'The LINQ expression 'DbSet<Customer>()
    .GroupBy(c => c.CustomerId)
    .Select(g => g
        .AsQueryable()
        .OrderByDescending(e => e.
   I CC ContractStartDate)
        .First())
    .Where(e0 => e0.AccountContractTermStatus == "Active")' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

If I remove the Where call, the query works fine and gives the intended newest results. I know that the support for translating LINQ to SQL is still not fully complete, even in EF Core 6, but this seems like something that should be possible. Am I missing something simple or is this just not yet doable via LINQ?

Happy to provide more context, as I've greatly simplified a production query into something I could share publicly.

Answer 1

Try the following emulation of query which EF Core cannot translate. It is almost direct translation what EF Core should do in your case.

var dataQuery = Query<Contract>();

var latestContracts = 
    from d in dataQuery.Select(d => new { d.CustomerId }).Distinct()
    from c in dataQuery
        .Where(c => d.CustomerId == c.CustomerId)
        .OrderByDescending(c => c.ContractStartDate)
        .Take(1)
    where c.Status == "Active"
    select c;

Or using Method Chain syntax:

var dataQuery = Query<Contract>();

var latestContracts = dataQuery
    .Select(d => new { d.CustomerId })
    .Distinct()
    .SelectMany(d => dataQuery
        .Where(c => d.CustomerId == c.CustomerId)
        .OrderByDescending(c => c.ContractStartDate)
        .Take(1))
    .Where(c => c.Status == "Active");