I am new to joins in linq to entities. I have one query in sql server and want to convert it into LINQ to Entities.
Can someone please provide the solution for the same? Do we have any online tool to convert sql queries to LINQ to entities?
SELECT R.ID,r.Name,u.UserId
FROM Roles R
Left JOIN UserRoles U ON r.Id = u.RoleId
AND [UserId] = '5'
where [UserId] IS NULL
DefaultIfEmpty will result in a left outer join, therefore as you want a simple left join you should do as follows:
var list = (from r in context.Roles
join u in context.UsersRoles on r.Id equals u.RoleId && u.UserId='5' into x
where r.UserId == null
select new
{
r.Id,
r.Name,
u.UserId
}).ToList();
var list = (from r in context.Roles
join ur in context.UsersRoles on r.Id equals ur.RoleId && ur.UserId='5' into x
from y in x.DefaultIfEmpty()
where r.UserId ==null
select new Object
{
Id = r.Id,
Name = r.Name,
ur.UserId
}).ToList();
Note: Not understand your second UserId IS NULL logic
