Libs employed:
Devart.Data.dll => 5.0.1878.0
Devart.Data.MySql.dll => 8.10.1086.0
Devart.Data.MySql.Design.dll => 8.10.1086.0
Devart.Data.MySql.Entity.EF6.dll => 8.10.1086.0
EntityFramework => 6.2.0
We are targeting .net4.7.1 from an ASP.NET MVC5 project. Our repositories are build on top of an EF .edmx (db-first approach). Our queries look like so:
var results = _db.NB_FILTERS
.Select(x => new ReportFiltersDTO { IsHiddenSubFilter = x.NFS_SUBFILTER_YN ?? false })
.ToList();
NFS_SUBFILTER_YN is declared as a boolean in our .edmx aka:
<Property Name="NFS_SUBFILTER_YN" Type="boolean" />
The NB_FILTERS ddl is like so:
CREATE TABLE NB_FILTERS (
[...]
NFS_SUBFILTER_YN BIT(1) NULL,
[...]
)
In such a scenario if certain rows are null the given linq expression returns 'true' for all of them instead of 'false'. The culprit appears to be in the auto-generated sql:
actual => CASE WHEN Extent1.NFS_SUBFILTER_YN IS NULL THEN 0 ELSE Extent1.NFS_SUBFILTER_YN END
corrected => CASE WHEN Extent1.NFS_SUBFILTER_YN IS NULL THEN 1 ELSE CAST(Extent1.NFS_SUBFILTER_YN AS SIGNED) END
Sidenote:
Interestingly enough such queries work when the columns involved are nullable decimal(x,y) columns [the auto-generated sql correctly employs CAST(... AS SIGNED)]. It appears that somehow bit(x) was omitted from the list of types which are eligible for such handling.
[LINQ2SQL BUG] Nullable BIT(1) columns have their null-value rows always evaluated to true for 'COLUMN_NAME ?? false'
Re: [LINQ2SQL BUG] Nullable BIT(1) columns have their null-value rows always evaluated to true for 'COLUMN_NAME ?? false
Thank you for your report. We will notify you when the issue is fixed.
Re: [LINQ2SQL BUG] Nullable BIT(1) columns have their null-value rows always evaluated to true for 'COLUMN_NAME ?? false
The bug with using BIT(1) column in the CASE clause in EF1/EF4/EF5/EF6 is fixed: viewtopic.php?f=2&t=37092.