LINQ expression could not be translated and will be evaluated locally

EntityFrameworkCore has 3 extension points that can be used to translate custom types to SQL.

• IMemberTranslator
• IMethodCallTranslator
• RelationalTypeMapping

These translators and mapppings can be registered using the corresponding plugins:

• IMemberTranslatorPlugin
• IMethodCallTranslatorPlugin
• IRelationalTypeMappingSourcePlugin

The plugins are registered with a IDbContextOptionsExtension

The following example illustrates how I have implemented these interfaces to register the custom type Semester:

IMemberTranslator

public class SqlServerSemesterMemberTranslator : IMemberTranslator
{
    public Expression Translate(MemberExpression memberExpression)
    {

        if (memberExpression.Member.DeclaringType != typeof(Semester)) {
            return null;
        }

        var memberName = memberExpression.Member.Name;

        if (memberName == nameof(Semester.FirstDay)) {
            return new SqlFunctionExpression(
                "DATEFROMPARTS",
                typeof(DateTime),
                new Expression[] {
                        new SqlFunctionExpression( "YEAR", typeof(int),new[] { memberExpression.Expression }),
                        new SqlFunctionExpression( "MONTH", typeof(int),new[] { memberExpression.Expression }),
                        Expression.Constant(1, typeof(int))
                });
        }

        if (memberName == nameof(Semester.LastDay)) {
            return new SqlFunctionExpression(
                "EOMONTH",
                typeof(DateTime),
                new Expression[] {
                        memberExpression.Expression
                });
        }

        if (memberName == nameof(Semester.HalfYear)) {
            return Expression.Convert(
                new SqlFunctionExpression(
                    "MONTH",
                    typeof(int),
                    new Expression[] {
                            memberExpression.Expression
                    }),
                typeof(HalfYear));
        }

        if (memberName == nameof(Semester.Year)) {
            return new SqlFunctionExpression(
                "YEAR",
                typeof(int),
                new Expression[] {
                        memberExpression.Expression
                });
        }

        return null;
    }

}

IMethodCallTranslator

 public class SqlServerSemesterMethodCallTranslator : IMethodCallTranslator
{
    public Expression Translate(MethodCallExpression methodCallExpression)
    {
        if (methodCallExpression.Method.DeclaringType != typeof(Period)) {
            return null;
        }

        var methodName = methodCallExpression.Method.Name;

        // Implement your Method translations here

        return null;
    }
}

RelationalTypeMapping

 public class SqlServerSemesterTypeMapping : DateTimeTypeMapping
{
    public SqlServerSemesterTypeMapping(string storeType, DbType? dbType = null) : 
        base(storeType, dbType)
    {
    }

    protected SqlServerSemesterTypeMapping(RelationalTypeMappingParameters parameters) : base(parameters)
    {
    }

    protected override RelationalTypeMapping Clone(RelationalTypeMappingParameters parameters) => new SqlServerSemesterTypeMapping(parameters);
}

IMemberTranslatorPlugin

 public class SqlServerCustomMemberTranslatorPlugin : IMemberTranslatorPlugin
{
    public IEnumerable<IMemberTranslator> Translators => new IMemberTranslator[] { new SqlServerSemesterMemberTranslator() };
}

 public class SqlServerCustomMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
{
    public IEnumerable<IMethodCallTranslator> Translators => new IMethodCallTranslator[] { new SqlServerSemesterMethodCallTranslator() };
}

IRelationalTypeMappingSourcePlugin

public class SqlServerCustomTypeMappingSourcePlugin : IRelationalTypeMappingSourcePlugin
{
    public RelationalTypeMapping FindMapping(in RelationalTypeMappingInfo mappingInfo) 
        => mappingInfo.ClrType == typeof(Semester) || (mappingInfo.StoreTypeName == nameof(DateTime))
            ? new SqlServerSemesterTypeMapping(mappingInfo.StoreTypeName ?? "datetime")
            : null;
}

After you have defined and registered the translators, you have to confgure them in the DbContext.

IDbContextOptionsExtension

public class SqlServerCustomTypeOptionsExtension : IDbContextOptionsExtensionWithDebugInfo
{
    public string LogFragment => "using CustomTypes";

    public bool ApplyServices(IServiceCollection services)
    {
        services.AddEntityFrameworkSqlServerCustomTypes();

        return false;
    }

    public long GetServiceProviderHashCode() => 0;

    public void PopulateDebugInfo(IDictionary<string, string> debugInfo) 
        => debugInfo["SqlServer:" + nameof(SqlServerCustomDbContextOptionsBuilderExtensions.UseCustomTypes)] = "1";

    public void Validate(IDbContextOptions options)
    {
    }
}

Extension Methods

public static class SqlServerCustomDbContextOptionsBuilderExtensions
{
    public static object UseCustomTypes(this SqlServerDbContextOptionsBuilder optionsBuilder)
    {
        if (optionsBuilder == null) throw new ArgumentNullException(nameof(optionsBuilder));

        // Registere die SqlServerDiamantOptionsExtension.
        var coreOptionsBuilder = ((IRelationalDbContextOptionsBuilderInfrastructure)optionsBuilder).OptionsBuilder;

        var extension = coreOptionsBuilder.Options.FindExtension<SqlServerCustomTypeOptionsExtension>()
            ?? new SqlServerCustomTypeOptionsExtension();

        ((IDbContextOptionsBuilderInfrastructure)coreOptionsBuilder).AddOrUpdateExtension(extension);

        // Configure Warnings
        coreOptionsBuilder
            .ConfigureWarnings(warnings => warnings
                .Log(RelationalEventId.QueryClientEvaluationWarning)        // Should be thrown to prevent only warnings if a query is not fully evaluated on the db
                .Ignore(RelationalEventId.ValueConversionSqlLiteralWarning));  // Ignore warnings for types that are using a ValueConverter

        return optionsBuilder;
    }
}


 public static class SqlServerServiceCollectionExtensions
{
    public static IServiceCollection AddEntityFrameworkSqlServerCustomTypes(
        this IServiceCollection serviceCollection)
    {
        if (serviceCollection == null) throw new ArgumentNullException(nameof(serviceCollection));

        new EntityFrameworkRelationalServicesBuilder(serviceCollection)
            .TryAddProviderSpecificServices(
                x => x.TryAddSingletonEnumerable<IRelationalTypeMappingSourcePlugin, SqlServerCustomTypeMappingSourcePlugin>()
                      .TryAddSingletonEnumerable<IMemberTranslatorPlugin, SqlServerCustomTypeMemberTranslatorPlugin>()
                      .TryAddSingletonEnumerable<IMethodCallTranslatorPlugin, SqlServerCustomTypeMethodCallTranslatorPlugin>());

        return serviceCollection;
    }
}

Register the option in the DbContext

dbOptionsBuilder.UseSqlServer(connectionString, builder => builder.UseCustomTypes())