How to add a custom MemberTranslator for DateOnly

[stevenmoberg]

The SqlServerDateTimeMemberTranslater does not work with DateOnly even if a value converter exists to change it to DateTime. I've created my own for translator for DateOnly but cannot find any documentation about adding custom IMemberTranslators to EF Core

It would be nice to be able to provide custom translators in ModelConfigurationBuilder for given type properties

System.InvalidOperationException : The LINQ expression 'DbSet<JournalEntry>()
    .Where(j => j.EntryDate.Year == __year_0 && (int)j.AccountType == (int)__accountType_1)' could not be translated. 

Taken frrom https://github.com/dotnet/efcore/blob/main/src/EFCore.SqlServer/Query/Internal/SqlServerMemberTranslatorProvider.cs

public class DateOnlyMethodCallTranslatorProvider : RelationalMemberTranslatorProvider
    {
        public DateOnlyMethodCallTranslatorProvider(RelationalMemberTranslatorProviderDependencies dependencies) : base(dependencies)
        {
            ISqlExpressionFactory expressionFactory = dependencies.SqlExpressionFactory;

            AddTranslators(
                new IMemberTranslator[]
                {
                    new DateOnlyMemberTranslator(expressionFactory)
                }
            );
        }
    }

    public class DateOnlyMemberTranslator : IMemberTranslator
    {
        private static readonly Dictionary<string, string> DatePartMapping = new()
        {
            { nameof(DateOnly.Year), "year" },
            { nameof(DateOnly.Month), "month" },
            { nameof(DateOnly.DayOfYear), "dayofyear" },
            { nameof(DateOnly.Day), "day" }
        };

        private readonly ISqlExpressionFactory _sqlExpressionFactory;
        public DateOnlyMemberTranslator(ISqlExpressionFactory sqlExpressionFactory) 
        {
            _sqlExpressionFactory = sqlExpressionFactory;
        }

        public SqlExpression Translate(SqlExpression instance, MemberInfo member, Type returnType, IDiagnosticsLogger<DbLoggerCategory.Query> logger)
        {
            var declaringType = member.DeclaringType;

            if (declaringType == typeof(DateOnly))
            {
                var memberName = member.Name;

                if (DatePartMapping.TryGetValue(memberName, out var datePart))
                {
                    return _sqlExpressionFactory.Function(
                        "DATEPART",
                        new[] { _sqlExpressionFactory.Fragment(datePart), instance! },
                        nullable: true,
                        argumentsPropagateNullability: new[] { false, true },
                        returnType);
                }
            }

            return null;
        }
    }

EF Core version: 6.0.5 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Windows 10 IDE: Visual Studio 2022 17.2.2

[roji]

The easiest option to register your own translator is to replace SqlServerMethodCallTranslatorProvider by calilng ReplaceService in your context configuration, e.g.:

public class BlogContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(...)
            .ReplaceService<IMethodCallTranslatorProvider, MyMethodCallTranslatorProvider>();
}

Your MyMethodCallTranslatorProvider would extend SqlServerMethodCallTranslatorProvider, adding your translator(s).

If you'd like to share this across multiple projects, you can also write a plugin; see the SQL Server NetTopologySuite plugin as an example for the required infrastructure.