T-SQL to LINQ to SQL using Navigation Properties

Question

I can’t seem to come up with the right corresponding LINQ to SQL statement to generate the following T-SQL. Essentially, I'm trying to return payment information with only one of the customer's addresses... the AR address, if it exists, then the primary address, if it exists, then any address.

SELECT < payment and address columns >
FROM   Payment AS p
       INNER JOIN Customer AS c ON c.CustomerID = p.CustomerID
       OUTER APPLY (
                       SELECT   TOP 1 < address columns >
                       FROM     Address AS a
                       WHERE    a.person_id = c.PersonID
                       ORDER BY CASE WHEN a.BusinessType = 'AR' THEN 0
                                     ELSE 1
                                END
                              , a.IsPrimary DESC
                                END
                   ) AS pa
WHERE  p.Posted = 1

We’re using the Repository Pattern to access the DB, so inside a method of the Payment Repository, I’ve tried:

var q = GetAll()
            .Where(p => p.Posted == true)
            .SelectMany(p => p.Customer
                              .Address
                              .OrderBy(a => a.BusinessType != "AR")
                              .ThenBy(a => a.Primary != true)
                              .Take(1)
                              .DefaultIfEmpty()
            .Select(a => new
            {
                < only the columns I need from p and a >
            });

But when I execute .ToList(), it throws the NullReferenceException (Object reference not set to an instance of an object) on a record where the customer has no addresses set up. So, I tried:

var q1 = GetAll().Where(p => p.Posted == true);

var q2 = q11.SelectMany(p => p.Customer
                              .Address
                              .OrderBy(a => a.BusinessType != "AR")
                              .ThenBy(a => a.Primary != true));

var q3 = q1.SelectMany(p => q2.Where(a => a.PersonID == p.Customer.PersonID)
                              .Take(1)
                              .DefaultIfEmpty()
           .Select(a => new
           {
                < only the columns I need from p and a >
           });

This returns the correct results, but the T-SQL it generates puts the entire T-SQL from above into the outer apply, which is then joined again on Payment and Customer. This seems somewhat inefficient and I wondered if it could be made more efficient because the T-SQL above returns in 6ms for the test case I’m using.

Additional Info:
Q: I think the problem here is that GetAll() returns IEnumerable, not IQueryable ... it would help to see this GetAll() method. - Gert Arnold
A: Actually, GetAll(), when traced all the way back, returns Table<TEntity> System.Data.Linq.GetTable<TEntity>() and Table<TEntity> does implement IQueryable.

However, DefaultIfEmpty() does return IEnumerable<Address>, which is what is throwing the exception, if I'm not mistaken, as I mentioned in the first L2S code section.

SOLUTION UPDATE

Okay, I knew I could fall back to simply going straight to joining the tables and foregoing the use of the navigation properties, and in this case, I now know that is how it should be done. It all makes sense now. I just had become accustomed to preferring using the navigation properties, but here, it’s best to go straight to joining tables.

The reason the T-SQL generated by the second L2S code section was so inefficient was because in order to get to the Address table, it required the inclusion of the Payment/Customer data.

When I simply go straight to joining the tables, the generated T-SQL, while not ideal, is much closer to the desired script code section. That’s because it didn’t require the inclusion of the Payment/Customer data. And that’s when the “well, duh” light bulb flashed on.

Thanks for all who helped on this path to discovery!

Answer 1

When trying a similar query it turned out that this DefaultIfEpty() call knocks down LINQ-to-SQL. The exception's stack trace shows that things go wrong in System.Data.Linq.SqlClient.SqlBinder.Visitor.IsOuterDependent, i.e. during SQL query building.

Contrary to your conclusion it's not advisable to abandon the use of navigation properties and return to explicit joins. The question is: how to use the best parts of LINQ (which includes nav properties) without troubling LINQ-to-SQL. This, by the way, is true for each ORM with LINQ support.

In this particular case I'd switch to query syntax for the main query and use the keyword let. Something like:

from p in context.Payments
let address = p.Customer
    .Addresses
    .OrderBy(a => a.BusinessType != "AR")
    .ThenBy(a => a.Primary != true)
    .FirstOrDefault()
select new 
{
    p.PropertyX,
    address.PropertyY
    ...
}

This will be translated into one SQL statement and it avoids LINQ-to-SQL's apparent issue with DefaultIfEmpty.