How to Add Lazy Loading to Dapper

Dapper is a great way to take care of querying SQL Server (and other) databases. It offers a nice compromise between full blown ORM Frameworks such as entity framework and raw SqlDataReader based approaches. Although entity framework is great, it adds another layer of complexity to your applications. I’ve found that sometimes it can be hard to figure out how everything is glued together. Which is why I like to use the very cool dapper library, it’s a bit more transparent than the Entity Framework, giving you more control over the SQL Queries executed and allowing you to use advanced SQL syntax.

Because Dapper is purely an object mapper, it doesn’t support features like dependency tracking and lazy loading. Today, we’ll discuss how to implement lazy loading when you are using dapper (it’s quite easy really!). We’ll be using a new class of the .NET Framework 4.0 Lazy<T>.

Below this post, you can find a demo application that shows the completed project. For the demo, I’ve used the AdventureWorks database (its predecessor being the Northwind sample database) which you can download here at CodePlex.

For the purpose of this demo, I’ve modeled the following entities:

Although not visible in the above diagram, it’s important to note that the properties Person and Store are of type Lazy<Person> (ha ha, funny I know =), and Lazy<Store>. So after setting up the entities, we’ll use the Repository pattern to abstract our database queries. To keep the demo simple, I’ve created the following interface and class:

As you see, only the LoadAll() method is publicly exposed, the others are private methods and are only relevant to the repository itself.

public IList<Customer> LoadAll()
{
   using (var connection = GetConnection())
   {
       string sql = "SELECT CustomerID, 
       PersonID, StoreID, AccountNumber FROM Sales.Customer";
       return connection.Query(sql).Select<dynamic, 
       Customer>(row => {return LoadFromData(row);}).ToList();
   }
}

As you can see, we are using dapper's dynamic querying abilities, we’ve done this for two reasons:

1. Using the generic querying methods, it’s not possible to set extra properties after Dapper created the mapped instance of the entity
2. To set the lazy loading properties later on the entities we’d have to re iterate the list, which wouldn’t be very efficient.

So anyway, as you can see, we are mapping the entity manually using the Select LINQ extension method, and have implemented the mapping in a method to better abstract the functionality. The Select method takes two generic parameters firstly dynamic, which is the source of the data we are querying. Secondly, it takes the destination type as a parameter which in our case is Customer.

Now let’s have a look at the method where we are actually mapping the data, which is where things get interesting.

private Customer LoadFromData(dynamic data)
{
   var customer = new Customer();
   customer.AccountNumber = data.AccountNumber;
   /*SNIP*/
   customer.Person = new Lazy<Person>(() => LoadPerson(customer.PersonID));
   customer.Store = new Lazy<Store>(() => LoadStore(customer.StoreID));
   return customer; 
}

So as you can see, we are instantiating a new Customer object, and start assigning the dynamic data properties to it. When we want to create a our Lazy<Person>, we instantiate a new Lazy<T> object and pass a Func<T> as the first parameters (these are basically inline delegates), see the MSDN documentation here. So we declare that when you call the property Person on the Customer entity the LoadPerson method is executed, passing in the current customer's PersonId.

And that’s all! For a completed version of the project, check out the repository on github:

• https://github.com/wullinkm/dapper-lazyloading