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

To solve the N+1 problem, 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, query the rest of the Patient data in a separate non-joined query.