I’m converting a Visual Basic API to .NET 8 using the same SQL query with the same parameters, and I noticed that there’s one specific row that’s not being displayed in the response object.

The query uses UNION ALL and the row only exists in the second SELECT statement:

SELECT
    A.Appointment_Id,
    A.Appointment_Date,
    -- other columns
FROM Appointments A (NOLOCK)
LEFT JOIN Some_Table ST (NOLOCK) ST.Id = A.Appointment_Id
-- more tables using LEFT JOIN
WHERE A.Appointment_Date BETWEEN '07/01/2024 00:00:00' AND '07/31/2024 23:59:59'

UNION ALL

SELECT
    DA.Appointment_Id,
    DA.Appointment_Date,
    -- other columns
FROM Deleted_Appointments DA (NOLOCK)
LEFT JOIN Some_Table ST (NOLOCK) ST.Id = DA.Appointment_Id
-- more tables using LEFT JOIN
WHERE DA.Appointment_Date BETWEEN '07/01/2024 00:00:00' AND '07/31/2024 23:59:59'

The C# code using Dapper’s QueryAsync to retrieve the data:

public async Task<List<AppointmentsResponse>> GetAppointments(/* parameters */)
{
    // my SQL query

    using var con = connectionFactory.CreateConnection();
    var result = await con.QueryAsync<AppointmentsResponse>(sql.ToString());

    return result.ToList();
}

I’ve tried using foreach to loop through each row and check if they were being returned correctly, and the missing row gets skipped entirely. I’ve also tried using QueryMultipleAsync but the result remained the same.

I’ve compared the same query with the one from the Visual Basic API and they both display it as it should when executed in Microsoft’s SSMS.

The VB implementation also returns it correctly.