Dapper Multi-Mapping — Joining Related Data into Object Graphs

SQL JOIN returns flat rows. A prescription with its patient is one flat row:
  prescription_id | medication | patient_id | patient_name | patient_mrn

Without multi-mapping: map everything manually — fragile, verbose.
With multi-mapping: Dapper splits the row at a specified column
  and maps each part to a different C# type.

splitOn: tells Dapper where to split the columns in the row.
  "Id" (default) → split at each column named "Id"
  "PatientId" → split when the column named "PatientId" is reached

// SQL: Prescription joined with its Prescriber (clinician)
const string sql = @"
    SELECT
        p.id            AS Id,
        p.medication    AS Medication,
        p.dose_amount   AS DoseAmount,
        p.dose_unit     AS DoseUnit,
        p.prescribed_at AS PrescribedAt,
        -- Split here: next columns map to PrescriberDto
        c.id            AS Id,
        c.full_name     AS FullName,
        c.specialty     AS Specialty
    FROM prescriptions p
    INNER JOIN clinicians c ON c.id = p.prescriber_id
    WHERE p.patient_id = @PatientId AND p.is_active = 1";

using var connection = _connectionFactory.CreateConnection();

var prescriptions = await connection.QueryAsync<PrescriptionDto, PrescriberDto, PrescriptionDto>(
    sql,
    (prescription, prescriber) =>
    {
        prescription.Prescriber = prescriber;
        return prescription;
    },
    new { PatientId = patientId },
    splitOn: "Id");  // split at each column named "Id"

// PrescriberDto maps: Id, FullName, Specialty
// PrescriptionDto maps: Id, Medication, DoseAmount, DoseUnit, PrescribedAt

// SQL JOIN with one-to-many returns duplicate parent rows
// Must group in C# — Dapper cannot do this automatically

const string sql = @"
    SELECT
        pat.id          AS PatientId,
        pat.mrn         AS Mrn,
        pat.first_name  AS FirstName,
        -- Split here: next columns are prescriptions
        pr.id           AS PrescriptionId,
        pr.medication   AS Medication,
        pr.is_active    AS IsActive
    FROM patients pat
    LEFT JOIN prescriptions pr ON pr.patient_id = pat.id AND pr.is_deleted = 0
    WHERE pat.ward_id = @WardId AND pat.is_deleted = 0
    ORDER BY pat.id";

using var connection = _connectionFactory.CreateConnection();

var patientDict = new Dictionary<Guid, PatientWithPrescriptionsDto>();

await connection.QueryAsync<PatientWithPrescriptionsDto, PrescriptionSummaryDto, PatientWithPrescriptionsDto>(
    sql,
    (patient, prescription) =>
    {
        if (!patientDict.TryGetValue(patient.PatientId, out var existing))
        {
            existing = patient;
            existing.Prescriptions = new List<PrescriptionSummaryDto>();
            patientDict.Add(patient.PatientId, existing);
        }

        if (prescription.PrescriptionId != Guid.Empty)
            existing.Prescriptions.Add(prescription);

        return existing;
    },
    new { WardId = wardId },
    splitOn: "PrescriptionId");

var result = patientDict.Values.ToList();

// Patient → Prescription → Medication (three types)
const string sql = @"
    SELECT
        pat.id        AS PatientId,
        pat.mrn       AS Mrn,
        pr.id         AS PrescriptionId,
        pr.dose       AS Dose,
        m.id          AS MedicationId,
        m.name        AS MedicationName,
        m.drug_class  AS DrugClass
    FROM patients pat
    INNER JOIN prescriptions pr ON pr.patient_id = pat.id
    INNER JOIN medications m   ON m.id = pr.medication_id
    WHERE pat.id = @PatientId";

var patientDict = new Dictionary<Guid, PatientDetailDto>();

await connection.QueryAsync<PatientDetailDto, PrescriptionDto, MedicationDto, PatientDetailDto>(
    sql,
    (patient, prescription, medication) =>
    {
        if (!patientDict.TryGetValue(patient.PatientId, out var existing))
        {
            existing = patient;
            patientDict.Add(patient.PatientId, existing);
        }

        if (prescription != null)
        {
            prescription.Medication = medication;
            existing.Prescriptions.Add(prescription);
        }
        return existing;
    },
    new { PatientId = patientId },
    splitOn: "PrescriptionId,MedicationId");  // comma-separated splitOn for 3+ types

// splitOn must match a column in the result set.
// Use column aliases to make it unambiguous.

// BAD: both prescription and medication have "id" — ambiguous split
SELECT pr.id, pr.medication, m.id, m.name ...

// GOOD: distinct aliases for each Id column
SELECT pr.id AS PrescriptionId, pr.medication,
       m.id  AS MedicationId,   m.name ...
// splitOn: "PrescriptionId,MedicationId"

Multi-mapping (JOIN approach):
  ✓ One-to-one relationships (prescription + prescriber)
  ✓ Small one-to-many (patient with under 10 prescriptions)
  ✓ When minimizing round trips is critical

Separate queries (two SELECT approach):
  ✓ One-to-many with large child collections
  ✓ Optional relationships (LEFT JOIN with many NULLs wastes bandwidth)
  ✓ When the JOIN creates a Cartesian product (multiple collections)

Multi-result sets (Dapper QueryMultiple — see next lesson):
  ✓ When you need separate queries in one database round trip
  ✓ Parent + multiple child collections in one network call