CODEWITHSUNDEEP
c#entity-frameworklinq
Monojit Sarkar
Monojit Sarkar

Reputation: 2451

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

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

Upvotes: 3

Views: 10018

Answers (1)

Sampath
Sampath

Reputation: 65870

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

Upvotes: 2

Related Questions