使用UNION ALL时Dapper QueryAsync未返回部分SQL行问题

核心问题定位 #

从你的代码和描述来看，缺失Deleted_Appointments行的原因大概率集中在SQL拼接错误、参数化失效或Dapper实体映射不兼容这三个方面，以下是具体排查和修复步骤：

1. 修复SQL参数化与过滤条件范围 #

你的代码存在两个关键SQL问题：

• 硬编码日期未使用传入的startDate/endDate参数，导致.NET执行的SQL和SSMS中手动执行的逻辑不一致
• 过滤条件（IsMobile/ClientIds/Status）仅作用于第一个SELECT，未同步应用到第二个SELECT

修复后的SQL拼接代码

public async Task<List<AppointmentsResponse>> GetAppointments(
    DateTime startDate,
    DateTime endDate,
    List<long> ClientIds,
    int Status,
    bool IsMobile
)
{
    var sql = new StringBuilder();

    // 第一个SELECT：正常预约数据
    sql.AppendLine(@"SELECT
        A.Appointment_Id,
        A.Appointment_Date,
        C.Client_Id,
        C.Name AS Client_Name,
        S.Service_Id,
        S.Description AS Service_Name,
        CO.Name AS Company_Name,
        CO.Address AS Company_Address,
        '' AS Deleted_By
    FROM Appointments A (NOLOCK)
    LEFT JOIN Appointment_Services ApS (NOLOCK) ON ApS.Appointment_Id = A.Appointment_Id
    LEFT JOIN Services S (NOLOCK) ON S.Service_Id = ApS.Service_Id
    LEFT JOIN Clients C (NOLOCK) ON C.Client_Id = A.Client_Id
    LEFT JOIN Companies CO (NOLOCK) ON CO.Company_Id = A.Company_Id
    LEFT JOIN Users U (NOLOCK) ON U.Id = A.User_Id
    WHERE A.Appointment_Date BETWEEN @startDate AND @endDate");

    // 给第一个SELECT添加过滤条件
    if (IsMobile)
        sql.AppendLine("AND A.IsMobile = 1");
    if (ClientIds.Count > 0)
        sql.AppendLine("AND A.Client_Id IN @ClientIds");
    if (Status > 0)
        sql.AppendLine("AND A.Appointment_Status = @Status");

    // UNION ALL 分隔符
    sql.AppendLine(@"
    UNION ALL

    SELECT
        DA.Appointment_Id,
        DA.Appointment_Date,
        C.Client_Id,
        C.Name AS Client_Name,
        S.Service_Id,
        S.Description AS Service_Name,
        CO.Name AS Company_Name,
        CO.Address AS Company_Address,
        U.Name AS Deleted_By
    FROM Deleted_Appointments DA (NOLOCK)
    LEFT JOIN Deleted_Appointment_Services DAS (NOLOCK) ON DAS.Appointment_Id = DA.Appointment_Id
    LEFT JOIN Services S (NOLOCK) ON S.Service_Id = DAS.Service_Id
    LEFT JOIN Clients C (NOLOCK) ON C.Client_Id = DA.Client_Id
    LEFT JOIN Companies CO (NOLOCK) ON CO.Company_Id = DA.Company_Id
    LEFT JOIN Users U (NOLOCK) ON U.Id = DA.User_Id
    WHERE DA.Appointment_Date BETWEEN @startDate AND @endDate");

    // 给第二个SELECT添加对应过滤条件（注意表别名改为DA）
    if (IsMobile)
        sql.AppendLine("AND DA.IsMobile = 1"); // 假设Deleted_Appointments表有IsMobile字段
    if (ClientIds.Count > 0)
        sql.AppendLine("AND DA.Client_Id IN @ClientIds");
    if (Status > 0)
        sql.AppendLine("AND DA.Appointment_Status = @Status"); // 假设Deleted_Appointments表有Appointment_Status字段

    using var con = connectionFactory.CreateConnection();
    // Dapper支持直接传入List作为IN参数，避免SQL注入
    var result = await con.QueryAsync<AppointmentsResponse>(sql.ToString(), new { startDate, endDate, ClientIds, Status });

    return result.ToList();
}

2. 检查Dapper实体映射兼容性 #

如果SQL修复后仍缺失行，需确认AppointmentsResponse类的属性是否适配SQL返回的字段（尤其是NULL值场景）：

public class AppointmentsResponse
{
    public long Appointment_Id { get; set; }
    public DateTime Appointment_Date { get; set; }
    public long? Client_Id { get; set; } // LEFT JOIN可能返回NULL，需设为可空类型
    public string? Client_Name { get; set; }
    public long? Service_Id { get; set; } // LEFT JOIN可能返回NULL，需设为可空类型
    public string? Service_Name { get; set; }
    public string? Company_Name { get; set; }
    public string? Company_Address { get; set; }
    public string? Deleted_By { get; set; } // 第二个SELECT可能返回NULL，需设为可空类型
}

• 若类中属性为不可空类型（如long而非long?），当SQL返回NULL时，Dapper会无法映射并跳过该行

3. 调试验证步骤 #

1. 打印拼接后的完整SQL：在QueryAsync前添加Console.WriteLine(sql.ToString())，复制到SSMS执行，对比返回结果与.NET接口返回结果是否一致
2. 检查参数值：确认传入的startDate/endDate/ClientIds等参数与SSMS中手动测试的参数完全相同
3. 临时移除NOLOCK：虽然原VB也使用了NOLOCK，但可临时移除该提示，排除脏读导致的行缺失问题

内容的提问来源于stack exchange，提问作者Lucas Rafael