This is a migrated thread and some comments may be shown as answers.

Invalid SQL generated with navigation properties and DefaultIfEmpty

2 Answers 66 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Greg
Top achievements
Rank 1
Greg asked on 30 Oct 2013, 10:53 AM

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

2 Answers, 1 is accepted

Sort by
0
Alexander
Telerik team
answered on 01 Nov 2013, 03:17 PM
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
OpenAccess ORM Q3 2013 simplifies your model operations even further providing you with greater flexibility. Check out the list of new features shipped with our latest release!
0
Doroteya
Telerik team
answered on 03 Mar 2014, 06:58 AM
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
OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Tags
LINQ (LINQ specific questions)
Asked by
Greg
Top achievements
Rank 1
Answers by
Alexander
Telerik team
Doroteya
Telerik team
Share this question
or