Dapper Multi-Result Sets — QueryMultiple for Batch Queries

Multiple separate queries:
  Request 1: SELECT patient details      → 1 round trip
  Request 2: SELECT active prescriptions → 1 round trip
  Request 3: SELECT recent lab results   → 1 round trip
  Total: 3 network round trips to the database

QueryMultiple (one request with 3 SELECT statements):
  One round trip to the database
  SQL Server executes all 3 statements
  Results returned as 3 separate result sets in the same response
  Total: 1 round trip — 3x fewer network calls

For dashboards with 5-10 independent data sets, this matters.

public async Task<PatientDetailPageDto> GetPatientDetailAsync(
    Guid patientId, CancellationToken ct)
{
    const string sql = @"
        SELECT id AS Id, mrn AS Mrn, first_name AS FirstName, last_name AS LastName,
               date_of_birth AS DateOfBirth, ward_id AS WardId
        FROM patients WHERE id = @PatientId AND is_deleted = 0;

        SELECT id AS Id, medication AS Medication, dose_amount AS DoseAmount,
               dose_unit AS DoseUnit, prescribed_at AS PrescribedAt
        FROM prescriptions
        WHERE patient_id = @PatientId AND is_active = 1 AND is_deleted = 0
        ORDER BY prescribed_at DESC;

        SELECT id AS Id, result_type AS ResultType, result_value AS ResultValue,
               unit AS Unit, recorded_at AS RecordedAt
        FROM lab_results
        WHERE patient_id = @PatientId AND is_deleted = 0
        ORDER BY recorded_at DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;";

    using var connection = _connectionFactory.CreateConnection();
    using var multi = await connection.QueryMultipleAsync(
        sql,
        new { PatientId = patientId });

    var patient       = await multi.ReadFirstOrDefaultAsync<PatientDto>();
    var prescriptions = (await multi.ReadAsync<PrescriptionSummaryDto>()).AsList();
    var labResults    = (await multi.ReadAsync<LabResultDto>()).AsList();

    return new PatientDetailPageDto(patient, prescriptions, labResults);
}

// Result sets must be read in the order they appear in the SQL.
// Reading out of order causes an InvalidOperationException or wrong data.

using var multi = await connection.QueryMultipleAsync(sql, parameters);

// Read in exactly the same order as the SELECT statements appear:
var first  = await multi.ReadAsync<TypeA>();   // 1st SELECT
var second = await multi.ReadAsync<TypeB>();   // 2nd SELECT
var third  = await multi.ReadFirstOrDefaultAsync<TypeC>(); // 3rd SELECT

// Cannot read them in a different order.
// Cannot skip a result set without reading it.
// ReadAsync consumes one result set.

// Ward dashboard: multiple aggregates in one query
public async Task<WardDashboardDto> GetWardDashboardAsync(
    Guid wardId, CancellationToken ct)
{
    const string sql = @"
        -- Total patients
        SELECT COUNT(*) FROM patients
        WHERE ward_id = @WardId AND is_deleted = 0;

        -- High-priority alerts
        SELECT id AS Id, patient_id AS PatientId, message AS Message,
               severity AS Severity, created_at AS CreatedAt
        FROM alerts
        WHERE ward_id = @WardId AND is_resolved = 0
        ORDER BY severity DESC, created_at DESC;

        -- INR results out of range
        SELECT p.mrn AS Mrn, p.first_name AS FirstName, p.last_name AS LastName,
               lr.result_value AS InrValue, lr.recorded_at AS RecordedAt
        FROM lab_results lr
        INNER JOIN patients p ON p.id = lr.patient_id
        WHERE lr.ward_id = @WardId
          AND lr.result_type = 'INR'
          AND (CAST(lr.result_value AS FLOAT) < 2.0 OR CAST(lr.result_value AS FLOAT) > 3.0)
          AND lr.recorded_at > DATEADD(HOUR, -24, GETUTCDATE())
        ORDER BY lr.recorded_at DESC;

        -- Prescriptions expiring in 24 hours
        SELECT id AS Id, medication AS Medication, expiry_date AS ExpiryDate, patient_id AS PatientId
        FROM prescriptions
        WHERE ward_id = @WardId
          AND is_active = 1
          AND expiry_date BETWEEN GETUTCDATE() AND DATEADD(HOUR, 24, GETUTCDATE())
        ORDER BY expiry_date;";

    using var connection = _connectionFactory.CreateConnection();
    using var multi = await connection.QueryMultipleAsync(
        sql, new { WardId = wardId });

    var patientCount        = await multi.ReadFirstAsync<int>();
    var activeAlerts        = (await multi.ReadAsync<AlertDto>()).AsList();
    var outOfRangeInrValues = (await multi.ReadAsync<InrAlertDto>()).AsList();
    var expiringScripts     = (await multi.ReadAsync<ExpiringPrescriptionDto>()).AsList();

    return new WardDashboardDto(patientCount, activeAlerts, outOfRangeInrValues, expiringScripts);
}

// Stored procedure that returns multiple result sets
const string sql = "EXEC sp_GetPatientFullHistory @PatientId, @FromDate, @ToDate";

using var connection = _connectionFactory.CreateConnection();
using var multi = await connection.QueryMultipleAsync(
    sql,
    new { PatientId = patientId, FromDate = from, ToDate = to },
    commandType: CommandType.StoredProcedure);

var admissions    = (await multi.ReadAsync<AdmissionDto>()).AsList();
var prescriptions = (await multi.ReadAsync<PrescriptionDto>()).AsList();
var procedures    = (await multi.ReadAsync<ProcedureDto>()).AsList();
var discharges    = (await multi.ReadAsync<DischargeDto>()).AsList();

// Load patients with their prescriptions using two SELECT statements
// Avoids the Cartesian product of a JOIN while keeping one round trip

const string sql = @"
    SELECT id AS Id, mrn AS Mrn, first_name AS FirstName FROM patients
    WHERE ward_id = @WardId AND is_deleted = 0;

    SELECT id AS Id, patient_id AS PatientId, medication AS Medication
    FROM prescriptions
    WHERE patient_id IN (
        SELECT id FROM patients WHERE ward_id = @WardId AND is_deleted = 0)
    AND is_active = 1;";

using var multi = await connection.QueryMultipleAsync(sql, new { WardId = wardId });

var patients      = (await multi.ReadAsync<PatientDto>()).ToList();
var prescriptions = (await multi.ReadAsync<PrescriptionDto>()).ToList();

// Correlate in memory — clean and efficient
var prescriptionsByPatient = prescriptions
    .GroupBy(p => p.PatientId)
    .ToDictionary(g => g.Key, g => g.ToList());

foreach (var patient in patients)
    patient.Prescriptions = prescriptionsByPatient.GetValueOrDefault(patient.Id, []);