Creating Complex Objects with Dapper

March 23rd, 2016

Intro

Dapper for .NET is a great tool if you want something between pure ADO.NET and a large ORM like Entity Framework or NHibernate. If you still need the control that hand-writing your SQL queries provides, but want a cleaner way of populating your objects with the records then I suggest you check out a micro-ORM like Dapper.

Dapper is very straight-forward when you want to fill up an object or list of objects where the class is composed of simple types (int, string, etc.), but becomes a bit more complex when classes are composed of object hierarchies (i.e. a Person class has an Address class property). So in this post I'm going to discus the basics of how you could approach this problem.

Setup

Lets say your data looked like this:

People Table

Addresses Table

And our class definitions look like this:

Person Class

            public class Person {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Address Address{ get; set; }
}
            
        

Address Class

public class Address {
    public int Id { get; set; }
    public string StreetNumber { get; set; }
    public string StreetName { get; set; }
    public string City { get; set; }
    public string State { get; set; }
}

Basic Queries

We could create a List of type Person using the Dapper Query extension method. That would look like this:

db.Query<Person>("SELECT * FROM People").ToList();

That code assumes you have an object called db that is an instance of something implementing IDbConnection (e.g. SqlConnection in the System.Data.SqlClient namespace.) Similarily if we wanted to create a List of type Address we could write this:

db.Query<Address>("SELECT * FROM ADDRESSES").ToList();

Complex Queries

The queries above show just how simple it can be to use Dapper on flat objects. However, what if you wanted to have the Address property on a Person instance be populated with data as a result of a JOIN. It's possible to fill up a List of Persons and a List of Addresses and then find matches using LINQ, but why not let do it all in a single query and let Dapper handle the details.

So lets say our query was this:

SELECT
P.Id, P.FirstName, P.LastName,
A.Id AS AddressId, A.StreetNumber, A.StreetName, A.City, A.State
FROM People P INNER JOIN Addresses A ON A.AddressId = P.AddressId;

We would expect our results table to look like this:

We could create a List of type Person with the Address property filled with the results like this:

var sql = "SELECT P.Id, P.FirstName, P.LastName, " +
"A.Id AS AddressId, A.StreetNumber, A.StreetName, A.City, A.State " +
"FROM People P INNER JOIN Addresses A ON A.AddressId = P.AddressId; ";

db.Query<Person, Address, Person>(sql,
    (person, address) => {
    person.Address = address;
    return person;
    },
    splitOn: "AddressId"
).ToList();

Three important things to point out in the code above:

1) The Query generic parameter is not taking a single type but rather a Func<>. So a Func<Person, Address, Person> will take a Person and an Address object as parameters and return a object of type Person.

2) The magic is happening down in the lambda where we just have to tell the new address object (created by Dapper) where to go. In this case its a property on the newly instantiated person object (also created by Dapper).

3) In order to make this happen we have to use the "splitOn" parameter in the Query function. splitOn tells Dapper which column in the records represents the start of the second object. Since AddressId is the first column that is part of the Address object and not the Person object, thats where we want to "split on".

Conclusion

Dapper makes it incredibly easy to go from the relational world to the object-oriented world while still maintaining the power and flexibility that hand-rolling your queries affords.

Creating object hierarchies from simple queries is only a little more difficult than creating a single object. You only need to provide Dapper some clues about where things go and let it handle the rest.