Dapper and Entity Framework Core in .NET 6

In this article, I will show how to use Dapper and Entity Framework Core together in the same application.

Introduction to Dapper

What is Dapper?

It is a simple object mapping framework or Micro-ORM that helps us map SQL query result data to a .NET class efficiently. It extends IDbConnection and simplifies setup, execution and provides useful extension methods to query our database.

By using Dapper we can write SQL statements as if we were doing it in SQL Server. What’s more, Dapper has great performance because it doesn’t translate the queries we write in .NET to SQL.

Also, it supports multiple database providers.

Dapper vs Entity Framework Core

EF Core has many features that an ORM should have, while Dapper does not have many of these features. This is why it is wrong to compare them. Even so…

Dapper is a simple Micro ORM that has minimal features. Dapper is very fast, this does not mean that Entity Framework Core is slower. With each EF Core update, performance also seems to improve. Dapper is heaven for those who still like to work with queries instead of LINQ with EF Core.

This is why Dapper is amazing at handling complex queries that have multiple joins and really big logic.

And EF Core is great for class generation, object tracking, mapping multiple nested classes, and much more. Usually, it comes down to performance and features when talking about these 2 ORMs.

Therefore, it is possible to use both ORMs in the same project, taking advantage of the best of each one. If we have complex queries and we think using Dapper will improve performance, this is an option.

Let’s go to code?

How Dapper can be easily integrated with Entity Framework Core can be found in the source code of this article, we will see how to work with these two frameworks together in the same application. I’ve created a small Web API in ASP.NET Core, added repositories and usage examples. Also a transaction combining the two ORMs.

Facilities needed to use “Dapper“:

Install-Package Dapper
Install-Package Microsoft.Data.SqlClient

Installations required to use “Entity Framework Core” in our project:

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Relational
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

We will use the SQL Server Express LocalDB database, for this we launch the following command that will create the database and add test data when executing the API project:

Add-migration Initial
Update-database

Entity Framework Core Configuration

First, data access is done through a model that is made up of entity classes and a context object that represents a session with the database. This context object allows you to query and save data DbContext:

public class ApplicationContext : DbContext, IApplicationContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Post> Posts => Set<Post>();
    public DbSet<Comment> Comments => Set<Comment>();
    public DbSet<PostDetail> PostDetails => Set<PostDetail>();

    // Represents an open connection to a data source and
    // is implemented by .NET data providers that access relational databases.
    public IDbConnection Connection => Database.GetDbConnection();

    public ApplicationContext(DbContextOptions<ApplicationContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

        base.OnModelCreating(builder);
    }

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken())
    {
        return await base.SaveChangesAsync(cancellationToken);
    }
}

Second, interface IReadRepositoryBasethat implements read-only repository in EF Core:

public interface IReadRepositoryBase<TEntity> where TEntity : class
{
    IQueryable<TEntity> GetAll(bool asNoTracking = true);

    IQueryable<TEntity> GetAllBySpec(Expression<Func<TEntity, bool>> predicate, bool asNoTracking = true);

    Task<TEntity?> GetByIdAsync<TId>(TId id, CancellationToken cancellationToken = default) where TId : notnull;

    Task<TEntity?> GetBySpecAsync<Spec>(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);

    Task<ICollection<TEntity>> ListAsync(CancellationToken cancellationToken = default);

    Task<ICollection<TEntity>> ListAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);

    Task<int> CountAsync(CancellationToken cancellationToken = default);

    Task<int> CountAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);

    Task<bool> AnyAsync(CancellationToken cancellationToken = default);

    Task<bool> AnyAsync(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default);

    IQueryable<TEntity> GetAllIncluding(params Expression<Func<TEntity, object>>[] includeProperties);
}

Third, interface IRepositoryBasethat implements read and write repository in EF Core:

public interface IRepositoryBase<TEntity> : IReadRepositoryBase<TEntity> where TEntity : class
{
    IUnitOfWork UnitOfWork { get; }

    TEntity Add(TEntity entity);

    Task<TEntity> AddAsync(TEntity entity, CancellationToken cancellationToken = default);

    ICollection<TEntity> AddRange(ICollection<TEntity> entities);

    Task<int> AddRangeAsync(ICollection<TEntity> entities, CancellationToken cancellationToken = default);

    void Delete(TEntity entity);

    Task<int> DeleteAsync(TEntity entity, CancellationToken cancellationToken = default);

    void DeleteRange(ICollection<TEntity> entities);

    Task<int> DeleteRangeAsync(ICollection<TEntity> entities, CancellationToken cancellationToken = default);

    void Update(TEntity entity);

    Task<int> UpdateAsync(TEntity entity, CancellationToken cancellationToken = default);
}

Dapper configuration

To work with Dapper, the only requirements are a “DbConnection” , the SQL text, and some optional parameters, such as a “DbTransaction” , command timeout, query parameters, etc.

First of all, read class , there is no binding here to any DBContext (Entity Framework Core) object because it really doesn’t make sense to share the connections between Entity Framework Core and Dapper when you read the data. In the read implementation, we are working directly with the object IDbConnection, such as SqlConnection, with a known connection string. In order to execute the queries, we initialize it in the constructor using the connection string:

