Filtering by enum values with CompareTo leads to SQL query translation error

[bangiev]

You can find a runnable code sample here.

The sample includes the following filtering:

var unreadNotes = context.Notes
    .Where(n => n.Status.CompareTo(Status.Unread) == 0)
    .ToList();

The status property is an enum:

    public enum Status
    {
        Unknown,
        Unread,
        Read,
    }

The filtering produces the following exception:

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Note>()
    .Where(n => n.Status.CompareTo((object)Unread) == 0)' could not be translated. Additional information: Translation of method 'System.Enum.CompareTo' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.<Main>$(String[] args) in C:\Users\bangiev\source\repos\EfCoreIssueEnumCompareTo\EfCoreIssueEnumCompareTo\Program.cs:line 7

Provider and version information:

• EF Core version: reproduced with 5.0.6 and 6.0.1
• Database provider: Microsoft.EntityFrameworkCore.SqlServer
• Target framework: reproduced with .NET 5.0 and .NET 6.0
• Operating system: Windows 10
• IDE: Visual Studio Professional 2022 17.0.4

[roji]

@bangiev enums are saved to the database using a built-in value converter, which converts the CLR enum type to an int. Using methods over value-convertered values in queries isn't supported, that's tracked by #10434.

Note that the above can be rewritten to the simpler Where(n => n.Status == Status.Unread) which is supported.

Finally, while translating CompareTo may seem simple when value-converting to an int, it's common to value-convert enums to strings; at that point, the CompareTo call cannot really be translated - a string comparison in the database would yield different results from the local evaluation in .NET.

[bangiev]

I see, thank you @roji. Unfortunately the query expression that is using CompareTo is built by an external library in this case and can't be easily replaced.

I understand that this is being tracked as an enhancement as far as EF is concerned, so I'm closing the issue.