How to perform a group join in .NET Core 3.0 Entity Framework?

Question

With the changes to .NET Core 3.0 I am getting

... NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.) ---> System.InvalidOperationException: Processing of the LINQ expression 'GroupJoin, ...

This is a really simple query so there must be a way to perform it in .NET CORE 3.0:

 var queryResults1 = await patients             .GroupJoin(                 _context.Studies,                 p => p.Id,                 s => s.Patient.Id,                 (p, studies) => new                  {                     p.DateOfBirth,                     p.Id,                     p.Name,                     p.Sex,                    Studies =studies.Select(s1=>s1)                 }             )             .AsNoTracking().ToListAsync(); 

I am basically looking for a Linq query (or method syntax as above) which will join Studies onto Patients, and set Studies to an empty list or null if there are no studies for the given patient.

Any ideas? This was working in .NET Core 2.2. Also the MSFT link above mentions that the key breaking change is related to client side evaluation and avoiding that the generated query reads entire tables which must then be joined or filtered client side. However with this simple query, the join should be easily doable server side.

Answer 1

As discussed here, you're attempting a query that isn't supported by the database. EF Core 2 used client-side evaluation to make your code work, but EF Core 3 refuses, because the client-side convenience comes at the cost of hard-to-debug performance problems as the dataset increases.

You can use use DefaultIfEmpty to left join the patients' studies and then group manually with ToLookup.

var query =     from p in db.Patients     join s in db.Studies on p.Id equals s.PatientId into studies     from s in studies.DefaultIfEmpty()     select new { Patient = p, Study = s };  var grouping = query.ToLookup(e => e.Patient); // Grouping done client side 

The above example grabs the full Patient and Study entities, but you can cherry pick columns instead. If the data you need from Patient is too big to repeat for each Study, in the joined query select only the Patient ID, querying the rest of the Patient data in a separate non-joined query.

Answer 2

Had exactly the same issue and a big struggle with it. It turns out that .net Core 3.0 does not support Join or Groupjoin in method syntax (yet?). The fun part is though, it does work in Query syntax.

Try this, it's query syntax with a bit of method syntax. This translates nicely to the correct SQL query with a nice left outer join and it is processed on the database. I haven't got your models so you need to check the syntax yourselves....

var queryResults1 =      (from p in _context.patients     from s in _context.Studies.Where(st => st.PatientId == p.Id).DefaultIfEmpty()     select new     {         p.DateOfBirth,         p.Id,         p.Name,         p.Sex,         Studies = studies.Select(s1 => s1)     }).ToListAsync();