How to perform left join in linq with multiple table joins
I have the following request LinQ
:
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 a list of emails if the hero belongs to a certain category and if he is admin or the hero is super admin.
When I try to write the above request in sql
using the left join, I get 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 do a simple left join in the above linq query.
source to share
Something like this, but you have to take care of nulls in the where clause, because if you do left join
, then why are you filtering and not counting 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>();
source to share
As you test the values โโof the Heroes in the sentence WHERE
, you've effectively turned yours LEFT JOIN
into Heroes INNER JOIN
. The only one you LEFT JOIN
need is the one that is on HeroCategory
, and then only if the Hero is not a superadmin (Again, because you check the value HeroCategory
QuestionCategoryId
if they are not SuperAdmin
).
You need to correct your logic first, but here's the equivalent, assuming users for Heros are one-to-many, and HeroCategories
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 for Heros are a 1: 1 ratio, then it would be something 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();
source to share