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;
}
}