multiple joins in linq query and

Question

Hi i have joined 4 tables in a single query. in single call im getting both User Details and Next Appointment for the User. i can get the data perfectly when user has next appointment. But the scenario when there is no next appointment for user in Appointment table it returns null values even user details also. can i get solution where no data in next appointment for user i should get user details. below is my query. pls suggest me where i have done mistake. public EncounterUserDto GetUserDetailsById(Guid userId) { DateTime currentDate = DateTime.Now; try { var userList = (_unitOfWork.Repository().GetEntityAsQuerable().Join( _unitOfWork.Repository().GetEntityAsQuerable() user => user.UserId appoint => appoint.ProviderId (user appoint) => new { user appoint }) .Select(u => new { u.user.UserId u.user.FirstName u.user.LastName u.user.Title u.appoint.ProviderId u.appoint.PatientId u.appoint.FacilityId u.appoint.AppointmentId u.appoint.AppointmentStartDateTime }).Where(us => us.UserId == userId).Join( _unitOfWork.Repository().GetEntityAsQuerable() us => us.PatientId pa => pa.PatientId (us pa) => new { us pa }) .Select(pat => new { ProviderTitle = pat.us.Title ProviderFirstName = pat.us.FirstName ProviderLastName = pat.us.LastName PatientFirstName = pat.pa.FirstName PatientLastName = pat.pa.LastName pat.us.FacilityId pat.us.PatientId pat.us.AppointmentId pat.us.AppointmentStartDateTime ProviderUserId = pat.us.UserId pat.us.ProviderId }).Join( _unitOfWork.Repository().GetEntityAsQuerable() ap => ap.FacilityId bu => bu.BusinessUnitId (ap bs) => new { ap bs }) .Select(bu => new EncounterUserDto { NextAppoinment = new AppointmentDto { AppointmentId = bu.ap.AppointmentId ProviderId = bu.ap.ProviderId PatientId = bu.ap.PatientId FacilityId = bu.ap.FacilityId StartDateTime = bu.ap.AppointmentStartDateTime.HasValue ? bu.ap.AppointmentStartDateTime : null } Patient = new PatientDto { FirstName = bu.ap.PatientFirstName LastName = bu.ap.PatientLastName } BusinessUnit = new BusinessUnitDto { Name = bu.bs.Name } FirstName = bu.ap.ProviderFirstName LastName = bu.ap.ProviderLastName Title = bu.ap.ProviderTitle }).Where(bus => bus.NextAppoinment.StartDateTime >= currentDate) ).FirstOrDefault(); return userList; } catch (Exception) { throw ; } }

Tapan Patel · Answer

It is difficult to modify your code without knowing details about the entities but all what you need is Left join using Linq. go through below example and see how I have done it for test example. I am joining three lists based on IDs. Key point is to use DefaultIfEmpty on GroupJoin. class test1 { public int id; public string name; } class test2 { public int idX; public string nameX; } class test3 { public int idY; public string nameY; } and in the method : List lst1 = new List () { new test1() { id = 1 name = n1 } new test1() { id = 2 name = n2 } new test1() { id = 3 name = n3 } new test1() { id = 4 name = n4 } }; List lst2 = new List () { new test2() { idX = 2 nameX = n2X } new test2() { idX = 3 nameX = n3X } }; List lst3 = new List () { new test3() { idY = 2 nameY = n2Y } new test3() { idY = 3 nameY = n3Y } }; var temp = lst3.Where(x => x.idY > 0) .Select(t => t) .Join(lst2 p => p.idY q => q.idX (p q) => new { lst3Element = p lst2Element = q testName = p.nameY }) .Select(obj => new { obj.lst2Element name = obj.testName } ) // join till here to mimic what you are doing& then doing GroupJoin .GroupJoin(lst1 x => x.lst2Element.idX y => y.id (x y) => new { t1 = x t2 = y }) .SelectMany(x => x.t2.DefaultIfEmpty() (x y) => new { ft1 = x.t1 ft2 = x.t2 } ); var nextID = temp.FirstOrDefault().ft2.FirstOrDefault().name; Variable temp would contain the two elements with id 2 and 3 and it contains the info from other list two. If you need to swap elements in join and make the appropriate change in inner elements.