如何在EF Core中为PostgreSQL实现DateDiff方法的自定义翻译？

步骤1：配置DbFunction映射 #

在你的DbContext的OnModelCreating方法中，注册自定义的DateDiff方法并编写SQL翻译逻辑：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 获取自定义DateDiff方法的反射信息
    var dateDiffMethod = typeof(AppPgsqlSqlFunctions)
        .GetMethod(nameof(AppPgsqlSqlFunctions.DateDiff), 
            new[] { typeof(string), typeof(DateTime), typeof(DateTime) })!;

    modelBuilder.HasDbFunction(dateDiffMethod)
        .HasTranslation(parameters =>
        {
            // 拆解传入的三个参数：日期部分、起始日期、结束日期
            var datePartParam = parameters.ElementAt(0);
            var fromDateParam = parameters.ElementAt(1);
            var toDateParam = parameters.ElementAt(2);

            // 根据不同datePart生成对应PostgreSQL表达式
            return new CaseExpression(
                new List<CaseWhenClause>
                {
                    // 计算天数差：直接用结束日期减起始日期
                    new CaseWhenClause(
                        new SqlBinaryExpression(
                            ExpressionType.Equal,
                            datePartParam,
                            new SqlConstantExpression("day", typeof(string)),
                            typeof(bool)),
                        new SqlBinaryExpression(
                            ExpressionType.Subtract,
                            toDateParam,
                            fromDateParam,
                            typeof(int))),
                    // 计算年份差：用AGE函数获取时间间隔后提取年份
                    new CaseWhenClause(
                        new SqlBinaryExpression(
                            ExpressionType.Equal,
                            datePartParam,
                            new SqlConstantExpression("year", typeof(string)),
                            typeof(bool)),
                        new SqlFunctionExpression(
                            "DATE_PART",
                            new[] 
                            { 
                                new SqlConstantExpression("year", typeof(string)), 
                                new SqlFunctionExpression("AGE", new[] { toDateParam, fromDateParam }, typeof(TimeSpan)) 
                            },
                            typeof(double))),
                    // 计算月份差：同理提取月份部分
                    new CaseWhenClause(
                        new SqlBinaryExpression(
                            ExpressionType.Equal,
                            datePartParam,
                            new SqlConstantExpression("month", typeof(string)),
                            typeof(bool)),
                        new SqlFunctionExpression(
                            "DATE_PART",
                            new[] 
                            { 
                                new SqlConstantExpression("month", typeof(string)), 
                                new SqlFunctionExpression("AGE", new[] { toDateParam, fromDateParam }, typeof(TimeSpan)) 
                            },
                            typeof(double))),
                    // 计算小时差：通过时间差提取小时数
                    new CaseWhenClause(
                        new SqlBinaryExpression(
                            ExpressionType.Equal,
                            datePartParam,
                            new SqlConstantExpression("hour", typeof(string)),
                            typeof(bool)),
                        new SqlFunctionExpression(
                            "DATE_PART",
                            new[] 
                            { 
                                new SqlConstantExpression("hour", typeof(string)), 
                                new SqlBinaryExpression(ExpressionType.Subtract, toDateParam, fromDateParam, typeof(TimeSpan)) 
                            },
                            typeof(double)))
                },
                // 默认返回null（可根据需求改为抛出异常或其他处理）
                new SqlUnaryExpression(ExpressionType.Convert, new SqlConstantExpression(null, typeof(object)), typeof(int?)));
        })
        // 显式指定参数名称，确保映射准确
        .HasParameter("datePart")
        .HasParameter("from")
        .HasParameter("to");
}

步骤2：在LINQ查询中使用 #

直接调用静态方法即可，EF Core会自动将其翻译为对应的PostgreSQL SQL：

// 示例：查询实体的创建时间到现在的天数差
var dayDiffQuery = dbContext.YourEntities
    .Select(e => AppPgsqlSqlFunctions.DateDiff("day", e.CreatedDate, DateTime.UtcNow))
    .ToList();

对应的生成SQL类似：

SELECT ("e"."CreatedDate" - CURRENT_TIMESTAMP)
FROM "YourEntities" AS "e"

注意事项 #

• datePart参数需为常量：EF Core需要在查询翻译阶段确定datePart的值，因此建议使用字符串字面量传入，避免使用变量。
• 扩展支持更多日期部分：可以在CaseExpression中添加更多CaseWhenClause，比如处理minute、second等时间单位，对应PostgreSQL的DATE_PART提取逻辑。
• 处理可空日期：如果需要支持DateTime?类型参数，修改自定义方法的参数类型，并在翻译逻辑中添加NULL判断（比如用SqlCoalesceExpression处理）。

内容的提问来源于stack exchange，提问作者Nick Holden