SqlFunctions.DatePart equivalent in EF Core

Question

What is the equivalent of the following statement in EF Core?

SqlFunctions.DatePart("week", x.MyDate)

EF.Functions doesn't seem to have a DatePart method.

Answer 1

It is possible to make use of the datepart SQL function by wrapping it with the DbFunctionAttribute. Tricky part is to tell ef core not to handle the datepart type parameter as a string. Example:

DbContext ef core <= 2.1:

public int? DatePart(string datePartArg, DateTime? date) => throw new Exception();

public void OnModelCreating(DbModelBuilder modelBuilder) {
    var methodInfo = typeof(DbContext).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) });
    modelBuilder
        .HasDbFunction(methodInfo)
        .HasTranslation(args => new SqlFunctionExpression(nameof(DatePart), typeof(int?), new[]
                {
                        new SqlFragmentExpression(args.ToArray()[0].ToString()),
                        args.ToArray()[1]
                }));
}

DbContext ef core >= 3.1 (static SqlFunctionExpression.Create call instead of ctor):

public int? DatePart(string datePartArg, DateTime? date) => throw new Exception();

public void OnModelCreating(DbModelBuilder modelBuilder) {
    var methodInfo = typeof(DbContext).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) });
    modelBuilder
        .HasDbFunction(methodInfo)
        .HasTranslation(args => SqlFunctionExpression.Create(nameof(DatePart), new[]
                {
                        new SqlFragmentExpression(args.ToArray()[0].ToString()),
                        args.ToArray()[1]
                }, typeof(int?), null));
}

Query:

repository.Where(x => dbContext.DatePart("week", x.CreatedAt) > 10);

some more info: https://github.com/aspnet/EntityFrameworkCore/issues/10404

watch out not to call the DbFunction method on an interface of DbContext. The call must happend directly on the DbContext instance.

Edit: for ef core 3.1 you can use the static method SqlFunctionExpression.Create instead of the ctor:

Answer 2

For EF Core 5, it's even different as the SqlFunctionExpression.Create factory is now obsolete and the SqlFunctionExpression construction should be used instead. Here's the updated code (if adding it to the DB Context):

public partial class ApplicationDbContext : DbContext
{
    public int? DatePart(string datePartArg, DateTimeOffset? date) => throw new InvalidOperationException($"{nameof(DatePart)} cannot be called client side.");

    protected override void OnModelCreating(ModelBuilder builder)
    {
        var methodInfo = typeof(DbFunctionExtensions).GetMethod(nameof(DatePart));

        var datePartMethodInfo = typeof(ApplicationDbContext) // Your DB Context
            .GetRuntimeMethod(nameof(ApplicationDbContext.DatePart), new[] { typeof(string), typeof(DateTimeOffset) });
        builder.HasDbFunction(datePartMethodInfo)
           .HasTranslation(args =>
                    new SqlFunctionExpression("DATEPART",
                        new[]
                        {
                            new SqlFragmentExpression((args.ToArray()[0] as SqlConstantExpression).Value.ToString()),
                            args.ToArray()[1]
                        },
                        true,
                        new[] { false, false },
                        typeof(int?),
                        null
                    )
                );
    }
}

Note: You can switch to DateTime instead of DateTimeOffset as per your needs.

(The exception has nothing to do with EF Core 5, it's just more clear for anybody working on the code later on or trying to call the method client side).

Answer 3

for ef core 3.1 is little different solution, should cast argument first to SqlConstantExpression then pass its Value to SqlFragmentExpression constructor:

public static class DbFunctionExtensions
{
    public static int? DatePart(string type, DateTime? date) => throw new Exception();

    public static void ConfigureDbFunctions(this ModelBuilder modelBuilder)
    {
        var mi = typeof(DbFunctionExtensions).GetMethod(nameof(DatePart));

        modelBuilder.HasDbFunction(mi, b => b.HasTranslation(e =>
        {
            var ea = e.ToArray();
            var args = new[]
            {
                new SqlFragmentExpression((ea[0] as SqlConstantExpression).Value.ToString()),
                ea[1]
            };
            return SqlFunctionExpression.Create(nameof(DatePart), args, typeof(int?), null);
        }));
    }
}