I'm trying to do a simple group join where my result set should be a List of Doctors who each have a List of Patients. It's also possible that a Doctor has no patients.
For some reason I'm getting the following exception when I run the query below.
Processing of the LINQ expression 'DbSet<ApplicationUser>
.GroupJoin(
outer: DbSet<ApplicationUser>,
inner: doc => doc.Id,
outerKeySelector: p => p.DoctorId,
innerKeySelector: (doc, doc_patientsGroup) => new {
doc = doc,
doc_patientsGroup = doc_patientsGroup
})' by '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.
var q = from doc in _dbContext.Users
join p in _dbContext.Users on doc.Id equals p.DoctorId into doc_patientsGroup
join ur in _dbContext.UserRoles on doc.Id equals ur.UserId
join r in _dbContext.Roles on ur.RoleId equals r.Id
where r.Name == Roles.DOCTOR
select new
{
Id = doc.Id,
UserName = doc.UserName,
Name = string.Format("{0} {1}", doc.FirstName, doc.LastName),
NumPatients = doc_patientsGroup.Count(), //<-- DOESN'T LIKE THIS
};
var qq = await q.ToListAsync();
//model
public class ApplicationUser : IdentityUser
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string DoctorId { get; set; }
public ApplicationUser Doctor { get; set; }
public List<ApplicationUser> Patients { get; set; }
}
Can anyone explain how I should modify my query? Thanks,