EFCore enum to string value conversion not used in where clause

Question

I am having a problem with how my Linq where clause gets translated into Sql.

I am using EnumToStringConverter to map a property of my entity that is an enum into a text db column. This all works fine when just querying my entity from the DbContext.

I then started using LinqKit and Expressions to have reusable filters. I created an Expression that accepts my entity and gives my enum as a result of some calculations on other properties of the entity. I'll try to explain myself with code since words are failing me. I'll write an example so I don't have to post the full code, but the logic will be the same. You can find a GitHub repo with a project to replicate the issue here: https://github.com/pinoy4/efcore-enum-to-string-test

The model classes:

public class MyEntity
{
    public Guid Id { get; set; }
    public MyEnum Status { get; set; }
    public DateTime DueAtDate { get; set; }
}

public MyEnum
{
    New = 0,
    InProgress = 1,
    Overdue = 2
}

The FluentAPI configuration

public class MyEntityConfiguration : IEntityTypeConfiguration<MyEntity>
{
    public void Configure(EntityTypeBuilder<MyEntity> builder)
    {
        // irrelevant parts of configuration skipped here

        builder.Property(e => e.Status)
            .HasColumnName("status")
            .IsRequired()
            .HasConversion(new EnumToStringConverter<MyEnum>());
    }
}

The Linq Expressions are generated with static methods. A have two:

public static class MyExpressions
{
    public static Expression<Func<MyEntity, MyEnum>> CalculateStatus(DateTime now)
    {
        /*
         * This is the tricky part as in one case I am returning
         * an enum value that am am setting here and in the other
         * case it is an enum value that is taken from the entity.
         */
        return e => e.DueAtDate < now ? MyEnum.Overdue : e.Status;
    }

    public static Expression<Func<MyEntity, bool>> GetOverdue(DateTime now)
    {
        var calculatedStatus = CalculateStatus(now);
        return e => calculatedStatus.Invoke(e) == MyEnum.Overdue;
    }
}

Now that we have the above code I write a query as such:

var getOverdueFilter = MyExpressions.GetOverdue(DateTime.UtcNow);
DbContext.MyEntities.AsExpandable().Where(getOverdueFilter).ToList();

This gets translated into the following SQL:

SELECT ... WHERE CASE
  WHEN e.due_at_date < $2 /* the date that we are passing as a parameter */
  THEN 2 ELSE e.status
END = 2;

The problem is that the CASE statement is comparing 'Overdue' (which it correctly translated using the EnumToStringConverter) with an expression that gives a int (2 is the value for the MyEnum.Overdue case) when true and a string (e.status) when false. This is obviously invalid SQL.

I don't really know how to fix this. Any help?

Answer 1

The issue is not related to LinqKit, but the expression itself, specifically the conditional operator and current EF Core 2 query translation and value conversions.

The problem is that currently value conversions are specified per property (column) rather than per type. So in order to be translated correctly to SQL, the translator must "infer" the constant / parameter type from a property. It does that for most of the type of expressions, but not for conditional operator.

So the first thing you should do is to report it to the EF Core issue tracker.

Regarding workaround:

Unfortunately the functionality is inside an infrastructure class called DefaultQuerySqlGenerator, which is inherited by every database provider. The service provided by that class can be replaced, although in a bit complicated way, which can be seen in my answer to Ef-Core - What regex can I use to replace table names with nolock ones in Db Interceptor, and additionally has to be done for every database provider you want to support.

For SqlServer it requires something like this (tested):

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.EntityFrameworkCore.Query.Sql;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal;

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomDbContextOptionsBuilderExtensions
    {
        public static DbContextOptionsBuilder UseCustomSqlServerQuerySqlGenerator(this DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<IQuerySqlGeneratorFactory, CustomSqlServerQuerySqlGeneratorFactory>();
            return optionsBuilder;
        }
    }
}

namespace Microsoft.EntityFrameworkCore.SqlServer.Query.Sql.Internal
{
    class CustomSqlServerQuerySqlGeneratorFactory : SqlServerQuerySqlGeneratorFactory
    {
        private readonly ISqlServerOptions sqlServerOptions;
        public CustomSqlServerQuerySqlGeneratorFactory(QuerySqlGeneratorDependencies dependencies, ISqlServerOptions sqlServerOptions)
            : base(dependencies, sqlServerOptions) => this.sqlServerOptions = sqlServerOptions;
        public override IQuerySqlGenerator CreateDefault(SelectExpression selectExpression) =>
            new CustomSqlServerQuerySqlGenerator(Dependencies, selectExpression, sqlServerOptions.RowNumberPagingEnabled);
    }

