Converting a linq Join into a left join

Question

I'm attempting to convert this query

            userSummery = userSummery.Join(usersStatus, su => su.EmailAddress, us => us.EMail, (su, us) =>
                new UserSummary()
                {
                    Department = su.Department,
                    EmailAddress = su.EmailAddress,
                    FirstName = su.FirstName,
                    LastName = su.LastName,
                    FullName = su.FullName,
                    Id = su.Id,
                    PhoneNumber = su.PhoneNumber,
                    RmgUserId = su.RmgUserId,
                    IsActive = su.IsActive,
                    UserName = su.UserName,
                    Status = us.Status
                }).ToList();
            return userSummery;

to return ALL of su, even those that don't have a matching us.

I've looked at How do you perform a left outer join using linq extension methods but can't figure out exactly how to make it work with my slightly more complex case.

Slava Utesinov Slava Utesinov · Accepted Answer · 2016-06-03T12:22:10

Try this:

var userSummery = (from su in userSummery
                  join us in usersStatus
                  on su.EmailAddress equals us.EMail into sub 
                  from s in sub.DefaultIfEmpty()
                  select new
                  {
                      su,
                      status = s == null ? 0 : s.Status//or other default value instead of 0
                  }).ToList()
                  .Select(x => { x.su.Status = x.status; return x.su; }).ToList();

Converting a linq Join into a left join

1 Answers