Left Outer Join works in LINQPad (connected using EF 4.3) but fails in application

Question

I have an application that successfully utilizes LINQ to perform LEFT OUTER JOINs several instances; however, in one case it fails to work as expected.

Testing in LINQPad (using LINQ-to_SQL) produced the correct result; however, to be sure I changed to the LINQPad beta version 4.42.05 and successfully connected using my application's DLL and the connectionString from its web.config file (as per the Add Connection dialog). Again, LINQPad is successful in returning the proper result and clearly generates the expected left outer join in TSQL but the same code in the application fails.

In debugging the function, I get the "Object reference not set to an instance of an object." error. Please see additional descriptions after the following code and related TSQL. Note the relationship involves customer who have one-or-more stores and whoes stores have zero-or-more departments. Therefore, some returned records will not have a department (hence the requirement for the left outer join).

The following code works perfectly in LINQPad:

var model = (from h in SalesOrderHeaders
        join c in Customers on h.CustomerId equals c.CustomerId
        join s in Stores on h.StoreId equals s.StoreId
        join d in Departments on h.DepartmentId equals d.DepartmentId into outer
        from o in outer.DefaultIfEmpty()
        select new 
        {
            OrderId = h.SalesOrderHeaderId,
            OrderDetailId = 1,
            SalesOrderDate = h.SalesOrderDate,
            DeliveryDateTime = h.DeliveryDateTime,
            Customer = c.Customer,
            Store = s.Store,
            Department = (o.Department == null) ? "None" : o.Department,
            FullDescription = "None",
            Qty = 0,
            UoM = "None",
        }).OrderBy (m => m.OrderId);

When the code below is used in the application it fails:

var model = from h in headers
        join c in customers on h.CustomerId equals c.CustomerId
        join s in stores on h.StoreId equals s.StoreId
        join d in departments on h.DepartmentId equals d.DepartmentId into outer
        from o in outer.DefaultIfEmpty()
        select new  SalesOrderGridViewModel
        {
            OrderId = h.SalesOrderHeaderId,
            OrderDetailId = 1,
            SalesOrderDate = h.SalesOrderDate,
            DeliveryDateTime = h.DeliveryDateTime,
            Customer = c.Name,
            Store = s.Name,
            Department = (o.Name == null) ? "None" : o.Name,
            FullDescription = "None",
            Qty = 0,
            UoM = "None",
        };

However, when I change the code in the application so that the boolean in the assignment of the department field of the result references the join element from the headers variable (h.DepartmentId == null) as in the following code:

var model = from h in headers
        join c in customers on h.CustomerId equals c.CustomerId
        join s in stores on h.StoreId equals s.StoreId
        join d in departments on h.DepartmentId equals d.DepartmentId into outer
        from o in outer.DefaultIfEmpty()
        select new  SalesOrderGridViewModel
        {
            OrderId = h.SalesOrderHeaderId,
            OrderDetailId = 1,
            SalesOrderDate = h.SalesOrderDate,
            DeliveryDateTime = h.DeliveryDateTime,
            Customer = c.Name,
            Store = s.Name,
            Department = (h.DepartmentId == null) ? "None" : o.Name,
            FullDescription = "None",
            Qty = 0,
            UoM = "None",
        };

The expected result is returned.

Interestingly the subtle difference in the TSQL generated first from the original code:

SELECT [t4].[SalesOrderHeaderId] AS [OrderId], [t4].[SalesOrderDate], 
   [t4].[DeliveryDateTime], [t4].[Customer], [t4].[Store], 
   [t4].[value] AS [Department]
FROM (
    SELECT [t0].[SalesOrderHeaderId], [t0].[SalesOrderDate], 
       [t0].[DeliveryDateTime], [t1].[Customer], [t2].[Store], 
        (CASE 
            WHEN [t3].[Department] IS NOT NULL THEN [t3].[Department]
            ELSE CONVERT(NVarChar(50),@p0)
         END) AS [value]
    FROM [SalesOrderHeaders] AS [t0]
    INNER JOIN [Customers] AS [t1] ON [t0].[CustomerId] = [t1].[CustomerId]
    INNER JOIN [Stores] AS [t2] ON [t0].[StoreId] = ([t2].[StoreId])
    LEFT OUTER JOIN [Departments] AS [t3] 
      ON [t0].[DepartmentId] = ([t3].[DepartmentId])) AS [t4]
ORDER BY [t4].[SalesOrderHeaderId]

And here from the revised code, where the boolean was changed to test the value of the DepartmentId in the original headers table ([t3].[Department] versus [t0].[DepartmentId] ), appears to be the solution:

SELECT [t4].[SalesOrderHeaderId] AS [OrderId], [t4].[SalesOrderDate], 
   [t4].[DeliveryDateTime], [t4].[Customer], [t4].[Store], 
   [t4].[value] AS [Department]
FROM (
    SELECT [t0].[SalesOrderHeaderId], [t0].[SalesOrderDate], 
       [t0].[DeliveryDateTime], [t1].[Customer], [t2].[Store], 
        (CASE 
            WHEN [t0].[DepartmentId] IS NOT NULL THEN [t3].[Department]
            ELSE CONVERT(NVarChar(50),@p0)
         END) AS [value]
    FROM [SalesOrderHeaders] AS [t0]
    INNER JOIN [Customers] AS [t1] ON [t0].[CustomerId] = [t1].[CustomerId]
    INNER JOIN [Stores] AS [t2] ON [t0].[StoreId] = ([t2].[StoreId])
    LEFT OUTER JOIN [Departments] AS [t3] 
      ON [t0].[DepartmentId] = ([t3].[DepartmentId])) AS [t4]
ORDER BY [t4].[SalesOrderHeaderId]

While I have found a way to make this work; because it works both ways in LINQPad and successfully in numerous other LINQ queries scattered throughout my application, its failure in the original form in this one location gives me concern.

Ultimately, it appears to fail in the application when I test the returned value of the left outer join. However, this is the documented practice in many books and articles. So my final question is does anyone have any insight into why this would occur and/or how it can work in LINQPad (using the applications DLL and against the same DB)?

Answer 1

This is a classic example of how not to write LINQ queries - thinking in SQL and then transliterating into LINQ.

With LINQ, you can avoid the joins completely and formulate your query as follows:

from h in SalesOrderHeaders
orderby h.OrderId
select new
{
    OrderId = h.SalesOrderHeaderId,
    OrderDetailId = 1,
    SalesOrderDate = h.SalesOrderDate,
    DeliveryDateTime = h.DeliveryDateTime,
    c.Customer.Customer,
    s.Store.Store,
    Department = (h.Department == null) ? "None" : h.Department.Department,
    FullDescription = "None",
    Qty = 0,
    UoM = "None"
}

Answer 2

It appears that [t0].DepartmentID (from SalesOrderHeaders) can be null. Your LEFT OUTER JOIN relies on this value. This ends up comparing [t3].DepartmentID to null (in some cases).

You might need to use your CASE statement from the select in the JOIN as well.