SqlFunctions.DatePart 在 EF Core 中等效 [英] SqlFunctions.DatePart equivalent in EF Core

问题描述

EF Core 中以下语句的等效项是什么?

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

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

EF.Functions 似乎没有 DatePart 方法.

推荐答案

可以通过用 DbFunctionAttribute 包装它来使用 datepart SQL 函数.棘手的部分是告诉 ef core 不要将 datepart 类型参数作为字符串处理.示例:

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:

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(静态SqlFunctionExpression.Create调用代替ctor):

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));
}

查询:

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

更多信息:https://github.com/aspnet/EntityFrameworkCore/issues/10404

注意不要在 DbContext 的接口上调用 DbFunction 方法.调用必须直接发生在 DbContext 实例上.

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

对于ef core 3.1，您可以使用静态方法SqlFunctionExpression.Create 而不是ctor:

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

这篇关于SqlFunctions.DatePart 在 EF Core 中等效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！