Querying into a complex object with Dapper

Question

I have a Customer class with the following properties:

public int Id { get; set; }
public string Name { get; set; }
public int AddressId { get; set; }
public Address Address { get; set; }

My goal is to write a Dapper query that will use an Inner Join to populate the entire Address property within each Customer that is returned.

Here is what I have and it is working but I am wondering if this is the cleanest/simplest way to do it:

StringBuilder sql = new StringBuilder();
using (var conn = GetOpenConnection())
{
    sql.AppendLine("SELECT c.Id, c.Name, c.AddressId, a.Address1, a.Address2, a.City, a.State, a.ZipCode ");
    sql.AppendLine("FROM Customer c ");
    sql.AppendLine("INNER JOIN Address a ON c.AddressId = a.Id ");

    return conn.Query<Customer, Address, Customer>(
        sql.ToString(),
        (customer, address) => {
            customer.Address= address;
            return userRole;
        },
        splitOn: "AddressId"
    ).ToList();
}

I have some concern about adding another property such as:

public Contact Contact { get; set; }

I am not sure how I would switch the syntax above to populate both Address and Contact.

Satellite

The trick would be to make the split columns have the same name (like Id) and then it would just be Query<Customer, Address, Contact, Customer>. – Surprint

Answer 1

I have coded using Dapper version 1.40 and I have written queries like the way below, I haven't got any issues to populate mote more than one object, but I have faced a limit of 8 different classes those I can map in a query.

public class Customer {
    public int Id { get; set; }
    public string Name { get; set; }
    public int AddressId { get; set; }  
    public int ContactId { get; set; }
    public Address Address { get; set; }
    public Contact Contact { get; set; }
}

public class Address {
    public int Id { get; set; }
    public string Address1 {get;set;}
    public string Address2 {get;set;}
    public string City {get;set;}
    public string State {get;set;}
    public int ZipCode {get;set;}
    public IEnumerable<Customer> Customer {get;set;}
}

public class Contact {
    public int Id { get; set; }
    public string Name { get; set; }
    public IEnumerable<Customer> Customer {get;set;}
}

using (var conn = GetOpenConnection())
{
    var query = _contextDapper
        .Query<Customer, Address, Contact, Customer>($@"
            SELECT c.Id, c.Name, 
                c.AddressId, a.Id, a.Address1, a.Address2, a.City, a.State, a.ZipCode,
                c.ContactId, ct.Id, ct.Name
            FROM Customer c
            INNER JOIN Address a ON a.Id = c.AddressId
            INNER JOIN Contact ct ON ct.Id = c.ContactId", 
            (c, a, ct) =>
            {
                c.LogType = a;
                c.Contact = ct;
                return c; 
            }, splitOn: "AddressId, ContactId")
        .AsQueryable();

    return query.ToList();          
}

Manville

helped me a lot this :P Thanks! :) I Also used my example here – Penicillate

I guess where the code said "c.LogType = a;" should be "c.Address = a;" – Adamik

Answer 2

Take a look in my example with a big query, note that Each Query line It's a different object.

public List<Appointment> GetList(int id)
{
    List<Appointment> ret;
    using (var db = new SqlConnection(connstring))
    {
        const string sql = @"SELECT AP.[Id], AP.Diagnostics, AP.Sintomns, AP.Prescription, AP.DoctorReport, AP.AddressId,
        AD.Id, AD.Street, AD.City, AD.State, AD.Country, AD.ZIP, Ad.Complement,
        D.Id, D.Bio, d.CRMNumber, D.CRMNumber, D.CRMState,
        P.Id,
        S.Id, S.Name,
        MR.Id, MR.Alergies, MR.BloodType, MR.DtRegister, Mr.HealthyProblems, MR.HealthyProblems, MR.Height, MR.MedicalInsuranceNumber, MR.MedicalInsuranceUserName, MR.Medications, MR.Weight,
        MI.Id, MI.Name
        from Appointment AP
        inner join [Address] AD on AD.Id = AP.AddressId
        inner join Doctor D on D.Id = AP.DoctorId
        inner join Patient P on P.Id = AP.PatientId
        left join Speciality S on S.Id = D.IDEspeciality
        left join MedicalRecord MR on MR.Id = P.MedicalRecordId
        left join MedicalInsurance MI on MI.Id = MR.MedicalInsuranceId
        where AP.Id = @Id
        order by AP.Id desc";

        ret = db.Query<Appointment, Address, Doctor, Patient, Speciality, MedicalRecord, MedicalInsurance, Appointment>(sql,
            (appointment, address, doctor, patient, speciality, medicalrecord, medicalinsurance) =>
            {
                appointment.Address = address;
                appointment.Doctor = doctor;
                appointment.Patient = patient;
                appointment.Doctor.Speciality = speciality;
                appointment.Patient.MedicalRecord = medicalrecord;
                appointment.Patient.MedicalRecord.MedicalInsurance = medicalinsurance;
                return appointment;
            }, new { Id = id }, splitOn: "Id, Id, Id, Id, Id, Id").ToList();
    }
    return ret;
}

Penicillate