public class ApplicationReadDbConnection : IApplicationReadDbConnection, IDisposable
{
    private readonly IDbConnection connection;

    public ApplicationReadDbConnection(IConfiguration configuration)
    {
        connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection"));
    }

    public async Task<IReadOnlyList<T>> QueryAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, CancellationToken cancellationToken = default)
    {
        return (await connection.QueryAsync<T>(sql, param, transaction)).AsList();
    }

    public async Task<IEnumerable<TResult>> QueryMapAsync<T1, T2, TResult>(string sql, Func<T1, T2, TResult> map, object? param = null, IDbTransaction? transaction = null, string splitOn = "Id", CancellationToken cancellationToken = default)
    {
        return await connection.QueryAsync(sql, map, param, transaction, true, splitOn);
    }

    public async Task<IEnumerable<TResult>> QueryMapAsync<T1, T2, T3, TResult>(string sql, Func<T1, T2, T3, TResult> map, object? param = null, IDbTransaction? transaction = null, string splitOn = "Id", CancellationToken cancellationToken = default)
    {
        return await connection.QueryAsync(sql, map, param, transaction, true, splitOn);
    }

    public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, CancellationToken cancellationToken = default)
    {
        return await connection.QueryFirstOrDefaultAsync<T>(sql, param, transaction);
    }

    public async Task<T> QuerySingleAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, CancellationToken cancellationToken = default)
    {
        return await connection.QuerySingleAsync<T>(sql, param, transaction);
    }

    public void Dispose()
    {
        connection.Dispose();
    }
}

Second, read-write class , the use case of sharing the connection comes into the picture when there is data writing involved. In the write implementation we are reusing the context object to execute queries and commands with the help of Dapper. We can see that we are injecting the one IApplicationDbContextthat belongs to the Entity Framework in the Constructor. This is how we can share the connection and the transaction. Using the context connection, we perform the read and write operations using Dapper. Let’s see how it is implemented:

public class ApplicationWriteDbConnection : IApplicationWriteDbConnection
{
    private readonly IApplicationContext context;

    public ApplicationWriteDbConnection(IApplicationContext context)
    {
        this.context = context;
    }

    public async Task<int> ExecuteAsync(string sql, object? param = null, IDbTransaction? transaction = null, CancellationToken cancellationToken = default)
    {
        return await context.Connection.ExecuteAsync(sql, param, transaction);
    }

    public async Task<IReadOnlyList<T>> QueryAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, CancellationToken cancellationToken = default)
    {
        return (await context.Connection.QueryAsync<T>(sql, param, transaction)).AsList();
    }

    public async Task<IEnumerable<TResult>> QueryMapAsync<T1, T2, TResult>(string sql, Func<T1, T2, TResult> map, object? param = null, IDbTransaction? transaction = null, string splitOn = "Id", CancellationToken cancellationToken = default)
    {
        return await context.Connection.QueryAsync(sql, map, param, transaction, true, splitOn);
    }

    public async Task<IEnumerable<TResult>> QueryMapAsync<T1, T2, T3, TResult>(string sql, Func<T1, T2, T3, TResult> map, object? param = null, IDbTransaction? transaction = null, string splitOn = "Id", CancellationToken cancellationToken = default)
    {
        return await context.Connection.QueryAsync(sql, map, param, transaction, true, splitOn);
    }

    public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, CancellationToken cancellationToken = default)
    {
        return await context.Connection.QueryFirstOrDefaultAsync<T>(sql, param, transaction);
    }

    public async Task<T> QuerySingleAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, CancellationToken cancellationToken = default)
    {
        return await context.Connection.QuerySingleAsync<T>(sql, param, transaction);
    }
}

Let’s now see a repository where we use Dapper and EF Core

In this repository we can make use of both Dapper and EF Core, according to needs. I show some examples of uses, such as queries with one-to-one, one-to-many relationships… Also the same transaction that Dapper and EF Core implement. If something fails, all changes are reverted, both those made with Dapper and with EF Core.

public class PostRepository : BaseRepository<Post>, IPostRepository
{
    private readonly ApplicationContext _dbContext;
    private readonly IApplicationReadDbConnection _readDbConnection;
    private readonly IApplicationWriteDbConnection _writeDbConnection;

    public PostRepository(ApplicationContext dbContext, IApplicationReadDbConnection readDbConnection, IApplicationWriteDbConnection writeDbConnection) :
        base(dbContext)
    {
        _dbContext = dbContext ?? throw new ArgumentNullException(nameof(dbContext));
        _readDbConnection = readDbConnection ?? throw new ArgumentNullException(nameof(readDbConnection));
        _writeDbConnection = writeDbConnection ?? throw new ArgumentNullException(nameof(writeDbConnection));
    }

