multiple joins in linq query and

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

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.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
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.