Why EF Core generates inverted bit comparison

Question

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().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.

user_7408879 · Answer

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().Where(o => new []{false}.Contains(o.Archived))

In that case we receive following sql query:

select * from Order where Archived = CAST(0 as bit)

Why EF Core generates inverted bit comparison

1 Answers