Creating a Data Repository using Dapper: 10 Years of .Net Compressed into Weeks #12

This post looks at writing a repository implementation using the Micro-ORM named Dapper. This post is part of a blog series ASP.Net 10 Years On. Even though this is part of a series I have tried to make each post standalone.

Update

I've improved upon this implementation. See this post for details.

Original

In the previous post we compared options for our repository implementation and we concluded that a Micro-ORM was best suited for our requirements. We decided on Dapper as our tool. To recap, we chose a Micro-ORM as we wanted to balance automation and control over SQL queries in the application. We're happy for the nontrivial stuff to be automated but we want control over any SQL that has a JOIN or more.

To kick off our repository we start with a base repository interface. All domain level repository interfaces will implement this interface as it will offer the convention we seek in making all repositories uniform:

public interface IRepository<T> where T : EntityBase, IAggregateRoot
{
    void Add(T item);
    void Remove(T item);
    void Update(T item);
    T FindByID(Guid id);
    IEnumerable<T> Find(Expression<Func<T, bool>> predicate);
    IEnumerable<T> FindAll();
}

Here is how the solution structure looks in Visual Studio. The IRepository is situated in the .Infrastructure project, since it is generic but the ICompetitionRepository can be found in the .Model project since it's domain specific:

Screen Shot 2012 12 13 at 23 25 00

Next we move to the .Dapper project and add a concrete implementation of the IRepository interface. In our requirements for the repository we desired convention. All concrete implementations of the domain specific interfaces such as ICompetitionRepository will inherit from this base repository class. This provides the convention that all domain entities that implement IAggregateRoot have CRUD operations and the ID property will always be a GUID.

public abstract class Repository<T> : IRepository<T> where T : EntityBase, IAggregateRoot
{
    private readonly string _tableName;

    internal IDbConnection Connection
    {
        get
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["SmsQuizConnection"].ConnectionString);
        }
    }

    public Repository(string tableName)
    {
        _tableName = tableName;
    }

 internal virtual dynamic Mapping(T item)
    {
        return item;
    }

    public virtual void Add(T item)
    {
        using (IDbConnection cn = Connection)
        {
            var parameters = (object)Mapping(item);
            cn.Open();
            item.ID = cn.Insert<Guid>(_tableName, parameters);
        }
    }

    public virtual void Update(T item)
    {
        using (IDbConnection cn = Connection)
        {
            var parameters = (object)Mapping(item);
            cn.Open();
            cn.Update(_tableName, parameters);
        }
    }

    public virtual void Remove(T item)
    {
        using (IDbConnection cn = Connection)
        {
            cn.Open();
            cn.Execute("DELETE FROM " + _tableName + " WHERE ID=@ID", new { ID = item.ID });
        }
    }

    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;
    }

    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;
    }

    public virtual IEnumerable<T> FindAll()
    {
        IEnumerable<T> items = null;

        using (IDbConnection cn = Connection)
        {
            cn.Open();
            items = cn.Query<T>("SELECT * FROM " + _tableName);
        }

        return items;
    }
}

The CompetitionRepository implementation will look as below. Assuming that a domain entities properties are primitive types and are named the same as the corresponding database table our code will "just work" and the CRUD operations will be implemented by way of convention:

public sealed class Competitionepository : Repository<Competition>, ICompetitionRepository
{
    public CompetitionRepository() : base("Competitions") { }
}

Note how all the CRUD methods are virtual, so in the event we want to take control of a method we can simply override the method.

Some additional methods have been added to provide additional automation/convention of repetitive tasks. The additional features are:

  • An extension method for Dapper that dynamically generates the INSERT SQL query.
  • An extension method for Dapper that dynamically generates the UPDATE SQL query.
  • A utility method that will dynamically generate simple and ONLY simple lambda expressions into SQL.

Here is the code for the first two extension methods for Dapper:

public static class DapperExtensions
{
    public static T Insert<T>(this IDbConnection cnn, string tableName, dynamic param)
    {
        IEnumerable<T> result = SqlMapper.Query<T>(cnn, DynamicQuery.GetInsertQuery(tableName, param), param);
        return result.First();
    }

    public static void Update(this IDbConnection cnn, string tableName, dynamic param)
    {
        SqlMapper.Execute(cnn, DynamicQuery.GetUpdateQuery(tableName, param), param);
    }
}

The three add-ons we discussed all point to the static methods that begin with DynamicQuery.[SomeMethod]. The static methods available in this class are:

  1. DynamicQuery.GetInsertQuery();
  2. DynamicQuery.GetUpdateQuery();
  3. DynamicQuery.GetDynamicQuery();

These methods do as their name suggests and generate a SQL query based on the property names of the dynamic 'param' variable passed by value to the method. The 3rd method GetDynamicQuery() accepts a lambda expression that will be converted into a simple SQL query. The dynamic query generator will ONLY work for basic queries with no JOINS. Any SQL query that has a JOIN or more must be hand crafted.

