IComparable/IEquatable could not be translated

[zbarnett]

I recently ran into a bit of unexpected behavior where casting to IComparable or IEquatable<> causes a previously working query to become untranslatable. It's especially unexpected that casting to object still works but IComparable does not.

I have restructured my code to work around this quirk but wanted to put it out there since I couldn't find an existing issue for it.

Minimal reproducible example

using Microsoft.EntityFrameworkCore;

using (var db = new MyContext())
{
    db.MyEntities.OrderBy(x => x.Id).ToList();                      // works
    db.MyEntities.OrderBy(x => (object)x.Id).ToList();              // works

    db.MyEntities.OrderBy(x => (IComparable)x.Id).ToList();         // breaks
    db.MyEntities.OrderBy(x => (IComparable<int>)x.Id).ToList();    // breaks
    db.MyEntities.OrderBy(x => (IEquatable<int>)x.Id).ToList();     // breaks
}

class MyContext : DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }
}

class MyEntity
{
    public int Id { get; set; }
}

Stack trace

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression 'DbSet<MyEntity>()
    .OrderBy(m => (IComparable)m.Id)' could not be translated. 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.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   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)

Include provider and version information

EF Core version: 7.0.2 & 6.0.13 Database provider: Microsoft.EntityFrameworkCore.SqlServer & Microsoft.EntityFrameworkCore.Sqlite Target framework: .NET 6.0 & .NET 7.0 Operating system: Windows 10 IDE: Visual Studio 2022 17.4.4

[ajcvickers]

@zbarnett Can you explain a bit more about why you want to do this?

[zbarnett]

Sure! We have some generic code that is helping with our searching/sorting/pagination and then we have more specific code for each of the entities we want to allow those operations on. This is mainly affecting us with sorting/searching.

Generic sorting helper code

public static IQueryable<T> BuildSortQuery<T>(IQueryable<T> query, Dictionary<string, Expression<Func<T, IComparable>>> expressions, JqueryDataTablesParameters table)
{
    foreach (var order in table.Order)
    {
        if (table.Columns[order.Column] != null)
        {
            var column = table.Columns[order.Column];
            if (expressions.TryGetValue(column.Data, out Expression<Func<T, IComparable>> expression))
            {
                if (expression != null)
                {
                    query = query.ApplySorting(expression, order.Dir);
                }
            }
        }
    }

    return query;
}

public static IQueryable<T> ApplySorting<T>(this IQueryable<T> query, Expression<Func<T, IComparable>> expression, DTOrderDir order)
{
    var ordered = query as IOrderedQueryable<T>;
    if (order == DTOrderDir.ASC)
    {
        if (query.Expression.Type == typeof(IOrderedQueryable<T>))
            return ordered.ThenBy(expression);
        return query.OrderBy(expression);
    }
    else
    {
        if (query.Expression.Type == typeof(IOrderedQueryable<T>))
            return ordered.ThenByDescending(expression);
        return query.OrderByDescending(expression);
    }
}

Entity-specific code

public static IQueryable<MyCustomType> BuildSortQuery(IQueryable<MyCustomType> query, JqueryDataTablesParameters table)
{
    var sortExpressions = new Dictionary<string, Expression<Func<MyCustomType, IComparable>>>
    {
        ["Name"] = x => x.Name,
        ["Description"] = x => x.Description
    };

    return QueryBuilderHelper.BuildSortQuery(query, sortExpressions, table);
}

I am currently working around this issue by just changing the types in these methods from IComparable to object but it would be nice to have some compile-time guarantee that the field we're trying to sort on is comparable.

[ajcvickers]

Thanks for the additional info. We will discuss this, but don't you worry that using IComparable in the signature implies that the type implementing the interface will have some impact on the behavior? That is, that it might, somehow, have an effect on the way the database does comparisons?

[zbarnett]

Yes, and I would expect the type implementing the interface could have an affect on the behavior. The piece of this that I left out since it didn't seem immediately relevant was that we're using a lot of custom types with value conversions and we want to make sure that those types are the ones implementing the required interface(s) before they can be used for searching/sorting.

[ajcvickers]

But nothing that you do with the type, including value converters or comparers, will affect what happens in the database, which is where the sorting will happen after the IQueryable is translated.

[zbarnett]

Currently, yes. But once https://github.com/dotnet/efcore/issues/9906 is addressed I could imagine that it would since I'm not sure if there would be an obvious way (by default) to have the database order by a struct with multiple fields.

[ajcvickers]

I don't believe the current plans for https://github.com/dotnet/efcore/issues/9906 include this, but I will discuss with the team.

[ajcvickers]

I discussed #9906 with the team, and it's likely that ordering by value objects that map to multiple columns will not be supported. A value object mapped to a single column will work as it does now, but obviously isn't affected in any way by the IComparable interface.