    public class CustomSqlServerQuerySqlGenerator : SqlServerQuerySqlGenerator
    {
        public CustomSqlServerQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies, SelectExpression selectExpression, bool rowNumberPagingEnabled)
            : base(dependencies, selectExpression, rowNumberPagingEnabled) { }
        protected override RelationalTypeMapping InferTypeMappingFromColumn(Expression expression)
        {
            if (expression is UnaryExpression unaryExpression)
                return InferTypeMappingFromColumn(unaryExpression.Operand);
            if (expression is ConditionalExpression conditionalExpression)
                return InferTypeMappingFromColumn(conditionalExpression.IfTrue) ?? InferTypeMappingFromColumn(conditionalExpression.IfFalse);
            return base.InferTypeMappingFromColumn(expression);
        }
    }
}

and for PostgreSQL (not tested):

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query.Expressions;
using Microsoft.EntityFrameworkCore.Query.Sql;
using Microsoft.EntityFrameworkCore.Storage;
using Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure.Internal;
using Npgsql.EntityFrameworkCore.PostgreSQL.Query.Sql.Internal;

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomDbContextOptionsBuilderExtensions
    {
        public static DbContextOptionsBuilder UseCustomNpgsqlQuerySqlGenerator(this DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<IQuerySqlGeneratorFactory, CustomNpgsqlQuerySqlGeneratorFactory>();
            return optionsBuilder;
        }
    }
}

namespace Npgsql.EntityFrameworkCore.PostgreSQL.Query.Sql.Internal
{
    class CustomNpgsqlQuerySqlGeneratorFactory : NpgsqlQuerySqlGeneratorFactory
    {
        private readonly INpgsqlOptions npgsqlOptions;
        public CustomNpgsqlQuerySqlGeneratorFactory(QuerySqlGeneratorDependencies dependencies, INpgsqlOptions npgsqlOptions)
            : base(dependencies, npgsqlOptions) => this.npgsqlOptions = npgsqlOptions;
        public override IQuerySqlGenerator CreateDefault(SelectExpression selectExpression) =>
            new CustomNpgsqlQuerySqlGenerator(Dependencies, selectExpression, npgsqlOptions.ReverseNullOrderingEnabled);
    }

    public class CustomNpgsqlQuerySqlGenerator : NpgsqlQuerySqlGenerator
    {
        public CustomNpgsqlQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies, SelectExpression selectExpression, bool reverseNullOrderingEnabled)
            : base(dependencies, selectExpression, reverseNullOrderingEnabled) { }
        protected override RelationalTypeMapping InferTypeMappingFromColumn(Expression expression)
        {
            if (expression is UnaryExpression unaryExpression)
                return InferTypeMappingFromColumn(unaryExpression.Operand);
            if (expression is ConditionalExpression conditionalExpression)
                return InferTypeMappingFromColumn(conditionalExpression.IfTrue) ?? InferTypeMappingFromColumn(conditionalExpression.IfFalse);
            return base.InferTypeMappingFromColumn(expression);
        }
    }
}

Besides the boilerplate code, the fix is

if (expression is UnaryExpression unaryExpression)
    return InferTypeMappingFromColumn(unaryExpression.Operand);
if (expression is ConditionalExpression conditionalExpression)
    return InferTypeMappingFromColumn(conditionalExpression.IfTrue) ?? InferTypeMappingFromColumn(conditionalExpression.IfFalse);

inside InferTypeMappingFromColumn method override.

In order to have effect, you need to add UseCustom{Database}QuerySqlGenerator anywhere you use Use{Database}, e.g.

.UseSqlServer(...)
.UseCustomSqlServerQuerySqlGenerator()

or

.UseNpgsql(...)
.UseCustomNpgsqlQuerySqlGenerator()

etc.

Once you do that, the translation (at least for SqlServer) is as expected:

WHERE CASE
    WHEN [e].[DueAtDate] < @__now_0
    THEN 'Overdue' ELSE [e].[Status]
END = 'Overdue'