I wrote these utilities to accompany this blog series as a learning exercise so they are not production ready or performance tested (yet!), but with that said you can find the source code here.

Why not just use an ORM?

Most of the functionality described so far you also get when using an ORM. So why go to all this trouble to get the same thing? We really start to see the benefit of this implementation when things become a little more complex.

Mapping Properties when Saving

For example, the User domain entity looks as follows:

public sealed class User : EntityBase, IAggregateRoot
{
    public string Username { get; set; }
    public EncryptedString Password { get; set; }
}

The slight difference when inserting or updating this entity to the database is the property type named Password which has a data type of EncryptedString. This type is a custom value object in our domain model that provides string encryption. Since this is not a primitive type we need some way of mapping the correct property value to the database table column. In the database we have a Users table with three columns:

  • ID
  • Username
  • Password

In this instance we need to map the value of the Password property to the table column Password. Even though they are named the same the property accessor to get the encrypted password value is not the same as if it were a string or int data type. We can perform such a mapping by simply overriding the virtual Mapping method:

public sealed class UserRepository : Repository<User>, IUserRepository
{
    public UserRepository() : base("Users") { }

    internal override dynamic Mapping(User item)
    {
        return new
        {
            ID = item.ID,
            Username = item.Username,
            Password = item.Password.EncryptedValue
        };
    }
}

Mapping Properties when Retrieving Data

As we've discussed, if our entity matches the database table structure like-for-like then everything just works. But what happens when this is not the case? What if a single domain entity is made up of data across three database tables?

Following up our UserRepository example we need to set the value of the EncryptedString property type as follows:

public override User FindByID(Guid id)
{
    User item = null;

    using (IDbConnection cn = Connection)
    {
        cn.Open();
        var result = cn.Query("SELECT * FROM Users WHERE ID=@ID", new { ID = id }).SingleOrDefault();

        if (result != null)
        {
            item = new User();
            item.ID = result.ID;
            item.Username = result.Username;
            /* The custom mapping */
            item.Password = new EncryptedString(result.Password);
        }
    }

    return item;
}

Whenever we want to take control we can use the full range of methods from the Dapper documentation to meet our requirements. If we need to add any very custom code that goes beyond what Dapper offers we can just code this manually if we choose to.

The key point is that we never have to fight a framework when we need explicit control. We use convention to make the simple cases "just work" so we can focus on getting the more complex stuff right.

Isn't it bad to use Dynamic?

The use of the dynamic type in C# will often polarise developers into love or hate categories. There are arguments relating to performance and that the use of dynamic is more error prone since possible runtime errors will not be picked up by the compiler at compile time in the same way static types are.

Performance

The performance benchmarks from the Dapper documentation indicate that using dynamic in the data access code doesn't result in a performance hit.

For more detail on how dynamic works there is a great answer to a question at Stackoverflow.com.

Lack of static typing

In our examples we used dynamic to map properties such as:

return new
{
    ID = item.ID,
    Username = item.Username,
    Password = item.Password.EncryptedValue
};

I would contest that this is little different from manually mapping properties from a data reader object such as the following line of code:

item.Username = reader["Username"].ToString();

In either example if I make a spelling mistake I will get a runtime error regardless!

Future move to NoSql?

In the requirements there was a possibility moving the database to a cloud computing/NoSQL platform. In order to future proof the repository design I wrote a simple NoSql flavour version of the base repository just to double check my interface design makes sense before it becomes too late to change. Here is how the code looks:

public abstract class Repository<T> : IRepository<T> where T : EntityBase, IAggregateRoot
{
    private Dictionary<Guid, T> _db = new Dictionary<Guid,T>();

    public void Add(T item)
    {
        item.ID = Guid.NewGuid();
        _db.Add(item.ID, item);
    }

    public void Remove(T item)
    {
        _db.Remove(item.ID);
    }

    public void Update(T item)
    {
        _db[item.ID] = item;
    }

    public T FindByID(System.Guid id)
    {
        if (_db.ContainsKey(id))
            return _db[id];

        return default(T);
    }

    public IEnumerable<T> Find(Expression<Func<T, bool>> predicate)
    {
        return _db.Values.Where(predicate.Compile()).AsQueryable();
    }

    public IEnumerable<T> FindAll()
    {
        return _db.Values.AsQueryable();
    }
}

This is an oversimplification of how NoSQL instances work but it helps in sounding out how the repository interface works when dealing with a key-value pair. I run the repository unit test I wrote for the Dapper implementation of the CompetitionRepository to verify this proof of concept.

Get the Source Code

You can see the full project code on Github.

The DynamicQuery code is also on Github.

The base Repository for mapper can also be found here on Github.

In the next post we look at making all our layers work together via a HTTP API.

