如何在ServiceStack ORMLite中实现带CASE的MySQL统计查询？

Select
    sum(
        CASE
            WHEN e01f04 < '2024-02-01' THEN DATEDIFF(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY), '2024-02-01') + 1
            WHEN ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) > '2024-02-29' THEN DATEDIFF(e01f04, '2024-02-29') + 1
            ELSE DATEDIFF(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY), e01f04) + 1
        END
    ) AS Leave_Count
    , E01F06
FROM
    lve01
where
        e01f02 = 1
    AND
        e01f07 = 2
    AND
        (e01f04 >= '2024-02-01' AND
        e01f04 <= '2024-02-29')
    OR
        (ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) >= '2024-02-01' AND
        ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) <= '2024-02-29');

• e01f02 - 员工ID（employeeId）
• e01f04 - 休假开始日期（leave start date）
• ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) - 休假结束日期（leave last date）
• e01f05 - 休假天数（no. of leaves）
• e01f07 - 休假状态（leave status，2代表已批准）

该查询用于统计员工ID=1的2024年2月已批准休假总天数。

用户已通过ORMLite编写了如下代码，但部分逻辑使用了SQL字面量：

SqlExpression<LVE01> sqlExp = db.From<LVE01>()
    .Where(l => l.e01f02 == EmployeeId && l.e01f07 == LeaveStatus.Approved)
    .Where($"(e01f04 >= '{MonthFirstDate}' AND e01f04 <= '{MonthLastDate}')")
    .Or($"(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) >= '{MonthFirstDate}' AND ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) <= '{MonthLastDate}')");

sqlExp.SelectExpression = $"SELECT sum( CASE WHEN e01f04 < '{MonthFirstDate}' THEN DATEDIFF(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY), '{MonthFirstDate}') + 1 WHEN ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY) > '{MonthLastDate}' THEN DATEDIFF(e01f04, '{MonthLastDate}') + 1 ELSE DATEDIFF(ADDDATE(e01f04, INTERVAL (e01f05 - 1) DAY), e01f04) + 1 END) AS Leave_Count";

如何将上述查询修改为更基于ORMLite函数的实现，避免使用SQL字面量？

可以通过ORMLite提供的SqlFunc静态类封装SQL函数，完全替换字符串拼接逻辑，实现类型安全的查询构造：

// 预先定义休假结束日期的表达式，复用多次
var leaveEndDate = SqlFunc.DateAdd(
    LVE01.Fields.E01f04, 
    SqlFunc.Subtract(LVE01.Fields.E01f05, 1), 
    Sql.DateInterval.Day
);

var query = db.From<LVE01>()
    .Where(l => l.E01f02 == EmployeeId && l.E01f07 == LeaveStatus.Approved)
    .Where(q => 
        // 开始日期在目标月范围内
        (q.E01f04 >= MonthFirstDate && q.E01f04 <= MonthLastDate) ||
        // 结束日期在目标月范围内
        (leaveEndDate >= MonthFirstDate && leaveEndDate <= MonthLastDate)
    )
    .Select(() => new {
        Leave_Count = SqlFunc.Sum(SqlFunc.CaseWhen(
            // 情况1：休假开始早于目标月起始日，计算从起始日到结束日的天数
            SqlFunc.Lt(LVE01.Fields.E01f04, MonthFirstDate), 
            SqlFunc.Add(SqlFunc.DateDiff(leaveEndDate, MonthFirstDate), 1),
            // 情况2：休假结束晚于目标月结束日，计算从开始日到结束日的天数
            SqlFunc.Gt(leaveEndDate, MonthLastDate),
            SqlFunc.Add(SqlFunc.DateDiff(LVE01.Fields.E01f04, MonthLastDate), 1),
            // 默认情况：休假完全在目标月内，计算总休假天数
            SqlFunc.Add(SqlFunc.DateDiff(leaveEndDate, LVE01.Fields.E01f04), 1)
        ))
    });

关键说明 #

1. 复用表达式：将休假结束日期的计算逻辑提取为变量leaveEndDate，避免重复编写，提升可读性。
2. SqlFunc封装：
   • SqlFunc.DateAdd对应MySQL的ADDDATE函数
   • SqlFunc.DateDiff对应MySQL的DATEDIFF函数
   • SqlFunc.CaseWhen对应SQL的CASE语句
   • SqlFunc.Lt/SqlFunc.Gt对应</>比较操作
3. 类型安全：通过Lambda表达式和强类型字段引用（LVE01.Fields），避免手动拼接字符串导致的语法错误和SQL注入风险。

内容的提问来源于stack exchange，提问作者Prajval Gahine