Error on LINQ query with sub query in left join-CodePudding

I was trying to do a sub query inside a left join using LINQ, it looks like this, in SQL:

SELECT fields
FROM table1 A
LEFT JOIN table2 B ON B.Establishment = A.Establishment  
LEFT JOIN table3 C ON C.Vdb = B.Vdb AND C.Year = (SELECT MAX(Year) FROM table3 D WHERE D.Vdb = C.Vdb)

Using LINQ, I did the following:

var query = await (
    from a in _context.Table1
    
    join b in _context.Table2 on a.Establishment equals b.Establishment
    
    join c0 in _context.Table3 on b.Vdb equals c0.Vdb into gGroup
    from c in gGroup.Where(x => x.Year == (from c1 in _context.Table3
                                            where c1.Vdb == x.Vdb
                                            select c1.Year).Max()).DefaultIfEmpty()
                                            
    select new
    {
        fields
    })
    .ToListAsync();

I built this code using LINQPad, so I was trying run there and everything was going fine, but when I put this code in my IDE and tried to run it I got the following error:

{
  "Message": "The LINQ expression (expression here) 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.",
  "ErrorName": "InvalidOperationException"
}

So I don't know exactly what's wrong so I can fix this, can anyone help me?

CodePudding user response：

GroupJoin is almost non translatable by EF Core. Do not use this operator if you do not do simple LEFT JOIN.

I have rewritten query to use another technique which returns the same result:

var table3 = _context.Table3;

var latest = 
    from t in table3.Select(t => new { t.Vdb }).Distinct()
    from r in table3
      .Where(r => r.Vdb == t.Vdb)
      .OrderByDescending(r.Year)
      .Take(1)
    select r;

var query = 
    from a in _context.Table1
    join b in _context.Table2 on a.Establishment equals b.Establishment into gb
    from b in gb.DefaultIfEmpty()
    join c in latest on b.Vdb equals c.Vdb into gc
    from c in gc.DefaultIfEmpty()
    select new
    {
        // fields
    };