Using Linq.Any() in a ternary, cause a sql bug

4 posts, 0 answers
  1. Lagrange
    Lagrange avatar
    16 posts
    Member since:
    Sep 2010

    Posted 22 Oct 2015 Link to this post

    Hi,

     I have an issue using a .Where() statement containing a ternary.

    Each path of the ternary return the evaluation to execute.

    FluentModel.Accounts.Where(a =>
    a.AccountType == 1 ? a.Business == _idBusiness // for directs seller, check direct business
    : a.AccountType ==2 ? a.Customers.Any(c => c.Business == _idBusiness) // for indirect seller, check their client business
    : false
    );

    This generate an invalid statement, at least on sql serveur :

    1....
    2.CASE WHEN a.[​Business] = 2
    3.                THEN EXISTS(SELECT 1 FROM ​ ....)
    4.                ELSE
    5....

    The correct expression should be :

    1....
    2.CASE WHEN a.[​Business] = 2
    3.                THEN CASE WHEN EXISTS(SELECT 1 FROM ​ ....)
    4.                    THEN 1 ELSE 0 END
    5.                ELSE
    6....

     

    I'm using the 2015.1 version of openaccess

    Regards,

  2. Lagrange
    Lagrange avatar
    16 posts
    Member since:
    Sep 2010

    Posted 22 Oct 2015 in reply to Lagrange Link to this post

    I've a workaround by using Count() > 0 instead of Any(), which produce a valid query.

    But now, I see another problem in the query :

    the generated ​query contains a JOIN to the Customers table, instead of a LEFT JOIN, so all account of type 1 who don't have customers, will not be in the resultset.

  3. DevCraft banner
  4. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 27 Oct 2015 Link to this post

    I would propose to use something along these lines:


    FluentModel.Accounts.Where(a =>
    (a.AccountType == 1 && a.Business == _idBusiness) // for directs seller, check direct business
    || (a.AccountType == 2 && a.Customers.Any(c => c.Business == _idBusiness)) // for indirect seller, check their client business
    );

    Using the ternary operator looks good in c#, but is not the way to go with SQL. Don't do to much in
    its branches!

    Regards,
    Thomas
    Telerik
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.

     
  5. Lagrange
    Lagrange avatar
    16 posts
    Member since:
    Sep 2010

    Posted 27 Oct 2015 in reply to Thomas Link to this post

    I simplified the model ​to provide an understandable repro. Real world is far more complicated with 4 successives conditions.

    Anyway, I've throw away the linq approach for this query, and written a custom sql to get the int[] rows id I want, then I get fluent objects using .Where(a => ​result.Contains(a.AccountId))

     

    Thanks for help. This post was mainly to inform you about this sql syntax bug.

Back to Top