Invalid SQL generated with OR and subqueries

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

    Posted 16 Aug 2013 Link to this post

    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.

  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 19 Aug 2013 Link to this post

    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
    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.
  3. DevCraft banner
  4. Greg
    Greg avatar
    44 posts
    Member since:
    Jun 2012

    Posted 22 Aug 2013 Link to this post

    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
  5. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 22 Aug 2013 Link to this post

    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
    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.
  6. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 03 Mar 2014 Link to this post

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