How to implement Unit Of Work pattern with Dapper?

Question

Currently, I am trying to use Dapper ORM with Unit Of Work + Repository Pattern.

I want to use Unit of Work as opposed to a simple dapper Repository due to the fact that my insert and updates require a degree of transaction processing. I have been unable to find any useful examples as most seem to use Entity Framework and have leakage issue within the Unit of Work.

Could someone please point me in the right direction?

Answer 1

This Git project is very helpful. I started from the same and did some changes as per my need.

public sealed class DalSession : IDisposable
{
    public DalSession()
    {
        _connection = new OleDbConnection(DalCommon.ConnectionString);
        _connection.Open();
        _unitOfWork = new UnitOfWork(_connection);
    }

    IDbConnection _connection = null;
    UnitOfWork _unitOfWork = null;

    public UnitOfWork UnitOfWork
    {
        get { return _unitOfWork; }
    }

    public void Dispose()
    {
        _unitOfWork.Dispose();
        _connection.Dispose();
    }
}

public sealed class UnitOfWork : IUnitOfWork
{
    internal UnitOfWork(IDbConnection connection)
    {
        _id = Guid.NewGuid();
        _connection = connection;
    }

    IDbConnection _connection = null;
    IDbTransaction _transaction = null;
    Guid _id = Guid.Empty;

    IDbConnection IUnitOfWork.Connection
    {
        get { return _connection; }
    }
    IDbTransaction IUnitOfWork.Transaction
    {
        get { return _transaction; }
    }
    Guid IUnitOfWork.Id
    {
        get { return _id; }
    }

    public void Begin()
    {
        _transaction = _connection.BeginTransaction();
    }

    public void Commit()
    {
        _transaction.Commit();
        Dispose();
    }

    public void Rollback()
    {
        _transaction.Rollback();
        Dispose();
    }

    public void Dispose()
    {
        if(_transaction != null)
            _transaction.Dispose();
        _transaction = null;
    }
}

interface IUnitOfWork : IDisposable
{
    Guid Id { get; }
    IDbConnection Connection { get; }
    IDbTransaction Transaction { get; }
    void Begin();
    void Commit();
    void Rollback();
}

Now, your repositories should accept this UnitOfWork in some way. I choose Dependency Injection with Constructor.

public sealed class MyRepository
{
    public MyRepository(IUnitOfWork unitOfWork) 
    {
        this.unitOfWork = unitOfWork;
    }

    IUnitOfWork unitOfWork = null;

    //You also need to handle other parameters like 'sql', 'param' ect. This is out of scope of this answer.
    public MyPoco Get()
    {
        return unitOfWork.Connection.Query(sql, param, unitOfWork.Transaction, .......);
    }

    public void Insert(MyPoco poco)
    {
        return unitOfWork.Connection.Execute(sql, param, unitOfWork.Transaction, .........);
    }
}

And then you call it like this:

With transaction:

using(DalSession dalSession = new DalSession())
{
    UnitOfWork unitOfWork = dalSession.UnitOfWork;
    unitOfWork.Begin();
    try
    {
        //Your database code here
        MyRepository myRepository = new MyRepository(unitOfWork);
        myRepository.Insert(myPoco);
        //You may create other repositories in similar way in same scope of UoW.

        unitOfWork.Commit();
    }
    catch
    {
        unitOfWork.Rollback();
        throw;
    }
}

Without Transaction:

using(DalSession dalSession = new DalSession())
{
    //Your database code here
    MyRepository myRepository = new MyRepository(dalSession.UnitOfWork);//UoW have no effect here as Begin() is not called.
    myRepository.Insert(myPoco);
}

Please note that, UnitOfWork is more than DBTransaction.

More details about Repository in above code could be found here.

I have already post this code here. But this question looks more relevant to me for this code; so I am posting again instead of just link to original answer.

Answer 2

Edit 2018-08-03: Amit's comment really got me thinking, and made me realize that the repository's don't in fact NEED to be properties on the context itself. But rather, repositories could have a dependency on the context. Rather than continue to make incremental changes to the code samples below. I will simply reference a git repo I've put together to contain this concept.

Standing on the shoulders of others here.

Considering this answer is top in most Google searches pertaining to "dapper" and "unit of work". I wanted to provide my approach, which I've used to great effect several times now.

Using a ficitious (and overly simplified) example:

public interface IUnitOfWorkFactory
{
    UnitOfWork Create();
}

