Wrong SQL generated with joined subqueries

[Greg]

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.

[Greg]

Issue still present in Q2 release (2012.2.607.1)

[Thomas]

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

[Greg]

Still not fixed as of 2013.2.702.
Do you actually work on this at all?

[Alexander]

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

[Greg]

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)

[Doroteya]

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

[Greg]

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.

[Doroteya]

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