0

I need to group join 2 tables say Patients and Studies and group by Patients, however my database is very large 100,000+ patients and multiple studies per patient.

This question is very closely related (but old ) question How to perform a group join in .NET Core 3.0 Entity Framework?.

The answer to that question proposes the following query,

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

which overcomes the exception

... 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, ...:

The issue I have with the solution is that ToLookup is evaluated client side and requires loading of the whole dataset. Is there some way to perform the grouping server side, then applying paging and then obtain the lookup in order to limit amount of data retrieved by the client.

shelbypereira
  • 2,097
  • 3
  • 27
  • 50

1 Answers1

0

Try the following query:

var query =
    from p in db.Patients
    select new 
    { 
        Patient = p,
        Studies = db.Studies.Where(s => s.PatientId == p.Id).ToList()
    };
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32