!boolean in query should convert to boolean == false rather than boolean != true where possible

[davesmits]

Ask a question

We have in most of our tables a property bool Deleted (not nullable); and you guess in most of our queries we do Where(x => !x.Deleted).

No i notice that the query gets translated to Deleted <> 1 which is for sql different then Deleted == 0 making it for sql harder to use certain indexes. Are there any guidlines towards this?

[joakimriedel]

Out of curiosity, what happens if you do Convert.ToBoolean(x.IsDeleted) == false ? It will translate to CONVERT(bit, [x].[IsDeleted]) = CAST(0 AS bit) but don't know if the redundant convert and cast will hurt performance or get optimized away.

[roji]

No i notice that the query gets translated to Deleted <> 1 which is for sql different then Deleted == 0 making it for sql harder to use certain indexes. Are there any guidlines towards this?

Can you point to any resources on Deleted <> 1 being harder for indexes than Deleted == 0?

[davesmits]

@roji i dont have documentation just personal investigation on a perf problem we had

Query generated by EF Core

SELECT [c].[Id], [c].[AssignedToId], [c].[CaseStateId], [c].[ContentModifiedAt], [c].[CreatedAt], [c].[CreatedByUserId], [c].[Deleted], [c].[Description], [c].[ModifiedAt], [c].[ModifiedByUserId], [c].[Tags], [c].[TenantId], [c].[Title]
      FROM [Cases] AS [c]
      WHERE ([c].[Deleted] <> CAST(1 AS bit)) AND ([c].[TenantId] = '40C53C5C-528E-4A60-BC04-0AA23616DA80')
      ORDER BY [c].[CreatedAt] DESC
      OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

Resulted in this query plan

Change the Deleted<> 1 to Deleted = 0 ressulted in

and the last one is also using a index i was expecting

[davesmits]

@joakimriedel Changing the query to x.Deleted == false is already resolving our issue. The issue is only there when using !x.Deleted

[joakimriedel]

@davesmits that's interesting. For me running EF Core 5 x.IsDeleted == false will translate to [x].[IsDeleted] <> CAST(1 AS bit) which is why I suggested the convert solution.

[davesmits]

@joakimriedel sorry; we didnt upgrade yet to 5.0. sorry for forgetting to mention we are in 3.1; working on the upgrade but some breaking changes in identity part making it hard

[joakimriedel]

@davesmits OK one more thing to add for the upgrade to-do list then, if you choose to do x.IsDeleted == false as a workaround for !x.IsDeleted.

Not to hijack this issue but @roji do you know why x.IsDeleted == false translates differently in 3.1 and 5.0 ? It's not in breaking changes document.

[roji]

@joakimriedel I'm not sure exactly why this change was done, but we occasionally change/improve the precise SQL constructs which get generated; this shouldn't be a breaking change in any case.

However, the different perf characteristics indeed seem important and we should look into it. Thanks for that info @davesmits!

[joakimriedel]

@roji very minor impact, but still breaking. Indexing a bit column is normally useless unless the data is skewed to either many 0 or many 1. In one of my queries I had a filter with a predicate similar to

create index [IX_not_opened] on dbo.Messages (Id) where Opened=0

since there would be pretty few rows with Opened=0 this index helped SQL Server to quickly filter the rows when selecting non-opened messages.

After the upgrade to 5.0 this query didn't use the index anymore, but since it's a rarely used query I didn't notice the performance degradation until this issue posted by @davesmits . Tested my workaround above and the index is now properly used again!

Thanks for looking into this!

[smitpatel]

Cannot change without #15586
The issue is bit is what is used for bool in SqlServer and conditions are bool. If you convert everything to int then bit values would be same and compare the same but that is not the knowledge query pipeline have yet.

[davesmits]

@smitpatel I am not sure what you mean convert to int? Is that a internal thing of EF.Core?

[davesmits]

thanks @joakimriedel ; we are now migrating to .net 5 and seeing indeed the problem beeing with with deleted == false . We applied your workaround as well

[ajcvickers]

Ping @maumar

[maumar]