17 comments:

  1. Hi Bradley... RepoWrapper is missing in the github and when loaded your project is not compiling.

    ReplyDelete
    Replies
    1. Good catch, thanks for letting me know. Until I update the solution to correct this (soon), you can find the source code for that project here: https://github.com/bbraithwaite/RepoWrapper

      Delete
  2. Still missing RepoWrapper code?

    ReplyDelete
    Replies
    1. The solution file has been updated. The repo wrapper project has been removed and the classes can now be found within the .Dapper project. I committed these changes last night so you may need to checkout the latest branch.

      The main class from the RepoWrapper class can be found here:

      https://github.com/bbraithwaite/SmsQuiz/blob/master/src/BB.SmsQuiz.Repository.Dapper/DynamicQuery.cs

      Delete
    2. What happened to all the code that dealt with expressions? That is the part I was most interested in.

      Delete
    3. The project is still up on my Github page (It was deleted for about an hour today as I was changing something). I'm no longer using it within the quiz project I've been blogging about as the future direction of the application doesn't suit it (it will make sense as the series unfolds). I replaced the use of expression trees with anonymous types e.g. instead of saying: c => c.ProductName = "something" && p.CategoryID = 1 you can code: new { Productname = "something", CategoryID = 1 }. I will address this with new posts soon.

      I may take RepoWrapper further in the future just not as part of this series. There are some things I'm not happy with, hence the disclaimer on the Github page about it not being production ready. Here is a reminder of the project link: https://github.com/bbraithwaite/RepoWrapper

      If you want to get hold of similar wrappers, check out these links:

      http://www.codeproject.com/Articles/22770/How-To-LINQ-To-SQL-Transformation
      http://blogs.msdn.com/b/mattwar/archive/2008/11/18/linq-links.aspx

      Delete
    4. Thanks for the links. The previous version of DynamicQuery is a direction we are going. We're currently using Entity Framework and pushing close to ONLY 500k transactions per hour and having major issues with entity framework as a bottleneck.

      So, we're in a bind to find a solution pretty quickly that allows us to use expression trees and Dapper with a Expression tree wrapper seems to be the way we want to go.

      Delete
    5. Understood. To be honest, I spent a few hours MAX on RepoWrapper so I certainly wouldn't call it production ready! Not sure if the following screen cast "might" help? http://tekpub.com/products/ft_sullivan

      Delete
  3. Nice post!! You can use DapperExtensions, its helps you with the mapping issue. http://nilsanders.blogspot.se

    ReplyDelete
  4. How would you handle transaction using this Repository approach if I have to work on two repositories in a single process? If I encounter an error on the second entity after the first entity has been saved, or updated, how can I roll back the changes?

    ReplyDelete
    Replies
    1. Hi, in this application I do not need it, thus I've applied YAGNI. It is possible to have transaction scope using Dapper but you would need to apply a pattern like the unit of work pattern. I would maybe consider using an ORM such as Entity Framework for a more complex database structure and just use Dapper for read only access if you desire its performance. Stackoverflow use a similar approach and it works well for them!

      Delete
  5. What about connection instance management? If you use two repository objects each creating their own connection withing the same transaction, you'll wind up with a distributed transaction. I am currently on a search for something like Tophat except that Tophat does not currently support multiple connection strings. Enterprise Libray Data Access block manages connection instances but I don't want the dependency on Entlib just for connection management.

    ReplyDelete
    Replies
    1. In the context of this app, it doesn't need it thus it doesn't do it. If requirements are more complex than this simple little app then I would maybe switch to use a hybrid of ORMs such as EF, NHibernate etc that offer such features and for the read access where we really want the performance of a tool such as Dapper, just jump in and replace the Linq to SQL. Have you checked out: http://www.toptensoftware.com/petapoco/?

      Delete
    2. Update: this follow up post may help you out? http://www.contentedcoder.com/2013/05/orms-don-reinvent-wheel.html

      Delete
  6. I see this approach breaking down fairly quickly as your queries become more complex and you wind up overriding most if not all of your base repository functionality. At which point this repository interfaces makes absolutely no sense. The interface you're using works great with ORMs such as NHibernate and Entity Framework with change tracking, lazy-loading and etc. Dapper simply isn't made for that.

    ReplyDelete
    Replies
    1. Thanks for your comment.

      I'm not sure if you noticed, but there is an updated version of this post that highlights improvements to this approach?

      I both agree and disagree, it all depends on context! If you had a bunch of POCOs and didn't (couldn't) use an ORM or had a database that was vastly different to the domain model then there may be some value in this type of approach.

      I've also discussed the problem impedance mismatch a little more in another post: http://www.contentedcoder.com/2013/08/repository-vs-domain-model-vs-data.html

      Delete
  7. Hi,
    Can this approach work also for GroupBy queries.
    public IEnumerable Find(Expression> predicate)
    {
    return _db.Values.Where(predicate.Compile()).AsQueryable();
    }

    In the above Find method a the native SQL query is generated using the predicate. Similar to this can this approach work for GroupBy function with predicates as shows below.
    public IEnumerable GroupBy(Expression> keySelector, Expression> resultSelector,)
    {
    return _db.Values.Where(predicate.Compile()).AsQueryable();
    }

    ReplyDelete