public interface IDbContext 
{
    IProductRepository Product { get; set; }

    void Commit();
    void Rollback();
}

public interface IUnitOfWork
{
    IDbTransaction Transaction { get;set; }

    void Commit();
    void Rollback();
}


public interface IProductRepository 
{
    Product Read(int id);
}

Note how neither IDbContext or IUnitOfWorkFactory implements IDisposable. This is purposefully done to avoid a leaky abstraction. Instead the reliance is on Commit()/Rollback() to take care of cleanup and disposal.

A couple of points before sharing implementations.

• IUnitOfWorkFactory is responsible for instantiating the UnitOfWork and brokering the database connection.
• IDbContext is the repository backbone.
• IUnitOfWork is an encapsulation of IDbTransaction, and ensures that when working with multiple repositories, they share a single database context.

Implementation of IUnitOfWorkFactory

public class UnitOfWorkFactory<TConnection> : IUnitOfWorkFactory where TConnection : IDbConnection, new()
{
    private string connectionString;

    public UnitOfWorkFactory(string connectionString)
    {
        if (string.IsNullOrWhiteSpace(connectionString))
        {
            throw new ArgumentNullException("connectionString cannot be null");
        }

        this.connectionString = connectionString;
    }

    public UnitOfWork Create()
    {
        return new UnitOfWork(CreateOpenConnection());
    }

    private IDbConnection CreateOpenConnection()
    {
        var conn = new TConnection();
        conn.ConnectionString = connectionString;

        try
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
        }
        catch (Exception exception)
        {
            throw new Exception("An error occured while connecting to the database. See innerException for details.", exception);
        }

        return conn;
    }
}

Implementation of IDbContext

public class DbContext : IDbContext
{
    private IUnitOfWorkFactory unitOfWorkFactory;

    private UnitOfWork unitOfWork;

    private IProductRepository product;

    public DbContext(IUnitOfWorkFactory unitOfWorkFactory)
    {
        this.unitOfWorkFactory = unitOfWorkFactory;
    }

    public ProductRepository Product =>
        product ?? (product = new ProductRepository(UnitOfWork));

    protected UnitOfWork UnitOfWork =>
        unitOfWork ?? (unitOfWork = unitOfWorkFactory.Create());

    public void Commit()
    {
        try
        {
            UnitOfWork.Commit();
        }
        finally
        {
            Reset();
        }
    }

    public void Rollback()
    {
        try
        {
            UnitOfWork.Rollback();
        }
        finally
        {
            Reset();
        }
    }

    private void Reset()
    {
        unitOfWork = null;
        product = null;
    }
}

Implementation of IUnitOfWork

public class UnitOfWork : IUnitOfWork
{
    private IDbTransaction transaction;

    public UnitOfWork(IDbConnection connection)
    {
        transaction = connection.BeginTransaction();
    }

    public IDbTransaction Transaction =>
        transaction;

    public void Commit()
    {
        try
        {
            transaction.Commit();
            transaction.Connection?.Close();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
        finally
        {
            transaction?.Dispose();
            transaction.Connection?.Dispose();
            transaction = null;
        }
    }

    public void Rollback()
    {
        try
        {
            transaction.Rollback();
            transaction.Connection?.Close();
        }
        catch
        {
            throw;
        }
        finally
        {
            transaction?.Dispose();
            transaction.Connection?.Dispose();
            transaction = null;
        }
    }
}

Implementation of IProductRepository

public class ProductRepository : IProductRepository
{
    protected readonly IDbConnection connection;
    protected readonly IDbTransaction transaction;

    public ProductRepository(UnitOfWork unitOfWork)
    {
      connection = unitOfWork.Transaction.Connection;
      transaction = unitOfWork.Transaction;
    }

