CODEWITHSUNDEEP
linqleft-join
Aman Jain
Aman Jain

Reputation: 665

Unable to apply left join in linq

I explore a lot and came to know that there is no direct way to apply left join in LINQ. So I got to know that use cross join for the same and then filter the records.

I tried applying that, but still not getting all the records from the left.

Here is my trial:

List<UserAttendanceHistory> lstUserAttendanceHistory = (from registeredAppUser in registeredAppUsers
                                                                    from userAttendanceHistory in lstUserAttendance.DefaultIfEmpty()
                                                                    where registeredAppUser.EmailID == userAttendanceHistory.EmailID
                                                                    //on userAttendanceHistory.EmailID equals registeredAppUser.EmailID
                                                                    select new UserAttendanceHistory()
                                                                    {
                                                                        ContactName = registeredAppUser.ContactName,
                                                                        EmailID = userAttendanceHistory.EmailID,
                                                                        Status = userAttendanceHistory.Status
                                                                    }).ToList();

I have 6 records in lstUserAttendance and 11 records in registeredAppUsers. I want all 11 records from registeredAppUsers in the output.

I know there are lots of questions available similar to this, but I posted this because I am not able to figure it out.

Any help is greatly appreciated.

Upvotes: 0

Views: 42

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205579

As described in join clause (C# Reference), the left join pattern in LINQ is like this

from a in A
join b in B on keys(a) equals keys(b) into aBs
from b in aBs.DefaultIfEmpty()
//...

Applying it to your query:

var lstUserAttendanceHistory = (
    from registeredAppUser in registeredAppUsers
    join userAttendanceHistory in lstUserAttendance
    on registeredAppUser.EmailID equals userAttendanceHistory.EmailID
    into registeredAppUserHistory
    from userAttendanceHistory in registeredAppUserHistory.DefaultIfEmpty()
    select new UserAttendanceHistory()
    {
        ContactName = registeredAppUser.ContactName,
        EmailID = userAttendanceHistory.EmailID,
        Status = userAttendanceHistory.Status
    })
    .ToList();

Upvotes: 1

Michael Blackburn
Michael Blackburn

Reputation: 3229

It is a bit convoluted. My approach is to do a Group Join where the right side (the universal segment, or Users) is the key and the left side (the conditional segment, or attendance history) is the grouped elements. SelectMany then projects a list of lists out into a single result.

var userAttendanceHistory = 
registeredAppUsers.GroupJoin(lstUserAttendance, 
rAU=>rAu.EmailID, 
lUA=>lUA.EmailId, 
(u,a) => new {user=u, attend=a})
.SelectMany(anonType=> anonType.DefaultIfEmpty(), 
(us,at) => new UserAttendanceHistory()                                                                    {ContactName = us.ContactName,
EmailID = us.EmailID,
Status = at == null ? "Not attended": at.Status
}).ToList();

Upvotes: 2

Related Questions