I encountered a new effect within the SQL code generation of the Entity Framework 4 after switching from dotConnect v8.3.135 to 8.5.464:
My scenario: Oracle table "Shows" with a column "IsBestShow" declared as Number(1,0) NOT NULL, containing several million rows. I use that column as bool, containing less than 1 promille "1" values, rest "0". There is a bitmap index on that column. I generated the EF4 model from the database. The property in the entity is created as not nullable bool, just as I need it. Now I want to retrieve all rows marked as IsBestShow:
Code: Select all
using ( Entities e = new Entities() )
{
return e.Shows.Include( "OtherTable" )
.Where( x => x.IsBestShow )
.OrderByDescending( x => x.OtherValueColumn )
.ToList();
}
WHERE "Extent1".IsBestShow = 1
This worked fine and fast, the index was used.
In v8.5 that changed:
WHERE "Extent1".IsBestShow <> 0
This works not as well because for reasons unknown, Oracle won't use the index and performs a full table scan that lasts for several hours instead of a few seconds.
My question: Does a setting exist, that revert code generation in EF to the previous version or can I achieve the old behaviour in another way?
I also tried a second approach in using a OracleFunctions.Hints(...) to force the use of the index. That didn't work, Oracle ignored the hint and the index.
Any help would be appreciated.
Best regards
Marc
Update:
Same effect with EF 6.1.3.