We are trying to upgrade our project to .net core 5. And now we're struggling with a strange EF behaviour. A simple query:
context.Set<Order>().Where(o => o.Archived == false)
where Order.Archived is bit NOT NULL in the database, generates such a query:
select * from Order where Archived <> CAST(1 as bit)
Why does this happen? We are expecting to have query like the following instead:
select * from Order where Archived = CAST(0 as bit)
Such inversion may prevent proper indices from being used by the database in more complex queries. Is there a way to make EF generate a more straightforward query?
P.S.: We also tried this same approach with nullable fields. And there we do receive the expected query.
I looked into this and for nullable properties it works well, as expected. For non-nullable properties I found this trick, which gives expected result
context.Set<Order>().Where(o => new []{false}.Contains(o.Archived))
In that case we receive following sql query:
select * from Order where Archived = CAST(0 as bit)