Invalid SQL generated for bool expressions

[Greg]

Using mssql when casting a bool field to bool? in where expressions 'field <> 0' is generated which is a conditional expression and not a nullable bit field. This is especially annoying when the field comes from a left-joined table and I need to treat it as nullable. E.g.

from a in db.Table1
join b in db.Table2 on ... equals ... into bg
from b in bg.DefaultIfEmpty()
join c in db.Table3 on ... equals ... into cg
from c in cg.DefaultIfEmpty()
where (bool?)b.Flag ?? (bool?)c.Flag ?? false
select a

generates ISNULL(b.[Flag] <> 0,ISNULL(c.[Flag] <> 0,(1=0))). This works properly for Nullable<int>, but not for bool. The ORM should always ignore the cast from T to Nullable<T> when generating the query.



If I write (b ?? c).Allow the generated code is ISNULL(b.[Flag], c.[ID]) -- didn't ever get the column right! -- which is not a conditional expression, syntax error again.



Also note the constant 'false' is translated to (1=0) and 'true' to (1=1) so if I use ((bool?)b.Flag ?? (bool?)c.Flag) == true the generated code is ISNULL(b.[Flag]<>0, c.[Flag]<>0)=(1=1) which is again malformed.



You should only create a predicate expression from a bit expression when required (e.g. when combining with logical operators), the pattern would be "(case when [expression]=1 then 1 else 0 end)=1" or "isnull([expression], 0)=1"



A possible workaround is this: ((b ?? c) != null ? (b ?? c).Allow : false) or (b != null ? b.Allow : c.Allow), the latter generates quite complicated code if the PK is long.

[Thomas]

Hello Greg,

thanks for bringing this to our attention! I filed a bug entry for this. Based on my experience with past issues in the bool area, I expect this will take a bit of time; Booleans are not that easy as they seem to be when you need to consider bit,bool,int,string columns that could back them. Therefore I would not like to make a promise when this issue will be fixed.

I've updated your Telerik points for this.
Thomas
Telerik

[Doroteya]

Hello Greg,

The LINQ query you need to execute can be composed as demonstrated in the next code snippet (it is based on the Northwind sample database). The SQL statement generated by Telerik Data Access, in this case, will return the correct result on condition that the boolean properties used in the where clause are set as Nullable in the model.

using (EntitiesModel dbContext = new EntitiesModel())
{
  var products = from p in dbContext.Products
            join od in dbContext.OrderDetails on p.ProductID equals od.ProductID into pod
            from od in pod.DefaultIfEmpty()
            join o in dbContext.Orders on od.OrderID equals o.OrderID into podo
            from o in podo.DefaultIfEmpty()
            where (od.Confirmed ?? o.Shipped) ?? false
            select p;
}

The generated SQL is as follows:

SELECT *
FROM [Products] a
LEFT JOIN [Order Details] AS b ON (a.[ProductID] = b.[ProductID])
LEFT JOIN [Orders] AS c ON (b.[OrderID] = c.[OrderID])
WHERE ISNULL(b.[Confirmed],c.[Shipped]) = 1
ORDER BY a.[ProductID]

Regarding the bug discovered through the tests of the query posted by you, I will make sure you are properly updated in this thread once the fix is available.

Thank you for your patience and cooperation.  



Regards,
Doroteya
Telerik

[Greg]

Just an addition to your workaround:
This works:
    where (od.Confirmed ?? o.Shipped) ?? false
These however (which is semantically the same since ?? is associative) does not:
    where od.Confirmed ?? (o.Shipped ?? false)
    where od.Confirmed ?? o.Shipped ?? false
The latter two are syntactically the same due to operator precedence.
Bool has too many issues right now. I'm going to stick with mapping stuff as int until this is fixed. I can map that as non-nullable as well.

[Greg]

And one more addition, the following pattern also generates an invalid query regardless of parentheses and associativity:

from d in db.MainTable
where
(
    (
        db.LookupTable.Where({pk match based on MainTable values}).Select({bool? field}).FirstOrDefault()
        ??
        db.LookupTable.Where({other pk match based on MainTable values}).Select({bool? field}).FirstOrDefault()
    )
    ??
    false
)
select d

Again, OA detects correctly that the lookup subqueries will produce a single value, but the bool expression has a syntax error. It either does not add '=1' to the end or adds =(1=1).

[Doroteya]

Hi Greg,

Thank you for your feedback.

Indeed, your observation about syntactically the same where clauses, and the different results they produce is correct. This effect is caused by a bug on our side, which was logged after your initial report.

Regarding the last LINQ query, I confirm that we successfully reproduced the behaviour you describe. Unfortunately, I was unable to define a workaround about it.

Let me assure you that at the time present all the scenarios you experienced issues with were logged on our side, and would be taken into consideration when we design any fixes in this direction. Additionally, I will make sure to update this thread as soon as I have any suggestions for you, or as soon as the fix is available for download.

Thank you in advance for your understanding and cooperation.


Regards,
Doroteya
Telerik