Invalid SQL generated with navigation properties and DefaultIfEmpty

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

    Posted 30 Oct 2013 Link to this post

    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. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 01 Nov 2013 Link to this post

    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!
  3. DevCraft banner
  4. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 03 Mar 2014 Link to this post

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