Monojit Sarkar Monojit Sarkar - 1 year ago 144
C# Question

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

Answer Source

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