Wrong SQL generated for nullable navigation properties

[Greg]

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

[Thomas]

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

 

 

[Greg]

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

[Thomas]

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