In a5f3e26 we unified the translation for !a, a != true, a == false where a is non nullable bool. They now all translate to !a. Later, if that expression is in the predicate (on sql server) we must rewrite it to a search condition: !a -> !(a == true) -> a != true

We could special case the final step on that translation (SimplifyNegatedBinary), when comparing to bool constant so that it always uses equality, rather than non-equality. This transformation should be safe to do, assuming c# null semantics are being used - in case of negated comparisons we weed out nullability completely in the null semantics

[Chrille79]

Is there any workaround for EF 5?

SELECT TOP(1000) [t].[Id]
FROM [Transaction] AS [t]
WHERE [t].[Computed] <> CAST(1 AS bit)
ORDER BY [t].[Id]

--SQL Server Execution Times: CPU time = 17797 ms,  elapsed time = 23389 ms.
 
 
SELECT TOP(1000) [t].[Id]
FROM [Transaction] AS [t]
WHERE [t].[Computed] = CAST(0 AS bit)
ORDER BY [t].[Id]

--SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 1 ms.

The only workaround we know for the moment is going to FromSQL or not to upgrade and wait for EF 6

[joakimriedel]

Is there any workaround for EF 5?

Did you try the workaround I posted in the beginning of this thread? It helped for me.

[Chrille79]

@joakimriedel tnx, seams to work with CONVERT(bit, [t].[Computed]) = CAST(0 AS bit)
was trying all the other options.

[bbycroft]

Note that the workaround (Convert.ToBoolean(x.IsDeleted) == false) doesn't work in the case where the non-nullable bit column is used in a multi-column index (as opposed to an index filter clause).

e.g. if the index is defined as CREATE INDEX MyIdx ON MyTable (IsDeleted, CreateTime), then
WHERE CONVERT(bit, [x].[IsDeleted]) = CAST(0 AS bit) doesn't match against that index.

I'm using latest stable EF Core 5.0.9 & Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64), and I've run out of workarounds using Linq against this db structure.

[gparissis]

Note that the workaround (Convert.ToBoolean(x.IsDeleted) == false) doesn't work in the case where the non-nullable bit column is used in a multi-column index (as opposed to an index filter clause).

e.g. if the index is defined as CREATE INDEX MyIdx ON MyTable (IsDeleted, CreateTime), then WHERE CONVERT(bit, [x].[IsDeleted]) = CAST(0 AS bit) doesn't match against that index.

I'm using latest stable EF Core 5.0.9 & Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64), and I've run out of workarounds using Linq against this db structure.

Is this going to be corrected with EF Core 6? The workaround is not working for me because of the reason explained above.
I am using a filtered index on the bool column and the conversion is not using the index.

[ajcvickers]

@gparissis This is fixed in 6.0. Please try with EF Core 6.0 RC2.

[gparissis]

@gparissis This is fixed in 6.0. Please try with EF Core 6.0 RC2.

Thanks, can't upgrade to a non production version yet, because of compliance restrictions, but I just found a workaround that worked perfect. I used a value conversion and the SQL on the column is generated as [i].[voided] = 0.

What I did:
modelBuilder.Entity<ItemFact>().Property(x => x.Voided).HasConversion<int>();

The Voided is a bool property that before was translating to
[i].[voided] <> CAST(1 AS bit) but after the conversion it translated to [i].[voided] = 0 when I execute the below query
FetchCurrentFact<ItemFact, DataContext>( x => x.Key == key && x.Voided == false, ctx)

Hope it helps.

[granit1986]

@gparissis This is fixed in 6.0. Please try with EF Core 6.0 RC2.

I tried but doesn't work

[roji]

@gparissis can you share a code repro with 6.0 rc2 which shows the issue still happening?

[EhsanMrf]

Hi, I have this problem, but I tried very hard but I did not succeed
I am using this version 6.0.2

See the pictures below

Return of two records
But the duration is 14 milliseconds

But the second image
12 records returned but duration 2 milliseconds

Two points:
Tip 1: Because the IsInStoock column is not indexed
It may act slowly

Tip 2:
CAST (1 bit AS)

Question?
How problematic is this break in the above records?
@roji Rojansky