Linq query NullReferenceException on multiple (cascade) left joins

Question

I'm using a Linq query to get customers and their optional main address (a customer can have zero or more addresses). The object hierarchy is as follows:

• Customer
  • CustomerAddress (contains boolean property Main)
    • Address

This is the query I'm using:

var qry = from customer cus in GetDBContext(c).customer
    join cusadd in GetDBContext(c).customeraddress on new { cus_code = cus.cus_code, main = "1" } equals new { cus_code = cusadd.cus_code, main = cusadd.Main_addr } into grpcusadd
    from cusadd in grpcusadd.DefaultIfEmpty()
    join add in GetDBContext(c).address on new { addr_code = cusadd.Addr_Code } equals new { addr_code = add.Addr_Code } into grpadd
    from add in grpadd.DefaultIfEmpty()
    select new { cus, cusadd, add };

var customers = qry.ToList();

When I execute it on a database (through EF) it returns the values correctly. When I execute it on a mocking context with in memory objects, I get a NullReferenceException: Object reference not set to an instance of an object.

I was able to fix this error by checking for a null value in the second left join because the first left join returns null values:

join add in GetDBContext(c).address on new { addr_code = cusadd == null ? null : cusadd.Addr_Code } equals new { addr_code = add.Addr_Code } into grpadd

I've found a blogpost with the same conclusion but no explanation: http://technologycraftsmen.net/blog/2010/04/14/multiple-outer-joins-in-linq-to-sql/

Why does this query fail on local objects and not on a database?

Should cascade left outer joins always be written like this in Linq?

Thanks for your feedback!

Answer 1

Linq is wonderful, but no abstraction is perfect. This is a case where the underlying abstractions are leaking out a bit.

When the expression is executed with the real context, it is converted into a Transact SQL statement using a LEFT JOIN. The expression is never actually executed in the CLR, everything happens in the database. Even if there are no matching records in the right table the query succeeds.

When executing the query against your mocked context, the actual query execution is happening in the CLR and not in the database. At that point, the expression operates just as if you had written non-LINQ C# code. That means a test against a property of an object which is null will throw the NullReferenceException that you are seeing.

It might be helpful to imagine what would happen if this was just a join between two sequences like:

var customers = new List<Customer> { new Customer { Id = 1, Name = "HasAddress" }, new Customer { Id = 2, Name = "HasNoAddress" } };

var addresses = new List<Address> { new Address { Id = 1, CustomerId = 1, Street = "123 Conselyea Street" } };


var customerAddresses = from Customer cus in customers
                        join address in addresses on cus.Id equals address.CustomerId into grouped
                        from ca in grouped.DefaultIfEmpty()
                        select new { CustomerId = cus.Id, Name = cus.Name, Street = ca.Street };

The assignment "Street = ca.Street" will throw a NullReferenceException, because our second customer has no matching address. How would we fix that? With a ternary operator just like you included in your fix to the cascading join:

var customerAddresses = from Customer cus in customers
                        join address in addresses on cus.Id equals address.CustomerId into grouped
                        from ca in grouped.DefaultIfEmpty()
                        select new { CustomerId = cus.Id, Name = cus.Name, Street = (ca != null) ? ca.Street : null };

In the case where you are mocking the context, your cascading join isn't a Transact SQL join, it's just a normal C# usage of objects and sequences as in my example.

I don't know of any way to write your code to be oblivious of the differences in rules between execution in the CLR and execution in a database. It might be possible to do something tricky by providing a default instance to the DefaultIfEmpty method call, but that seems hacky to me.

Hopefully this will be improved once we have the null propagating operator in C#.