Linq NullReferenceException query for multiple (cascading) left joins

Question

I use the Linq query to get clients and their additional primary address (a client can have zero or more addresses). The hierarchy of objects is as follows:

• Customer
  • CustomerAddress (contains the main boolean property)
    • 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 in the database (via EF), it returns the values ​​correctly. When I execute it in a mocking context with memory objects, I get a NullReferenceException: Object reference not set on the object instance.

I was able to fix this error by checking the 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 found a blog post with the same conclusion, but did not explain: http://technologycraftsmen.net/blog/2010/04/14/multiple-outer-joins-in-linq-to-sql/

Why does this query fail in local objects and not in the database?

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

Thanks for the feedback!

Answer 1

The link is great, but the abstraction is not perfect. This is the case when basic abstractions leak out a bit.

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

When you run a query against your mocking context, the actual query is executed in the CLR, not in the database. At this point, the expression acts just as if you had written LINQ C # code. This means that a test against an object property that is null will throw a NullReferenceException that you see.

It might be useful to imagine what would happen if it was just a connection between two sequences, such as:

 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 }; 

A destination of "Street = ca.Street" will throw a NullReferenceException because our second client does not have a matching address. How do we fix this? With the triple operator, just like you included a cascading connection in your fix:

 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 when you mock the context, your cascading join is not a Transact SQL join, it is just a normal use of C # objects and sequences, as in my example.

I do not know how to write code, so as not to forget about the differences in the rules between execution in the CLR environment and execution in the database. You might be able to do something complicated by providing a default instance to call the DefaultIfEmpty method, but that seems hacked to me.

We hope this will be improved if we have a zero distribution operator in C #.