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

[Lagrange]

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,

[Lagrange]

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.

[Thomas]

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

 

 

[Lagrange]

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.