How to perform left join in linq having multiple table joins

Question

I have the following LinQ query:

List<string> emails = (from u in context.User
                            join h in context.Hero
                            on u.UserID equals h.UserID
                            join hc in context.HeroCategory
                            on h.HeroID equals hc.HeroID
                            where
                            (hc.QuestionCategoryID == 1
                            && h.IsHeroAdmin) || h.IsHeroSuperAdmin
                            select u.EmailAddress).ToList<string>();

I am trying to get list of emails if a hero belongs to a certain category and if he is a admin or if the hero is super admin.

When I try to write the above query in sql using left join, I am getting the correct result:

SELECT 
    U.EmailAddress
FROM USERS U LEFT JOIN
    Heroes H ON (U.UserID=H.UserID) LEFT JOIN
    HeroCategory HC ON (H.HeroID=HC.HeroID)
WHERE (HC.QuestionCategoryID=1 AND H.IsHeroAdmin=1)
   OR H.IsHeroSuperAdmin=1

Basically I want to know to perform a simple left join in the above mentioned linq query.

Check out [this stack overflow question](http://stackoverflow.com/questions/10920653/multiple-tables-left-join-using-linq). It should provide you with your answer. — DeadZone, May 27 '15 at 13:34
http://stackoverflow.com/a/1971086/2039359 describes how to do multiple left joins — BunkerMentality, May 27 '15 at 13:49
Left join is not the problem - in that SQL the WHERE negates the left. You are missing into group LINQ syntax. — paparazzo, May 27 '15 at 14:18

Giorgi Nakeuri · Accepted Answer · 2015-05-27T15:46:10.180

Something like this, but you should take care of nulls in where clause, because if you are doing left join then why you are filtering and not consider nulls?:

List<string> emails = (from u in context.User
                       join h in context.Hero on u.UserID equals h.UserID into hleft
                       from hl in hleft.DefaultIfEmpty()
                       join hc in context.HeroCategory on hl.HeroID equals hc.HeroID into hcleft
                       from hcl in hcleft.DefaultIfEmpty()
                       where
                       (hcl.QuestionCategoryID == 1
                       && hl.IsHeroAdmin) || hl.IsHeroSuperAdmin
                       select u.EmailAddress).ToList<string>();

score 0 · Answer 2 · edited May 27 '15 at 19:28

Because you are testing the values of Heroes in your WHERE clause, you've effectively turned your LEFT JOIN on Heroes into an INNER JOIN. The only LEFT JOIN needed is the one on HeroCategory, and then only if the Hero isn't a superadmin (Again, because you test the value of HeroCategory's QuestionCategoryId if they aren't a SuperAdmin).

You need to fix your logic first, but here's the equivalent, assuming Users to Heros is a one to many relationship, and HeroCategories is one to many:

var emails=context.Users
  .Where(u=>u.Heros.Any(h=>
      (h.IsHeroAdmin==1 && h.HeroCategories.Any(hc=>hc.QuestionCategoryID==1))
          || u.Heros.Any(h=>h.IsHeroSuperAdmin==1)))
  .Select(u=>u.EmailAddress)
  .ToList();

If Users to Heros is a 1:1 relationship, then it would be like this:

var emails=context.Users
  .Where(u=>(u.Heros.IsHeroAdmin==1 && h.Heros.HeroCategories.Any(hc=>hc.QuestionCategoryID==1)) || u.Heros.IsHeroSuperAdmin==1)
  .Select(u=>u.EmailAddress)
  .ToList();

How to perform left join in linq having multiple table joins

2 Answers2