Entity Framework: How to perform left join with EF and LINQ among multiple tables

Question

basically i have 3 tables and those are user,colors and usercolor

tables info

User Tables has fields like -> UserID, UserName

Color Tables has fields like -> ColorID, ColorName

UserColor Tables has fields like -> UserID, ColorID

i have corresponding dbset classes in my code.

now see the below query where left join is performed among 3 tables in sql and tell me how to write the same equivalent query with EF and LINQ.

select c.ColorID
    , c.ColorName
    , IsSelected = case when uc.ColorID is null then 0 else 1 end
from dbo.Colors c
left join dbo.UserColor uc on uc.ColorID = c.ColorID and uc.UserID = 1 --leave this in the join or it becomes an inner join
left join dbo.Users u on u.UserID = uc.UserID

You shouldn't use join in LINQ syntax. Use navigation properties. If you need help with that, please show the class definitions. — Gert Arnold, Sep 26 '16 at 14:57
If you really want to do this manually, take a look here http://stackoverflow.com/questions/37885001/how-to-left-join-multiple-tables-with-linq — kiziu, Sep 26 '16 at 14:58
@kiziu as u said it seems various way we can do this things. please discuss if we can solve it various way. thanks — Monojit Sarkar, Sep 26 '16 at 15:05
@GertArnold if u see my table info area then u can understand how class would look like in c# and accordingly just post a sample code to perform join with linq and EF. thanks — Monojit Sarkar, Sep 26 '16 at 15:07
Which part of "You shouldn't use join in LINQ syntax" wasn't clear to you? You've been given it on a plate now, but it's not the best solution. — Gert Arnold, Sep 26 '16 at 20:57

Sampath · Accepted Answer · 2016-09-26T18:35:26.647

2

You can try as shown below.

var result = from c in dbo.Colors 
             join uc in dbo.UserColor on (uc.ColorID = c.ColorID and uc.UserID = 1) into UserColor
             from q in UserColor.DefaultIfEmpty() join u in dbo.Users 
             on q.UserID equals u.UserID into Users
             from l in Users.DefaultIfEmpty()
             select new
               {
                 ColorID = c.ColorID,
                 ColorName = c.ColorName,
                 IsSelected = uc.ColorID == null ? 0 : 1
               };

You can read more about Left Outer Join in LINQ to Entities

edited Sep 26 '16 at 18:35

answered Sep 26 '16 at 15:25

Sampath

63,341
64
307
441

how do i know that left outer join has been used? when we use left outer join then we use left keyword in sql but what keyword is used when we compose the same with EF and LINQ ? how one should be able to know the query is left outer join query compose with EF and LINQ ? – Monojit Sarkar Sep 26 '16 at 18:29
i will let u know tomorrow after checking your query. thanks – Monojit Sarkar Sep 26 '16 at 18:45
OK sure.No problem :) – Sampath Sep 26 '16 at 18:45
how to identity linq query that it is left outer join? what special keyword is used to indicate it is left outer join linq query? – Monojit Sarkar Sep 27 '16 at 09:04
`DefaultIfEmpty()`.you can read more about it using above url too. – Sampath Sep 27 '16 at 09:30
u did not answer for this question "how to identity linq query that it is left outer join? what special keyword is used to indicate it is left outer join linq query?" – Monojit Sarkar Sep 27 '16 at 09:39
if you see the above query,you can see that,there is a pattern.you have to use that pattern with the `DefaultIfEmpty()` keyword.that is it.no other magic keys here. – Sampath Sep 27 '16 at 09:46
url is now leading to random chrome plugin store page. – Xiang Wei Huang May 08 '23 at 06:25

Entity Framework: How to perform left join with EF and LINQ among multiple tables

tables info

1 Answers1