    // One-to-one
    public async Task<Post?> GetRelationOneToOneAsync(int id)
    {
        var result = await _readDbConnection.QueryMapAsync<Post, PostDetail, Post>(
            sql: "SELECT p.Id, p.UserId, p.Title, p.Body, pd.Created, pd.LastModified FROM Posts p INNER JOIN PostDetails pd ON p.Id = pd.PostId Where p.Id = @Id;",
            map: (post, detail) =>
            {
                post.Detail = detail;
                return post;
            },
            param: new { id },
            splitOn: "Created");

        return result.FirstOrDefault();
    }

    //One-to-many
    public async Task<Post?> GetRelationOneToManyAsync(int id)
    {
        var postMap = new Dictionary<int, Post>();

        var result = await _readDbConnection.QueryMapAsync<Post, Comment, Post>(
            sql: "SELECT p.Id, p.UserId, p.Title, p.Body, c.Id, c.PostId, c.Email, c.Name, c.Body FROM Posts p INNER JOIN Comments c ON p.Id = c.PostId Where p.Id = @Id;",
            map: (post, comment) =>
            {
                comment.PostId = post.Id; //non-reference back link

                //check if this order has been seen already
                if (postMap.TryGetValue(post.Id, out Post? existingPost))
                    post = existingPost;
                else
                    postMap.Add(post.Id, post);

                post.Comments.Add(comment);
                return post;
            },
            param: new { id },
            splitOn: "Id");

        return result.FirstOrDefault();
    }

    //Multi mapping
    public async Task<Post?> GetMultiMappingAsync(int id)
    {
        var postMap = new Dictionary<int, Post>();

        var result = await _readDbConnection.QueryMapAsync<Post, Comment, PostDetail, Post>(
            sql: "SELECT p.Id, p.UserId, p.Title, p.Body, " +
            "c.Id, c.PostId, c.Email, c.Name, c.Body, " +
            "pd.Created " +
            "FROM Posts p " +
            "INNER JOIN Comments c " +
            "ON p.Id = c.PostId " +
            "INNER JOIN PostDetails pd " +
            "ON p.Id = pd.PostId " +
            "Where p.Id = @Id;",
            map: (post, comment, detail) =>
            {
                if (post.Detail is null)
                    post.Detail = detail;

                comment.PostId = post.Id; //non-reference back link

                //check if this order has been seen already
                if (postMap.TryGetValue(post.Id, out Post? existingPost))
                    post = existingPost;
                else
                    postMap.Add(post.Id, post);

                post.Comments.Add(comment);
                return post;
            },
            param: new { id },
            splitOn: "Id,Created");

        return result.FirstOrDefault();
    }

    public async Task<IReadOnlyList<Post>> SearchPostByText(string text)
    {
        return await _readDbConnection.QueryAsync<Post>(
            sql: "SELECT * FROM Posts WHERE title LIKE @Text or body LIKE @Text",
            param: new { Text = $"%{ text.Trim() }%" });
    }

    /* Transaction Dapper and EF Core */
    public async Task SampleTransaction()
    {
        _dbContext.Connection.Open();

        using var transaction = _dbContext.Connection.BeginTransaction();

        try
        {
            // TRANSACTION
            _dbContext.Database.UseTransaction(transaction as DbTransaction);

            // add user with EF Core
            var user = new User { Name = "Ervin Howell", Email = "Julianne.OConner@kory.org", Username = "Clementine", Address = new Address("Douglas Extension", "McKenziehaven", "McKenziehaven", "Germany", "59590-4157") };
            await _dbContext.Users.AddAsync(user);
            await _dbContext.SaveChangesAsync();

            // add post with Dapper
            var postId = await _writeDbConnection.QuerySingleAsync<int>(
                sql: $"insert into Posts(UserId, Title, Body) values (@User, @Title, @Body);SELECT CAST(SCOPE_IDENTITY() as int)",
                param: new { User = 1, Title = "ullam et saepe reiciendis voluptatem", Body = "nsit amet autem assumenda provident rerum culpa" },
                transaction: transaction
                );

            if (postId == 0) throw new Exception("error post id");

            // add detail with EF Core
            var detail = new PostDetail { PostId = postId, Created = DateTime.Now };
            await _dbContext.PostDetails.AddAsync(detail);
            await _dbContext.SaveChangesAsync();

            // add comments with Dapper
            var count = await _writeDbConnection.ExecuteAsync(
                sql: @"insert into Comments(PostId, Email, Name, Body) values (@PostId, @Email, @Name, @Body)",
                param: new Comment[] {
                        new Comment { PostId = postId, Email = "Shanna@melissa.tv", Name = "sunt aut facere repellat provident", Body = "occaecati excepturi optio reprehenderit" },
                        new Comment { PostId = postId, Email = "Clementine Bauch", Name = "ea molestias quasi exercitationem", Body = "doloribus vel accusantium quis pariatur" }
                },
                transaction: transaction
              );

            if (count != 2) throw new Exception("error adding posts");

            transaction.Commit();
            // COMMIT
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            throw;
        }
        finally
        {
            _dbContext.Connection.Close();
        }
    }
}

That’s all for now, I’ll keep updating and adding content in this post. I hope this article has given you a good idea on how to easily integrate Dapper with Entity Framework Core, either to optimize critical paths or to work around limitations.