    public Product Read(int id)
    {
        return connection.QuerySingleOrDefault<Product>("select * from dbo.Product where Id = @id", new { id }, transaction: Transaction);
    }
}

To access the database, simply instantiate DbContext or inject using the IoC container of your choice (I personnally use the IoC container provided by .NET Core).

var unitOfWorkFactory = new UnitOfWorkFactory<SqlConnection>("your connection string");
var db = new DbContext(unitOfWorkFactory);

Product product = null;

try 
{
    product = db.Product.Read(1);
    db.Commit();
}
catch (SqlException ex)
{
    //log exception
    db.Rollback();
}

The explicit need for Commit() for this simple read-only operation seems excessive, but pays dividends as the system grows. And apparently, offers a minor performance benefit according to Sam Saffron. You "can" also omit the db.Commit() on simple read operations, by doing this though you leaving the connection hanging open and put the onus of cleaning things up onto the garbage collector. So this isn't recommended.

I typically bring the DbContext into the fold at the service-tier, where it works in unison with other services to form the "ServiceContext". I then reference this ServiceContext in the actual MVC layer.

As another point of mention, it's recommended to use async throughout the stack if you can. It is omitted here for simplicity.

Answer 3

Okay, it's been half a decade since the OP asked, but as I keep coming across this question when I develop with Dapper (or anything really, this isn't really very Dapper specific). Here's my two cents.

First Let's talk about the other answers:

pimbrouwers' answer IDbContext manages Unit of Work in a very similar way to how entity framework does it. It's perfectly sensible and easy to understand. But the major drawback is that you end up passing a IDbContext to all your business code. It's a bit of a god object. Just like in EF. I prefer to inject individual repositories and make it explicit what database stuff I'm going to be doing, instead of having everything in my domain model always just one . away. However, if you don't agree with my 'god object' objection, pim's answer sounds like the right one for you.

Amit Joshi's answer has the MyRepository take the unit of work as a constructor parameter. This means you can't inject Repositories anymore. This can be solved by injecting repository factories instead, but this is certainly its own level of hassle.

A quick aside: In some of these answers the word "transaction" and "unit of work" are used interchangeably. In practice here they have a 1:1 relationship, but they aren't the same thing. The "transaction" is the db implementation, the "unit of work" is more of a higher level conceptual thing. If we had more persistence that just one database, there would be a difference, and the UOW would contain more than just one transaction. So, to avoid confusion, "Transaction" is probably not a great word to use in our UOW interface.

So here's my way:

I'll start with the Usage

// Business code. I'm going to write a method, but a class with dependencies is more realistic
static async Task MyBusinessCode(IUnitOfWorkContext context, EntityRepoitory repo)
{
    var expectedEntity = new Entity {Id = null, Value = 10};

    using (var uow = context.Create())
    {
        expectedEntity.Id = await repo.CreateAsync(expectedEntity.Value);
        await uow.CommitAsync();
    }

    using (context.Create())
    {
         var entity = await repo.GetOrDefaultAsync(expectedEntity.Id.Value);
         entity.Should().NotBeNull();
         entity.Value.Should().Be(expectedEntity.Value);
    }
}

The unit of work just wraps a transaction and is shortlived:

public class UnitOfWork : IDisposable
{

    private readonly SQLiteTransaction _transaction;
    public SQLiteConnection Connection { get; }

    public bool IsDisposed { get; private set; } = false;

    public UnitOfWork(SQLiteConnection connection)
    {
        Connection = connection;
        _transaction = Connection.BeginTransaction();
    }

    public async Task RollBackAsync()
    {
        await _transaction.RollbackAsync();
    }

    public async Task CommitAsync()
    {
        await _transaction.CommitAsync();
    }

    public void Dispose()
    {
        _transaction?.Dispose();

        IsDisposed = true;
    }
}

The Context is more interesting. It's the way in which the repos and the unit of works communicate behind the scenes.

There's one interface for the business code to manage a unit of work, and one for the repo to abide by that unit of work.

public class UnitOfWorkContext : IUnitOfWorkContext, IConnectionContext
{
    private readonly SQLiteConnection _connection;
    private UnitOfWork _unitOfWork;

    private bool IsUnitOfWorkOpen => !(_unitOfWork == null || _unitOfWork.IsDisposed);

    public UnitOfWorkContext(SQLiteConnection connection)
    {
        _connection = connection;
    }

    public SQLiteConnection GetConnection()
    {
        if (!IsUnitOfWorkOpen)
        {
            throw new InvalidOperationException(
                "There is not current unit of work from which to get a connection. Call BeginTransaction first");
        }

        return _unitOfWork.Connection;
    }

    public UnitOfWork Create()
    {
        if (IsUnitOfWorkOpen)
        {
            throw new InvalidOperationException(
                "Cannot begin a transaction before the unit of work from the last one is disposed");
        }

        _unitOfWork = new UnitOfWork(_connection);
        return _unitOfWork;
    }
}

