Working with Dapper in .NET Core

I have been using Dapper for some time now (a year or so) and it doesn't get nearly enough love. While Entity Framework is an incredible ORM it introduces a lot of overhead.

What exactly is Dapper?

Dapper is a lightweight open-source micro-ORM that holds the title for the king of performance (in terms of micro-ORMs). It makes it easy to map objects to and from your database. It is also very easy to learn, and you can get a project up and running with Dapper in no time.

While this all sounds great, there are some drawbacks compared to Entity Framework. Dapper isn't going to generate any SQL queries or class models for you, doesn't come out of the box with CRUD functionality (can easily install the Contrib package to get those features), and you lose some advanced features like change tracking.

Why use Dapper?

Entity Framework can often just become the default choice. Most developers already know about it, have used it, and are comfortable with it.

In situations where you need the performance of raw SQL queries or don't need the advanced Entity Framework features, Dapper should be the choice. It affords you a great deal of control and is very easy to use.

I have also seen Dapper used as a read-only ORM. You use it to do all the reads in your database, and use another ORM (Entity) to do all of your writes. This approach can be really nice if you decouple your data access layer properly. Otherwise, it can get quite messy.

How to use it

Dapper is easy to use and learn. Going from not knowing about Dapper at all to understanding the very basics shouldn't take more than 30 minutes.

Query for a list of items

private readonly IConnectionProvider _connectionProvider;
public CarRepository(IConnectionProvider connectionProvider)
{
    _connectionProvider = connectionProvider;
}

public async Task<IEnumerable<Car>> GetListAsync()
{
    using (var connection = _connectionProvider.Create())
    {
        return await connection.QueryAsync<Car>("select * from cars");
    }
 }

First, we receive a connection provider from dependency injection. The provider is just an abstraction on creating a database connection (SqlConnection) and returns an IDbConnection.

We use that connection to call the QueryAsync method. QueryAsync is exactly what it sounds like, it asynchronously queries your database and maps it back to an object (in this case a Car object).

Query for a single item

public async Task<Car> GetByIdAsync(int carId)
{
    string sql = "select * from cars where id = @car_id";
    using (var connection = _connectionProvider.Create())
    {
        var parameters = new 
        {
            car_id = carId
        };

        return await connection.QueryFirstAsync<Car>(sql, parameters);
    }
 }

QueryFirstAsync maps the first element returned in the sequence. There is also a QueryFirstOrDefaultAsync method that returns a default value if no element is returned.

public async Task<Car> GetByIdAsync(int carId)
{
    string sql = "select * from cars where id = @car_id";
    using (var connection = _connectionProvider.Create())
    {
        var parameters = new 
        {
            car_id = carId
        };

        return await connection.QuerySingleAsync<Car>(sql, parameters);
    }
 }

QuerySingleAsync will map a single item returned from your database but will throw an exception if there isn't exactly one element returned. Dapper also has a QuerySingleOrDefaultAsync that returns a default value if there are no elements, but will still throw an exception if there is more than one element.

Querying stored procedures

public async Task<Car> GetByIdAsync(int carId)
{
    string procedure = "sp_get_car_by_id";
    using (var connection = _connectionProvider.Create())
    {
        var parameters = new 
        {
            car_id = carId
        };

        return await connection.QueryFirstAsync<Car>(procedure, parameters, 
            commandType: CommandType.StoredProcedure);
    }
 }

We can query using stored procedures by passing in a procedure name, parameters (if any), and telling Dapper that the command is a stored procedure.

Executing stored procedures

Dapper also comes with an ExecuteAsync method that allows us to execute SQL and return the number of rows affected rather than mapping data.

// Returns the number of rows affected.
public async Task<int> UpdateAsync(int carId, string color)
{
    string procedure= "sp_update_car_color";
    using (var connection = _connectionProvider.Create())
    {
        var parameters = new 
        {
            car_id = carId,
            color
        };

        return await connection.ExecuteAsync(procedure, parameters, 
            commandType: CommandType.StoredProcedure);
    }
 }

ExecuteAsync looks the same as QueryAsync, the only difference in the call being we don't pass a class to map data back to since it will only return the number of rows affected.

Closing

Dapper is an extremely performant micro-ORM that is great for situations that you have large domain models, need extreme performance, or don't need some of the more advanced features of Entity Framework.

While it is less known than Entity Framework, it is the most popular choice for micro-ORMs.

Learn more Dapper:

Nolan Bradshaw