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.

Answer 1

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>();