c# - Left Join on Linq to Entity issue -

i need extract data orders table not assigned , assigned orders in different table orders_assigned. below linq entity code.

---

issue: not getting unassigned orders, rather gets both unassigned , assigned orders. below linq code has issues including , clause {and [extent2].[chem_id] null}. suggestions how correct sql in linq entity code.

var query = objorder in context.orders                             join objorderassigned in context.orders_assigned                             on new { key1 = objorder.chem_id, key2 = objorder.order_nbr } equals new { key1 = objorderassigned.chem_id, key2 = objorderassigned.order_no } temptbl                             temp in temptbl.defaultifempty()                             objorder.order_status == "new"                             select new order                             {                                 compoundid = temp.chem_id,                                 orderno = objorder.order_nbr,                                 route = objorder.route_cd,                             }; 

when sql profile, see 1 clause missing , not sure missing here

sql generated sql profile above linq entity code :

select  [project1].[chem_id] [chem_id],  [project1].[order_nbr] [order_nbr],  [project1].[route_cd] [route_cd],  [project1].[c1] [c1] ( select      [extent1].[chem_id] [chem_id],      [extent1].[order_nbr] [order_nbr],      [extent1].[route_cd] [route_cd],      n'' [c1]      [dbo].[orders] [extent1]     left outer join [dbo].[orders_assigned] [extent2] on ([extent1].[chem_id] = [extent2].[chem_id]) , ([extent1].[order_nbr] = [extent2].[order_no])     'new' = [extent1].[order_status] )  [project1] 

sql produces correct data

select  [project1].[chem_id] [chem_id],  [project1].[order_nbr] [order_nbr],  [project1].[route_cd] [route_cd],  [project1].[c1] [c1] ( select      [extent1].[chem_id] [chem_id],      [extent1].[order_nbr] [order_nbr],      [extent1].[route_cd] [route_cd],      n'' [c1]      [dbo].[orders] [extent1]     left outer join [dbo].[orders_assigned] [extent2] on ([extent1].[chem_id] = [extent2].[chem_id]) , ([extent1].[order_nbr] = [extent2].[order_no])     'new' = [extent1].[order_status]     , [extent2].[chem_id] null )  [project1] 

below linq code has issues including , clause {and [extent2].[chem_id] null}.

you forgot include null check in linq query where clause. specific here can't check temp.chem_id property because c# compiler perspective it's non nullable, have apply check on temp object instead (ef smart enough translate correct sql).

here working query:

var query =     objorder in context.orders     join objorderassigned in context.orders_assigned     on new { key1 = objorder.chem_id, key2 = objorder.order_nbr }     equals new { key1 = objorderassigned.chem_id, key2 = objorderassigned.order_no }     assignedorders     objorderassigned in assignedorders.defaultifempty()     objorder.order_status == "new" && objorderassigned == null     select new order     {         compoundid = objorder.chem_id,         orderno = objorder.order_nbr,         route = objorder.route_cd,     };