CODEWITHSUNDEEP
c#linqleft-join
ksg
ksg

Reputation: 4067

Simple Left join in Linq not working as expected

I want to convert the following query to Linq

select m.Id,m.MenuName,mr.CanAdd,mr.CanDelete,mr.CanEdit
from Menu m
left join MenuRole mr on m.Id=mr.MenuID
and mr.DesignationID=1 where m.ParentID<>0

Expected result is

enter image description here

I have tried the following but not getting the desired result

var test = (from m in _db.Menus
                   join mr in _db.MenuRoles on m.Id equals mr.MenuID  into t
                   from rt in t.Where(x=>x.Menu.ParentID!=0).DefaultIfEmpty()
                   where rt.DesignationID==1
                   orderby m.Id 
                   select new MenuVM
                   {
                       CanAdd=rt.CanAdd==null?false:true,
                       CanEdit=rt.CanEdit==null?false:true,
                       CanDelete=rt.CanDelete==null?false:true,
                       MenuId=rt.Menu.Id,
                       MenuName=rt.Menu.MenuName                       
                   });

The issue is Employee data is not listing

Upvotes: 1

Views: 75

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I believe this should be:

from m in _db.Menus
join mr in _db.MenuRoles on m.Id equals mr.MenuID  into t
from rt in t.Where(x=>x.DesignationID==1).DefaultIfEmpty()
where m.ParentID!=0

DesignationID goes to left join condition, ParetID goes to where clause.

Select from m:

select new MenuVM
{
     CanAdd=rt.CanAdd==null?false:true,
     CanEdit=rt.CanEdit==null?false:true,
     CanDelete=rt.CanDelete==null?false:true,
     MenuId=m.Id,
     MenuName=m.MenuName                       
});

Upvotes: 2

Related Questions