CODEWITHSUNDEEP
c#sql-serverlinqentity-framework
seadrag0n
seadrag0n

Reputation: 848

How to perform left join in linq having multiple table joins

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.

Upvotes: 3

Views: 1485

Answers (2)

Robert McKee
Robert McKee

Reputation: 21477

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

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 1

Related Questions