CODEWITHSUNDEEP
c#linq.net-coreentity-framework-corelinq-to-sql
Shubham Tiwari
Shubham Tiwari

Reputation: 1171

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

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 ?

Upvotes: 1

Views: 3239

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205579

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

Upvotes: 5

Related Questions