Invalid SQL generated with navigation properties and DefaultIfEmpty

[Greg]

Using the Northwind sample db the following query:

from c in db.Customers
from o in c.Orders.DefaultIfEmpty()
where c.CustomerID == "FISSA"
select new { Customer = c, Order = o };

generates the following sql:

SELECT a.[CustomerID] AS COL1, b.[OrderID] AS COL2
FROM [Customers] a
JOIN [Orders] AS b ON (a.[CustomerID] = b.[CustomerID])
WHERE a.[CustomerID] = 'FISSA'
ORDER BY COL1

That join should be a left outer join, not an inner join. This query should return exactly 1 row with Customer={customer record with Id=FISSA} and Order=null. Instead it returns 0 rows. No exception or warning, just plain wrong results!
Possible workaround is to substitute the navigation property with an explicit left join:

from c in db.Customers
join o in db.Orders on c equals o.Customer into og
from o in og.DefaultIfEmpty()
where c.CustomerID == "FISSA"
select new { Customer = c, Order = o };

Using OA 2013.3.1014

[Alexander]

Hello Greg,

You are right, at the moment collections of related persistent objects are always joined with an inner join. I have added an entry to our backlog to provide support for DefaultIfEmpty() directly on a persistent collection, however I am not able to give you a timeframe when it will be implemented. You have already found out the correct workaround.

Thank you for reporting this issue, your Telerik points have been updated.

Regards,
Alexander
Telerik

[Doroteya]

Hello Greg,

Thank you for your patience.

At the time present, the DefaultIfEmpty() support on collection navigation properties is not released. However, you can achieve the needed LEFT JOIN (and respectively the result) with the help of our Fetch Plans feature. Basically, it allows you to query for the objects related to a given one, in just one statement. The following example utilizes the Include extension method of Telerik Data Access, but you could also use the rest of the methods and patterns provided by the Fetch Plans API:

using Telerik.OpenAccess;

using (EntitiesModel db = new EntitiesModel())
{
    var result = from c in db.Customers.Include(c => c.Orders)
                where c.CustomerID == "FISSA"
                select c;

}

The generated SQL statement would be similar to this one:

SELECT a.*, b.*
FROM [Customers] a
LEFT JOIN [Orders] AS b ON (a.[CustomerID] = b.[CustomerID])
WHERE a.[CustomerID] = 'FISSA'
ORDER BY a.[CustomerID], b.[CustomerID]

Note that the extension method is in the Telerik.OpenAccess namespase, and you may need to add a using clause in your code.

Additionally, I will make sure to update this thread as soon as the support of DefaultIfEmpty() on collection navigational properties is available.

I hope you find this feasible. If you need further assistance, do not hesitate to get back to us.



Regards,
Doroteya
Telerik