Wrong SQL generated for nullable navigation properties

4 posts, 0 answers
  1. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 16 Mar 2015 Link to this post

    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
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 18 Mar 2015 Link to this post

    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.
     
  3. DevCraft banner
  4. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 18 Mar 2015 in reply to Thomas Link to this post

    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


  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 19 Mar 2015 Link to this post

    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.
     
Back to Top