This is a migrated thread and some comments may be shown as answers.
Wrong SQL generated for nullable navigation properties
3 Answers 10 Views
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 16 Mar 2015, 03:57 PM
Using OA latest (2015.1.225)
Using the Northwind sample database (model generated without any alterations)
The following query:

from c in db.Categories
join e in db.Employees on c.CategoryID equals e.EmployeeID
where e.Employee1.FirstName == "Andrew"
select e;

generates the following SQL:

SELECT ...
FROM [Categories] a
JOIN [Employees] AS b ON (a.[CategoryID] = b.[EmployeeID])
JOIN [Employees] AS c ON (b.[ReportsTo] = c.[EmployeeID])
WHERE (c.[FirstName] = 'Andrew')

However since the ReportsTo column (corresponding to the Employee1 navigation property) is nullable the second join should either be LEFT JOIN or the following query should be generated:

SELECT ...
FROM [Categories] a
JOIN [Employees] AS b ON (a.[CategoryID] = b.[EmployeeID])
WHERE EXISTS
(
    SELECT 1234567
    FROM [Employees] c
    WHERE b.[ReportsTo] = c.[EmployeeID]
    AND c.[FirstName] = 'Andrew'
)

As a temporary workaround I can write an explicit exists clause:

from c in db.Categories
join e in db.Employees on c.CategoryID equals e.EmployeeID
where db.Employees.Any(e2 => e2 == e.Employee1 && e2.FirstName == "Andrew")
select e;

or possibly

var q0 = db.Employees.Where(e => e.FirstName == "Andrew");
from c in db.Categories
join e in db.Employees on c.CategoryID equals e.EmployeeID
where q0.Contains(e.Employee1)
select e;

This bug might be related to http://www.telerik.com/forums/invalid-sql-generated-with-navigation-properties-and-defaultifempty

3 Answers, 1 is accepted

Sort by
0
Thomas
Telerik team
answered on 18 Mar 2015, 03:45 PM
Hi Greg,

I believe the issue is a different one: in your query, you state explicitly that you only want employees whose manager go by the name "Andrew". Hence a strict join can be applied and a left join could yield wrong results.

Regards,
Thomas
Telerik
 
OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
 
0
Greg
Top achievements
Rank 1
answered on 18 Mar 2015, 04:10 PM
Seems that I omitted a condition for brevity. The following query should return employees 1,2,3,4,5,8 but it only returns 1,3,4,5,8 and not 2. 
from c in db.Categories
join e in db.Employees on c.CategoryID equals e.EmployeeID
where e.FirstName == "Andrew" || e.Employee1.FirstName == "Andrew"
select e


0
Thomas
Telerik team
answered on 19 Mar 2015, 11:05 AM
Hello Greg,

yes, there is an issue when an OR is used, and you already found the workaround with the .Any clause.
Unfortunately I cannot promise when this issue will be fixed, the code is somewhat complex there.

Regards,
Thomas
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
Thomas
Telerik team
Greg
Top achievements
Rank 1
Share this question
or