public interface IConnectionContext
{
    SQLiteConnection GetConnection();
}

public interface IUnitOfWorkContext
{
    UnitOfWork Create();
}

Here's how the repo does that:

public class EntityRepository
{
    private readonly IConnectionContext _context;

    public EntityRepository(IConnectionContext context)
    {
        _context = context;
    }

    public async Task<int> CreateAsync(int value)
    {
        return await _context.GetConnection().QuerySingleAsync<int>(
            @"
insert into Entity (Value) values (@value);
select last_insert_rowid();
", new { value });
    }

    public async Task<Entity> GetOrDefaultAsync(int id)
    {
        return await _context.GetConnection().QuerySingleOrDefaultAsync<Entity>(
            @"
select * from Entity where Id = @id
", new { id });
    }
}

And finally here's DI. Do the setup. Here's a single threaded console application Example. I imagine it would be sensible to make it a singleton or per request. The implementation of UnitOfWorkContext can be changed to match your threading choices anyway (Eg by using a UnitOfWorkContext with a thread static UOW).

public static void Main(string[] args)
{
    using (var connection = new SQLiteConnection("Data Source=:memory:"))
    {
        connection.Open();
        Setup(connection);
        var context = new UnitOfWorkContextContext(connection);
        var repo = new EntityRepository(context);

        MyBusinessCode(repo, context).ConfigureAwait(false).GetAwaiter().GetResult();
    }
}

Full version on Github: https://github.com/NathanLBCooper/unit-of-work-example

Analysis:

We've eliminated god objects and don't need to create factories for all our repositories. the cost is that we've got a little bit more of a subtle non-obvious link between our repos and the Unit of Work stuff. There's no boiler plate, but we do need to be careful about what lifetime we give our context object, especially when multithreading.

I think this is a trade-off that's worth it, but that's me.

PS

I'll add one thing. Maybe you've looked up this answer because you've started using dapper. Right now all your repository methods are separate atomic operations and you feel no need to combine them into transactions yet. Then for the time being you don't need to do any of this. Close this browser window, write your repositories in the most simple and obvious way and be happy.

Answer 4

There is no need for a hand-rolled solution for this. What you want can be achieved very simply using the classes already in the framework.

/// <summary>
/// Register a single instance using whatever DI system you like.
/// </summary>
class ConnectionFactory
{
    private string _connectionString;

    public ConnectionFactory(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IDbConnection CreateConnection()
    {
        return new SqlConnection(_connectionString);
    }
}


/// <summary>
/// Generally, in a properly normalized database, your repos wouldn't map to a single table,
/// but be an aggregate of data from several tables.
/// </summary>
class ProductRepo
{
    private ConnectionFactory _connectionFactory;

    public ProductRepo(ConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public Product Get(int id)
    {
        // Allow connection pooling to worry about connection lifetime, that's its job.
        using (var con = _connectionFactory.CreateConnection())
        {
            return con.Get<Product>(id);
        }
    }

    // ...
}

class OrderRepo
{
    // As above.
    // ...
}

class ProductController : ControllerBase
{
    private ProductRepo _productRepo;
    private OrderRepo _orderRepo;

    public ProductController(ProductRepo productRepo, OrderRepo orderRepo)
    {
        _productRepo = productRepo;
        _orderRepo = orderRepo;
    }

    [HttpGet]
    public Task<IAsyncResult> Get(int id)
    {
        // This establishes your transaction.
        // Default isolation level is 'serializable' which is generally desirable and is configurable.
        // Enable async flow option in case subordinate async code results in a thread continuation switch.
        // If you don't need this transaction here, don't use it, or put it where it is needed.
        using (var trn = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
        {
            Product product = _productRepo.Get(id);

            // Use additional repositories and do something that actually requires an explicit transaction.
            // A single SQL statement does not require a transaction on SQL Server due to default autocommit mode.
            // ...

            return Ok(product);
        }
    }
}

Answer 5

I'd like to share my solution. I was experimenting with UnitOfWork implementation for multiple ORMs, including Dapper. Here's full project: https://github.com/pkirilin/UnitOfWorkExample

Base unit of work and repository abstractions:

public interface IUnitOfWork
{
    Task SaveChangesAsync(CancellationToken cancellationToken);
}

public interface IRepository<TEntity, in TId> where TEntity : EntityBase<TId> where TId : IComparable<TId>
{
    Task<TEntity> GetByIdAsync(TId id, CancellationToken cancellationToken);
    
    TEntity Add(TEntity entity);

    void Update(TEntity entity);

    void Remove(TEntity entity);
}

Domain model:

public abstract class EntityBase<TId> where TId : IComparable<TId>
{
    public TId Id { get; }

    protected EntityBase()
    {
    }

    protected EntityBase(TId id)
    {
        Id = id;
    }
}

public class WeatherForecast : EntityBase<int>
{
    // ...
}

Specific repository interface:

public interface IWeatherForecastsRepository : IRepository<WeatherForecast, int>
{
    Task<List<WeatherForecast>> GetForecastsAsync(CancellationToken cancellationToken);
}

Specific unit of work interface:

public interface IAppUnitOfWork : IUnitOfWork
{
    IWeatherForecastsRepository WeatherForecasts { get; }
}

You can have multiple data contexts in your application, so creating specific unit of works with strong boundary seems reasonable to me.

The implementation of unit of work will look like this:

internal class AppUnitOfWork : IAppUnitOfWork, IDisposable
{
    private readonly IDbConnection _connection;
    private IDbTransaction _transaction;
    
    public IWeatherForecastsRepository WeatherForecasts { get; private set; }

    // Example for using in ASP.NET Core
    // IAppUnitOfWork should be registered as scoped in DI container
    public AppUnitOfWork(IConfiguration configuration)
    {
        // I was using MySql in my project, the connection will be different for different DBMS
        _connection = new MySqlConnection(configuration["ConnectionStrings:MySql"]);
        _connection.Open();
        _transaction = _connection.BeginTransaction();
        WeatherForecasts = new WeatherForecastsRepository(_connection, _transaction);
    }
    
    public Task SaveChangesAsync(CancellationToken cancellationToken)
    {
        try
        {
            _transaction.Commit();
        }
        catch
        {
            _transaction.Rollback();
            throw;
        }
        finally
        {
            _transaction.Dispose();
            _transaction = _connection.BeginTransaction();
            WeatherForecasts = new WeatherForecastsRepository(_connection, _transaction);
        }
        
        return Task.CompletedTask;
    }

    public void Dispose()
    {
        _transaction.Dispose();
        _connection.Dispose();
    }
}

Quite simple. But when I tried to implement specific repository interface, I faced a problem. My domain model was rich (no public setters, some properties were wrapped in value objects etc.). Dapper is unable to handle such classes as-is. It doesn't know how to map value objects to db columns and when you try to select some value from db, it throws error and says it can't instantiate entity object. One option is to create private constructor with parameters matching your db column names and types, but it's very bad decision, because your domain layer shouldn't know anything about your database.

So I've splitted entities into different types:

• Domain entity: contains your domain logic, is used by other parts of application. You can use everything you want here, including private setters and value objects
• Persistent entity: contains all properties matching your database columns, is used only in repository implementation. All properties are public

The idea is that repository works with Dapper only via persistent entity and, when nessesary, maps persistent entity to or from domain entity.

There is also an official library called Dapper.Contrib, which can construct basic (CRUD) SQL queries for you, and I'm using it in my implementation, because it really makes life easier.

So, my final repository implementation:

// Dapper.Contrib annotations for SQL query generation
[Table("WeatherForecasts")]
public class WeatherForecastPersistentEntity
{
    [Key]
    public int Id { get; set; }

    public DateTime Date { get; set; }

    public int TemperatureC { get; set; }

    public string? Summary { get; set; }
}

internal abstract class Repository<TDomainEntity, TPersistentEntity, TId> : IRepository<TDomainEntity, TId>
    where TDomainEntity : EntityBase<TId>
    where TPersistentEntity : class
    where TId : IComparable<TId>
{
    protected readonly IDbConnection Connection;
    protected readonly IDbTransaction Transaction;

    // Helper that looks for [Table(...)] annotation in persistent entity and gets table name to use it in custom SQL queries
    protected static readonly string TableName = ReflectionHelper.GetTableName<TPersistentEntity>();

    protected Repository(IDbConnection connection, IDbTransaction transaction)
    {
        Connection = connection;
        Transaction = transaction;
    }
    
    public async Task<TDomainEntity> GetByIdAsync(TId id, CancellationToken cancellationToken)
    {
        var persistentEntity = await Connection.GetAsync<TPersistentEntity>(id, transaction: Transaction);
        return (persistentEntity == null ? null : MapToDomainEntity(persistentEntity))!;
    }

    public TDomainEntity Add(TDomainEntity entity)
    {
        var persistentEntity = MapToPersistentEntity(entity);
        Connection.Insert(persistentEntity, transaction: Transaction);
        var id = Connection.ExecuteScalar<TId>("select LAST_INSERT_ID()", transaction: Transaction);
        SetPersistentEntityId(persistentEntity, id);
        return MapToDomainEntity(persistentEntity);
    }

    public void Update(TDomainEntity entity)
    {
        var persistentEntity = MapToPersistentEntity(entity);
        Connection.Update(persistentEntity, transaction: Transaction);
    }

    public void Remove(TDomainEntity entity)
    {
        var persistentEntity = MapToPersistentEntity(entity);
        Connection.Delete(persistentEntity, transaction: Transaction);
    }

    protected abstract TPersistentEntity MapToPersistentEntity(TDomainEntity entity);
    
    protected abstract TDomainEntity MapToDomainEntity(TPersistentEntity entity);

    protected abstract void SetPersistentEntityId(TPersistentEntity entity, TId id);
}

internal class WeatherForecastsRepository : Repository<WeatherForecast, WeatherForecastPersistentEntity, int>, IWeatherForecastsRepository
{
    public WeatherForecastsRepository(IDbConnection connection, IDbTransaction transaction)
        : base(connection, transaction)
    {
    }

    public async Task<List<WeatherForecast>> GetForecastsAsync(CancellationToken cancellationToken)
    {
        var cmd = new CommandDefinition($"select * from {TableName} limit 100",
            transaction: Transaction,
            cancellationToken: cancellationToken);

        var forecasts = await Connection.QueryAsync<WeatherForecastPersistentEntity>(cmd);

        return forecasts
            .Select(MapToDomainEntity)
            .ToList();
    }

    protected override WeatherForecastPersistentEntity MapToPersistentEntity(WeatherForecast entity)
    {
        return new WeatherForecastPersistentEntity
        {
            Id = entity.Id,
            Date = entity.Date,
            Summary = entity.Summary.Text,
            TemperatureC = entity.TemperatureC
        };
    }

    protected override WeatherForecast MapToDomainEntity(WeatherForecastPersistentEntity entity)
    {
        return new WeatherForecast(entity.Id)
            .SetDate(entity.Date)
            .SetSummary(entity.Summary)
            .SetCelciusTemperature(entity.TemperatureC);
    }

    protected override void SetPersistentEntityId(WeatherForecastPersistentEntity entity, int id)
    {
        entity.Id = id;
    }
}

internal static class ReflectionHelper
{
    public static string GetTableName<TPersistentEntity>()
    {
        var persistentEntityType = typeof(TPersistentEntity);
        var tableAttributeType = typeof(TableAttribute);
        var tableAttribute = persistentEntityType.CustomAttributes
            .FirstOrDefault(a => a.AttributeType == tableAttributeType);

        if (tableAttribute == null)
        {
            throw new InvalidOperationException(
                $"Could not find attribute '{tableAttributeType.FullName}' " +
                $"with table name for entity type '{persistentEntityType.FullName}'. " +
                "Table attribute is required for all entity types");
        }

        return tableAttribute.ConstructorArguments
            .First()
            .Value
            .ToString();
    }
}

Example usage:

class SomeService
{
    private readonly IAppUnitOfWork _unitOfWork;

    public SomeService(IAppUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    public async Task DoSomethingAsync(CancellationToken cancellationToken)
    {
        var entity = await _unitOfWork.WeatherForecasts.GetByIdAsync(..., cancellationToken);
        _unitOfWork.WeatherForecasts.Delete(entity);

        var newEntity = new WeatherForecast(...);
        _unitOfWork.WeatherForecasts.Add(newEntity);

        await _unitOfWork.SaveChangesAsync(cancellationToken);
    }
}

Answer 6

I noticed in your github repo you removed the UnitOfWorkFactory and instead instantiate it when accessing the Connection

Problem with this approach that i can't wrap my head around is.

Imagine the following scenario, if i register DBContext as Scoped and Repositories as Transient

1. UserService CreateUserProfile
    a. UserRepositoryGetByEmail("some@email.com")
    b. UserRepository.Add(user)
    c. AddressRepository.Add(new address)
2. UserService Commit?

In this case all of (1.) above is a single transaction and then I want to commit in (2.)

With a large business layer with multiple services using the same scoped instance of dbcontext I can see transactions overlapping

Now I could set dbcontext as Transient but then UnitOfWork would be different on each injection and it wouldn't work.

Answer 7

I've created a simple unit of work implementation on top of Dapper, with some basic CQS in mind. https://github.com/giangcoi48k/Dapper.CQS. Please take a look and see if it can apply to your project.

Use IUnitOfWork to execute the corresponding Query or Command, defined SQL query, or Stored Procedure name in that Query or Command.

For example, here is a simple controller:

namespace Dapper.CQS.Example.Controllers
{
    [ApiController]
    [Route("[controller]/[action]")]
    public class PropertyController : ControllerBase
    {
        private readonly IUnitOfWork _unitOfWork;

        public PropertyController(IUnitOfWork unitOfWork)
        {
            _unitOfWork = unitOfWork;
        }

        [HttpGet]
        public async Task<ActionResult<Property>> GetById([FromQuery] int id)
        {
            var property = await _unitOfWork.QueryAsync(new PropertyGetByIdQuery(id));
            return property == null ? NoContent() : Ok(property);
        }

        [HttpGet]
        public async Task<ActionResult<List<Property>>> Filter([FromQuery] string? name)
        {
            var properties = await _unitOfWork.QueryAsync(new PropertyFilterQuery(name));
            return Ok(properties);
        }

        [HttpGet]
        public async Task<ActionResult<PagedList<Property>>> PagedFilter([FromQuery] string? name, int page = 1, int pageSize = 5)
        {
            var properties = await _unitOfWork.QueryAsync(new PropertyPagedFilterQuery(name, page, pageSize));
            return Ok(properties);
        }

        [HttpPost]
        public async Task<ActionResult<Property>> Create([FromBody] Property property)
        {
            var createdId = await _unitOfWork.ExecuteAsync(new PropertyCreateCommand(property));
            await _unitOfWork.CommitAsync();
            property.Id = createdId;
            return Ok(property);
        }

        [HttpDelete]
        public async Task<ActionResult> Delete([FromQuery] int id)
        {
            await _unitOfWork.ExecuteAsync(new PropertyDeleteCommand(id));
            await _unitOfWork.CommitAsync();
            return Ok();
        }
    }
}

And here is a Query:

namespace Dapper.CQS.Example.CommandQueries
{
    public class PropertyPagedFilterQuery : QueryPagedBase<Property>
    {
        [Parameter]
        public string? Name { get; set; }
        protected override CommandType CommandType => CommandType.Text;
        protected override string Procedure => @"
SELECT *, COUNT(*) OVER() [COUNT] 
FROM Properties WHERE Name = @Name OR @Name IS NULL
ORDER BY [Name]
OFFSET (@page -1 ) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY
";

        public PropertyPagedFilterQuery(string? name, int page, int pageSize)
        {
            Name = name;
            Page = page;
            PageSize = pageSize;
        }
    }
}

QueryBase will use Dapper

public abstract class QueryPagedBase<T> : CommandQuery, IQuery<PagedList<T>>, IQueryAsync<PagedList<T>>
    {
        [Parameter]
        public int Page { get; set; }

        [Parameter]
        public int PageSize { get; set; }

        protected virtual string FieldCount => "COUNT";

        public virtual PagedList<T> Query(IDbConnection connection, IDbTransaction? transaction)
        {
            var result = connection.Query<T, int, (T Item, int Count)>(Procedure, (a, b) => (a, b), GetParams(), transaction, commandType: CommandType, splitOn: FieldCount);
            return ToPagedList(result);
        }

        public virtual async Task<PagedList<T>?> QueryAsync(IDbConnection connection, IDbTransaction? transaction, CancellationToken cancellationToken = default)
        {
            var result = await connection.QueryAsync<T, int, (T Item, int Count)>(Procedure, (a, b) => (a, b), GetParams(), transaction, commandType: CommandType, splitOn: FieldCount);
            return ToPagedList(result!);
        }

        private PagedList<T> ToPagedList(IEnumerable<(T Item, int Count)> result)
        {
            return new PagedList<T>
            {
                PageSize = PageSize,
                Page = Page,
                TotalRecords = result.Select(t => t.Count).FirstOrDefault(),
                Items = result.Select(t => t.Item).ToList()
            };
        }
    }