When dealing with databases access in my C# applications I typically use Entity Framework’s code first approach to generate the model. Entity Framework is very nice, but it has two drawbacks:
- Generally, it is slow compared to raw SQL - primary reason i believe is that Entity Framework is not able to generate very efficient SQL behind the scene.
- Many-To-Many relationships are hard to represent in Entity Framework. Join tables are needed to be explicitly represented using a Join entity.
While researching a project I found another library: Dapper.
Dapper is an light-weight Object-Relational Mapping (ORM) library for .NET. While Entity Framework is primarily used as code-first, Dapper only supports Database first.
To use Dapper, a plain model class is written for each table (very similar to Entity framework) . The class for a User table with an autoincrement Id, Email and a Name would look like this:
class User
{
public int Id { get; set; }
public string Email { get; set; }
public string Name { get; set; }
}
Assuming we have an instance of SqlConnection to an SQL DBMS such as MSSQL or MySQL, we can use dapper to fetch our user rows and transform the rows into a collection of User instances like so:
var sql = "select \* from [User]";
var users = connection.Query<User>(sql);
the users variable is an IEnumerable type, which can be iterated using Linq.
Note: the SQL is tightly coupled to the underlying DBMS - meaning that the same SQL cannot be used to connect to different DBMS’.
Dapper also supports the concept of ‘multi-mapping’ which provides the ability to map a single row to multiple objects. This is especially useful when dealing with joins between two or more tables. Consider the following example which joins the Post table with the User and return a Post instance where the User property has been set to the particular user:
var sql = @"select \* from [Post]
left join [User] on [Post].UserId = [User].Id";
var postsWithUsers = connection.Query<Post, User, Post>(sql, (post, user) =>
{
post.User = user;
return post;
});
postsWithUsers is an IEnumerable returned by the query. Each Post instance will have a User property pointing to the User instance found during the join.
Dapper makes this work by automatically splitting the joined row on the Id columns. This can be changed through configuration if needed by providing a value on the splitOn parameter of the Query method.
Dapper is very fast, primarily due to raw SQL is being used. Many to many relations can also be expressed using Dapper’s multi-mapper, however, the same drawback naturally exists regarding the join table - but this problem is “pushed” to the database since it does not have to be represented as a model in the code.
If you know SQL and have no problems with being tighter coupled with the underlying database, I highly recommend Dapper.