Wrong SQL generated with joined subqueries

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

    Posted 08 Jun 2012 Link to this post

    When I 'left join' two filtered tables the filter expression is moved to after the join operation. Example uses the NorthwindEF sample database.

    LINQ query (db is the ObjectContext):

    from c in db.Customers
    join o0 in db.Orders.Where(a => a.OrderID == -1) on c.CustomerID equals o0.CustomerID into og
    from o in og.DefaultIfEmpty()
    select new { c.CustomerID, o.OrderID };

    Expected results (91 rows, one for each row in dbo.Customers):

    SELECT a.CustomerID, b.OrderID
    FROM Customers a
    LEFT JOIN
    ( SELECT * FROM Orders WHERE b.OrderID = -1) AS b
    ON a.CustomerID = b.CustomerID

    Actual results (0 rows):

    SELECT a.CustomerID, b.OrderID
    FROM Customers a
    LEFT JOIN Orders AS b ON a.CustomerID = b.CustomerID
    WHERE b.OrderID = -1

    Note that even though there is an fk association between Customers and Orders, I'm intentionally not using it. Orders could be any query with arbitrary complexity.

  2. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 12 Jun 2012 Link to this post

    Issue still present in Q2 release (2012.2.607.1)
  3. DevCraft banner
  4. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 25 Jun 2012 Link to this post

    Yes, I must confirm that. We are aware of the issue, and will provide a fix for this. It is just not doable quickly....

    Kind regards,
    Thomas
    the Telerik team
    OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.
  5. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 16 Aug 2013 Link to this post

    Still not fixed as of 2013.2.702.
    Do you actually work on this at all?
  6. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 19 Aug 2013 Link to this post

    Hello Greg,

    We have implemented the basic functionality which enables you to define joins on non-column conditions. This means that you could add your filter directly to the join. What is left to be implemented is the automatic shift of the Where clause to the join definition.
    For more details please see my last post in this thread.

    Regards,
    Alexander
    Telerik
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
  7. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 24 Feb 2014 Link to this post

    Still not fixed in 2013 Q3 SP2 and one more join-issue:
    from p in db.Products
    where
    (
        from y in db.Orders
        join od in db.OrderDetails on new { Id = y.OrderID, ProductID = p.ProductID } equals new { Id = od.OrderID, ProductID = od.ProductID }
        select 1
    ).Any()
    select p;
    This generates an invalid query: the OrderDetails extent is being moved outside the exists subquery:
    SELECT *
    FROM [Products] a
    JOIN [Order Details] AS b ON (c.[OrderID] = b.[OrderID] AND a.[ProductID] = b.[ProductID])
    WHERE EXISTS (SELECT 1 FROM [Orders] c)
    ORDER BY COL1
    This requires that the join expression has at least two fields with one expression referencing the inner (Orders) table and the other expression not referencing said table (e.g. p.ProductID as above, constant value, etc)
  8. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 27 Feb 2014 Link to this post

    Hello Greg,

    Thank you for bringing this issue to our attention.

    Indeed Telerik Data Access does not generate the appropriate SQL statement for the demonstrated LINQ query. It does, however, offer a workaround, which allows you to retrieve only the Product objects that participate in orders. The LINQ query would like the following:
    var result = dbContext.Products
             .Where(p => dbContext.OrderDetails
                     .Any(od => od.ProductID == p.ProductID &&
                             dbContext.Orders.Any(o => o.OrderID == od.OrderID)));
    With it the SQL statement generated by Telerik Data Access would be:
    SELECT *
    FROM [Products] a
    WHERE EXISTS (SELECT b.[OrderID], b.[ProductID]  
              FROM [Order Details] b
              WHERE b.[ProductID] = a.[ProductID] AND EXISTS (SELECT c.[OrderID]
                                               FROM [Orders] c
                                               WHERE c.[OrderID] = b.[OrderID]))
    ORDER BY a.[ProductID]
    Additionally, Telerik Data Access offer the necessary features to execute a custom SQL statement that can involve JOIN clauses. The code in this case, would be a mix between plain ADO code and LINQ.

    Regarding the fix which is still unavailable, I kindly ask you to accept our apologies. Let me assure you that it is logged on our side and that we are going to evaluate it to find a suitable place for it on our roadmap.

    I hope my suggestion works for you. Do let us know if you need further assistance or have questions. 



    Regards,
    Doroteya
    Telerik
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
  9. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 03 Mar 2014 in reply to Doroteya Link to this post

    I've deliberately used a simplified query to demonstrate the issue. I actually need to join multiple tables and derive the condition from all of them which is either not possible to express with Any() and logic operators only, or uses lots of unnecessary semi-joins which is prohibitive performance-wise.
    Using plain ADO is also not possible because I need to expose this as IQueryable.
  10. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 06 Mar 2014 Link to this post

    Hi Greg,

    I am sorry to here that my suggestion is not suitable in the scenario you implement, and I understand your considerations about the usage of ADO.

    I will make sure to update this thread once there is an official version of Telerik Data Access which implements a solution.

    Thank you for your cooperation.


    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
DevCraft banner