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):
Expected results (91 rows, one for each row in dbo.Customers):
Actual results (0 rows):
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.
8 Answers, 1 is accepted
the Telerik team
Do you actually work on this at all?
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.
This generates an invalid query: the OrderDetails extent is being moved outside the exists subquery:
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)
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: With it the SQL statement generated by Telerik Data Access would be: 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.
Using plain ADO is also not possible because I need to expose this as IQueryable.
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.