Dapper.NET Simple multi-table mapping


Let's say we have a query of the remaining horsemen that needs to populate a Person class.

Daniel Dennett1942United States of America
Sam Harris1967United States of America
Richard Dawkins1941United Kingdom
public class Person
    public string Name { get; set; }
    public int Born { get; set; }
    public Country Residience { get; set; }

public class Country
    public string Residence { get; set; }

We can populate the person class as well as the Residence property with an instance of Country using an overload Query<> that takes a Func<> that can be used to compose the returned instance. The Func<> can take up to 7 input types with the final generic argument always being the return type.

var sql = @"SELECT 'Daniel Dennett' AS Name, 1942 AS Born, 'United States of America' AS Residence
UNION ALL SELECT 'Sam Harris' AS Name, 1967 AS Born, 'United States of America' AS Residence
UNION ALL SELECT 'Richard Dawkins' AS Name, 1941 AS Born, 'United Kingdom' AS Residence";

var result = connection.Query<Person, Country, Person>(sql, (person, country) => {
        if(country == null)
            country = new Country { Residence = "" };
        person.Residience = country;
        return person;
    splitOn: "Residence");

Note the use of the splitOn: "Residence" argument which is the 1st column of the next class type to be populated (in this case Country). Dapper will automatically look for a column called Id to split on but if it does not find one and splitOn is not provided a System.ArgumentException will be thrown with a helpful message. So although it is optional you will usually have to supply a splitOn value.