user6747972 user6747972 - 23 days ago 6
C# Question

Left Join on Linq to Entity issue

I need to extract data from Orders table which are not assigned and assigned orders are in a different table Orders_Assigned. Below is my Linq to Entity Code.




Issue: I am not getting just the unassigned orders, rather it gets both the unassigned and assigned orders. Below linq code has issues including the and clause {and [Extent2].[CHEM_ID] IS NULL}. Any suggestions as to how to get the correct sql in linq to entity code.

var query = from 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 } into temptbl
from temp in temptbl.DefaultIfEmpty()
where objOrder.ORDER_STATUS == "NEW"
select new Order
{
compoundid = temp.CHEM_ID,
orderno = objOrder.ORDER_NBR,
route = objOrder.ROUTE_CD,
};


When I look into the SQL Profile, I see that one the where clause is missing and not sure what I am missing here

SQL generated by SQL Profile for the above Linq to Entity code :

SELECT
[Project1].[CHEM_ID] AS [CHEM_ID],
[Project1].[ORDER_NBR] AS [ORDER_NBR],
[Project1].[ROUTE_CD] AS [ROUTE_CD],
[Project1].[C1] AS [C1]
FROM ( SELECT
[Extent1].[CHEM_ID] AS [CHEM_ID],
[Extent1].[ORDER_NBR] AS [ORDER_NBR],
[Extent1].[ROUTE_CD] AS [ROUTE_CD],
N'' AS [C1]
FROM [dbo].[ORDERS] AS [Extent1]
LEFT OUTER JOIN [dbo].[ORDERS_ASSIGNED] AS [Extent2] ON ([Extent1].[CHEM_ID] = [Extent2].[CHEM_ID]) AND ([Extent1].[ORDER_NBR] = [Extent2].[ORDER_NO])
WHERE 'NEW' = [Extent1].[ORDER_STATUS]
) AS [Project1]


SQL which produces the correct data

SELECT
[Project1].[CHEM_ID] AS [CHEM_ID],
[Project1].[ORDER_NBR] AS [ORDER_NBR],
[Project1].[ROUTE_CD] AS [ROUTE_CD],
[Project1].[C1] AS [C1]
FROM ( SELECT
[Extent1].[CHEM_ID] AS [CHEM_ID],
[Extent1].[ORDER_NBR] AS [ORDER_NBR],
[Extent1].[ROUTE_CD] AS [ROUTE_CD],
N'' AS [C1]
FROM [dbo].[ORDERS] AS [Extent1]
LEFT OUTER JOIN [dbo].[ORDERS_ASSIGNED] AS [Extent2] ON ([Extent1].[CHEM_ID] = [Extent2].[CHEM_ID]) AND ([Extent1].[ORDER_NBR] = [Extent2].[ORDER_NO])
WHERE 'NEW' = [Extent1].[ORDER_STATUS]
and [Extent2].[CHEM_ID] IS NULL
) AS [Project1]

Answer

Below linq code has issues including the and clause {and [Extent2].[CHEM_ID] IS NULL}.

You forgot to include null check in the LINQ query where clause. The only specific here is that you can't check temp.CHEM_ID property because from C# compiler perspective it's non nullable, so you have to apply the check on temp object instead (EF is smart enough to translate it to correct SQL).

Here is the working query:

var query =
    from 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 }
    into assignedOrders
    from objOrderAssigned in assignedOrders.DefaultIfEmpty()
    where objOrder.ORDER_STATUS == "NEW" && objOrderAssigned == null
    select new Order
    {
        compoundid = objOrder.CHEM_ID,
        orderno = objOrder.ORDER_NBR,
        route = objOrder.ROUTE_CD,
    };