Dapper Repository Tutorial
on
A few months ago I wrote a post titled: Creating a Data Repository using Dapper. It was a post that explored how one might go about creating a data repository using Dapper .Net. Dapper is a micro ORM designed by the folks at Stackoverflow.com to offer highly performant data access with some simple object mapping. I've improved upon that implementation and this post details the changes.
As the post and accompanying code evolved I found myself retro-fitting a Linq to SQL style interface but with the highly performant Dapper sitting underneath. Sounds plausible, right?
For example, I had a generic ubiquitous "find by id" method to keep my repository code DRY:
public virtual T FindByID(Guid id)
{
T item = default(T);
using (IDbConnection cn = Connection)
{
cn.Open();
item = cn.Query<T>("SELECT * FROM " + _tableName + " WHERE ID=@ID", new { ID = id }).SingleOrDefault();
}
return item;
}
Usage of the above would result in an API that looks something like:
var user = _usersRepository.FindByID("4acb5c09-03e0-43d4-85ce-2a8f5723be4b");
Next, I put together a method that would take a Linq query like this:
var users = _usersRepository.Find(u => u.Username == "Rick Astley");
And make SQL like this:
SELECT * FROM Users WHERE Username = 'Rick Astley'
Here is the method:
public virtual IEnumerable<T> Find(Expression<Func<T, bool>> predicate)
{
IEnumerable<T> items = null;
// extract the dynamic sql query and parameters from predicate
QueryResult result = DynamicQuery.GetDynamicQuery(_tableName, predicate);
using (IDbConnection cn = Connection)
{
cn.Open();
items = cn.Query<T>(result.Sql, (object)result.Param);
}
return items;
}
The code in DynamicQuery.GetDynamicQuery can be found here. It worked perfectly well for the simple scenario I had coded it for, which was to only work for simple queries with no table JOINs as I felt that the JOIN statements are where I would want total control over the SQL.
In fact, after a couple of iterations I changed the API for dynamic queries which removed the Linq to sql parsing altogether and worked via simple parameter passing as in the following code snippet (but it was too late as the first post and accompanying code had been release into the wild):
var user = _userRepository.Find("Username=@Username", new { Username = "Rick Astley" });
You can find the code for this Find method here, by the way.
The Problem
In retrospect the content of the first post alluded to the fact that there was a fully functional Linq to Sql parser rather than a simple implementation that can only handle basic queries. I've had several comments on the blog post itself, emails and tweets requesting features akin to a fully functional ORM such as transaction handling, Linq to Sql parsing and so on.
I made the following mistakes with that post:
- I didn't clearly specify what the DynamicQuery.GetDynamicQuery method would/wouldn't do
- I broke a rule regarding my blog in that I generally try to discuss pragmatic topics
- Worst of all, I tried to reinvent the wheel
So assuming you haven't turned around and deserted me by this stage, let's look at a Dapper ORM implementation redux.
The Solution
Before we do anything, let's remind ourselves of what Dapper was designed for: to read data really, really fast with simple object mapping. So what about the other features we need from an ORM such as object creation, transaction management, unit of work etc? I'm a firm believer of the 80/20 rule and therefore I'm going to apply this principle and focus on the 20% of the code that really requires Dapper to make a noticeable performance improvement worthy of extra development effort.
This iteration is going to be different in that:
- We will have access to full ORM features such as unit of work, transactions, Linq to SQL parsing (provided by Entity Framework).
- We will only use Dapper for the purpose of its creation and furthermore only when we need it.
Starting from the outside in, this is how a service might look using the new repository:
public class UsersService
{
private readonly IUnitOfWork _unitOfWork;
public UsersService(IUnitOfWork unitOfWork)
{
_unitOfWork = unitOfWork;
}
public User GetUser(int id)
{
return _unitOfWork.UserRepository.FindById(id);
}
public IEnumerable<User> GetUsers()
{
return _unitOfWork.UserRepository.GetAll();
}
public IEnumerable<User> GetNewUsers()
{
return _unitOfWork.UserRepository.Find(u => u.CreatedDate == DateTime.Now);
}
public User CreateUser(User user)
{
_unitOfWork.UserRepository.Add(user);
_unitOfWork.Save();
return user;
}
}
The implementation of the Unit of Work interface is created using code first entity framework. You can view the full code example here but to highlight the main points, the UOW implementation looks as follows:
public class UnitOfWork : DbContext, IUnitOfWork
{
private readonly Repository<User> _useRepository;
public DbSet<User> Users { get; set; }
public UnitOfWork()
{
_useRepository = new Repository<User>(Users);
}
public IRepository<User> UserRepository
{
get { return _useRepository; }
}
public void Save()
{
SaveChanges();
}
}
At this stage I have a fully functioning ORM that caters for all of the feature requests I received from the first post. Assuming that I have no performance issues, my work is done.
But now let's assume I have a performance bottle neck with the GetUser, GetUsers and GetNewUsers methods. How do I solve this with Dapper in an elegant way?
I start by introducing a new "readonly interface" to the service class that allows me to optionally slot in a Dapper implementation where I need it. Here's a tweaked version of the same UsersService:
public class UsersService
{
private readonly IUnitOfWork _unitOfWork;
/// <summary>
/// Read only user repository that makes use of dapper
/// </summary>
private readonly IReadOnlyRepository<User> _userRepository;
public UsersService(IUnitOfWork unitOfWork, IReadOnlyRepository<User> userRepository)
{
_unitOfWork = unitOfWork;
_userRepository = userRepository;
}
public User GetUser(int id)
{
return _userRepository.FindById(id);
}
public IEnumerable<User> GetUsers()
{
return _userRepository.GetAll();
}
public IEnumerable<User> GetNewUsers()
{
return _userRepository.GetNewUsers();
}
public User CreateUser(User user)
{
_unitOfWork.UserRepository.Add(user);
_unitOfWork.Save();
return user;
}
}
The read only repository looks like this:
public interface IReadOnlyRepository<T> where T : class, IEntity
{
IDbConnection Connection { get; }
T FindById(int id);
IEnumerable<T> GetAll();
}
And the implementation:
public class UserRepository : IReadOnlyRepository<User>
{
public IDbConnection Connection
{
get
{
return new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
}
}
public User FindById(int id)
{
User user = null;
using (IDbConnection cn = Connection)
{
cn.Open();
user = cn.Query<User>("SELECT * FROM Users WHERE ID=@ID", new { ID = id }).SingleOrDefault();
}
return user;
}
public IEnumerable<User> GetAll()
{
IEnumerable<User> users = null;
using (IDbConnection cn = Connection)
{
cn.Open();
users = cn.Query<User>("SELECT * FROM Users");
}
return users;
}
}
This approach allows Dapper implementations where needed whilst keeping the Entity Framework implementation separate. Not every data read needs to make use of Dapper and there may be occasions where we need to perform reads within the scope of a unit of work transaction.
The moral of the story? Never reinvent the wheel at the expense of pragmatism!
