Invalid SQL generated with OR and subqueries

[Greg]

Speaking in Northwind terms consider the following LINQ query:

var q = from c in db.Categories
      where db.Products.Any(p => p.CategoryID == c.CategoryID)
      || db.Suppliers.Any(s => s.CompanyName == c.CategoryName)
      select c.CategoryID;

Quite simple, isn't it? This results in approximately the followinf query:

SELECT c.[CategoryID] AS COL1
FROM [Categories] c
WHERE EXISTS
(
    SELECT p.[ProductID]
    FROM [Products] p
    WHERE (p.[CategoryID] = c.[CategoryID] OR p.[CompanyName] = c.[CategoryName])
)

See the problem? You're doing an optimization that can only be done if the two tables with Any() are the same. Which is not the case here. If I'm really fortunate the columns referenced in table#2 do not exist in table#1 and I get an exception. If I'm not I get wrong data!
The same query generates semantically correct SQL if I replace || with && but again it makes some tweaking that has no performance gain in most cases and even worse performance depending on the complexity of the Any() predicate:

SELECT c.[CategoryID] AS COL1
FROM [Categories] c
WHERE EXISTS
(
    SELECT p.[ProductID]
    FROM [Products] p
    WHERE p.[CategoryID] = c.[CategoryID]
    AND EXISTS
    (
        SELECT s.[SupplierID]
        FROM [Suppliers] s WHERE s.[CompanyName] = 'AAA'
    )
)

This is the SQL I would expect, almost literally the same as the LINQ query:

SELECT c.[CategoryID] AS COL1
FROM [Categories] c
WHERE EXISTS
(
    SELECT p.[ProductID]
    FROM [Products] p
    WHERE p.[CategoryID] = c.[CategoryID]
)
{AND|OR} EXISTS
(
    SELECT s.[SupplierID]
    FROM [Suppliers] s
    WHERE s.[CompanyName] = c.[CategoryName]
)

Using OpenAccess 2013.2.702.

[Viktor Zhivkov]

Hello Greg,

You have hit an unreported bug with our LINQ implementation. I am sorry for the inconvenience caused.
We have already started investigation for the cause of the issue and we will do our best to build a fix in our future releases.
Meanwhile you can use the following work-around (albeit it is less performant than the query you are expecting):

1.var query = from c in db.Categories
2.        join p in db.Products on c.CategoryId equals p.CategoryID into j1
3.        from loj1 in j1.DefaultIfEmpty()
4.        join s in db.Suppliers on c.CategoryName equals s.CompanyName into j2
5.        from loj2 in j2.DefaultIfEmpty()
6.        where loj1 != null || loj2 != null
7.        select c.Id).Distinct();

This query produces the following SQL statements:

1.SELECT DISTINCT a.[id] AS COL1
2.FROM [category] a
3.LEFT JOIN [product] AS b ON (a.[id] = b.[id2])
4.LEFT JOIN [supplier] AS c ON (a.[category_name] = c.[company_name])
5.WHERE (b.[id] IS NOT NULL OR c.[id] IS NOT NULL)

If this work around is not acceptable in your scenario, please let us know what your concerns are and if there are any additional restrictions that we are not aware of currently.

As recognition for reporting a new issue I am adding 500 Telerik points to your account.
I will let you know as soon as the fix for this issue is available publicly.

Regards,
Viktor Zhivkov
Telerik

[Greg]

It seems that replacing Any(...) with Count(...)>0 does the trick which have several advantages over the suggested workaround:
- The new query looks almost exactly like the original, the intent is not obstructed
- The generated SQL has the exact same execution plan and performance characteristics (at least on MSSQL)
- The query stays composable. The workaround suggested in the previous post is always evaluated on the client if reused in another query

[Viktor Zhivkov]

Hello Greg,

I am glad that you were able to find a different work around for this issue.
The issue with the invalid query when using .Any() and OR has been fixed and it produces the same SQL that you have suggested in your last code snippet. You can expect that fix to be shipped with the next public release of OpenAccess.
Unfortunately the fix for the issue with AND is more tricky and will require more effort.
I will make sure to notify you as soon as these fixes are publicly available.

Regards,
Viktor Zhivkov
Telerik

[Doroteya]

Hello Greg,

Thank you for your patience.

Versions Q3 2013 SP2 and higher of Telerik Data Access contain a fix, which allows you to use the Any() method in combination with an OR clause. With it, the following LINQ query:

var q = from c in db.Categories
      where db.Products.Any(p => p.CategoryID == c.CategoryID)
      || db.Suppliers.Any(s => s.CompanyName == c.CategoryName)
      select c.CategoryID;

is translated to:

SELECT a.[CategoryID] AS COL1
FROM [Categories] a
WHERE (EXISTS (SELECT b.[ProductID]
          FROM [Products] b
          WHERE b.[CategoryID] = a.[CategoryID])
    OR EXISTS (SELECT c.[SupplierID]
           FROM [Suppliers] c
           WHERE c.[CompanyName] = a.[CategoryName]))

Note that, at the time present, the support of the combination Any() and AND is not yet introduced. Once, there is a version of Telerik Data Access that offers it is available, I will make sure to update this thread.

I hope you find this feasible. If you need further assistance, do not hesitate to get back to us.


Regards,
Doroteya
Telerik