Problem with SQL WHERE clause generated for boolean
Posted: Wed 26 May 2010 08:33
I have columns in my database as NUMBER(1) which are mapped in my entities to boolean (build with entity developer).
Fine for me.
In database, false values are 0, and true values are different from 0 (often -1).
When I checked the boolean properties of the entities, the property is true if the value in database is different from 0. Fine again.
But when I try to write a Linq expression with a boolean property it fails to return the good result.
For example : a query retrieves all diagnosis that are not obsolete.
Here a part of the SQL generated (read from dbMonitor) :
The problem is that where clause can't get the records with true value coded with -1. And it's different for the property value itself.
I use a workaround by testing d.Obsolete != false but it's a bit obvious and the risk of errors in the entire code is major.
Fine for me.
In database, false values are 0, and true values are different from 0 (often -1).
When I checked the boolean properties of the entities, the property is true if the value in database is different from 0. Fine again.
But when I try to write a Linq expression with a boolean property it fails to return the good result.
For example : a query retrieves all diagnosis that are not obsolete.
Code: Select all
var query = from d in myRepository.Diags where !d.Obsolete;
Code: Select all
SELECT ...
FROM DIAGS "Extent1"
WHERE "Extent1".FOBSOLETE 1
I use a workaround by testing d.Obsolete != false but it's a bit obvious and the risk of errors in the entire code is major.