Efcore: !boolean in query

[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 https://github.com/dotnet/efcore/issues/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