The LINQ expression 'Expression' could not be translated. Either rewrite the query in a form that can be translated

Question

I have looked at a lot of similar questions but none could give me a solution so I am thinking if anyone can help me with this problem. I have a hierarchy of entities as Clients have multiple ClientRateDeals and then I am trying to fetch only those clients that have a list of client rate deals that all pass some condition. Here's my LINQ query that generating an error :

            var query = _context.Client.Where(c=>c.Disabled==false)
                  .GroupJoin(_context.ClientRateDeal.Where(crd=>crd.Disabled==false),
                  c => c.Id,
                  crd => crd.ClientId,
                  (c, crd) => new
                  {
                      c,
                      crd = crd.Where(cr => cr.DateEnd == null || cr.DateEnd > DateTime.Today)
                  })
                  .Where(res =>  res.crd.Count() == 0)
                  .Select(cl => cl.c).AsNoTracking().ToList();

as you can see in the result selector argument I have kept that condition and then a where clause on the result selector to fetch only those whose client rate deal whose count is 0. However due to some reason I am getting the exception that the LINQ cant be translated. Can anyone help me with this ?

Answer 1

For unknown reason (it has nothing in similar with GroupBy), LINQ GroupJoin operator is not supported in EF Core 3.x, 5.x.

You have to use one of the available alternatives - (1) collection navigation property (preferred) or (2) correlated subquery.

e.g.

(1) In Client class define

public ICollection<ClientRateDeal> ClientRateDeals { get; set; }

and use it inside the query

var query = _context.Client
    .Where(c => c.Disabled==false)
    // vvv
    .Where(c => !c.ClientRateDeals.Any(
        crd => crd.Disabled == false &&
        (crd.DateEnd == null || crd.DateEnd > DateTime.Today)))
    .AsNoTracking().ToList();

or (2)

var query = _context.Client
    .Where(c => c.Disabled==false)
    // vvv
    .Where(c => !_context.ClientRateDeal.Any(crd =>
        c.Id == crd.ClientId &&
        crd.Disabled == false &&
        cr.DateEnd == null || cr.DateEnd > DateTime.Today))
    .AsNoTracking().ToList();

In general, instead of

db.As.GroupJoin(db.Bs, a => a.Id, b => b.AId, (a, Bs) => new { a, Bs })

use

db.As.Select(a => new { a, Bs = db.Bs.Where(b => a.Id == b.AId) })

Related github issue (please go vote in order to have a chance to get that implemented):

Query with GroupBy or GroupJoin throws exception #17068

Query: Support GroupJoin when it is final query operator #19930

even though the second is not exactly what we need (we want just GroupJoin to be translated as it was written in correlated subquery syntax shown above).