How to Implement Generic Repository Design Pattern with Dapper

How to implement Generic Repository Design Pattern with Dapper?

Sure, a function to create and dispose your Connection will work great.

protected void Execute(Action<IDbConnection> query)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
    {
        query.Invoke(db);
    }
}

And your simplified call site:

public void SaveCustomer(CustomerDTO custDTO)
{
    Execute(db => db.Execute(saveCustSp, custDTO, CommandType.StoredProcedure));
}

---

With Return Values:

public T Get<T>(Func<IDbConnection, T> query)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDB"].ConnectionString))
    {
        return query.Invoke(db); 
    }
}

In your call site, just write the logic you wish to use.

public IEnumerable<EmployeeDTO> GetEmployeeDetails(int employeeId)
{
    return Get<IEnumerable<EmployeeDTO>(db => 
        db.Query<EmployeeDTO>(anotherSp, new { EmployeeID = employeeId }, CommandType.StoredProcedure));
}

Generic Repository pattern for .net core with Dapper

The examples by @PathumLakshan request from comments. Provided examples are written in asynchronous manner, but of source can be implemented synchronous. Anyway it is just an illustration of how you can manage instrastructure with Dapper. Class Db provides some generic methods for getting data and executing SQL queries. For instance you can use overload Get<T>(string, object) for basic queries, or take Get<T>(Func<SqlConnection, SqlTransaction, int, Task<T>> to use let say QueryMultiple. Class Repository<Entity> shows, how can look basic repository for entity Entity.

Db class:

public class Db : IDb
{
    private readonly Func<SqlConnection> _dbConnectionFactory;

    public Db(Func<SqlConnection> dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory ?? throw new ArgumentNullException(nameof(dbConnectionFactory));
    }

    public async Task<T> CommandAsync<T>(Func<SqlConnection, SqlTransaction, int, Task<T>> command)
    {
        using (var connection = _dbConnectionFactory.Invoke())
        {
            await connection.OpenAsync();

            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var result = await command(connection, transaction, Constants.CommandTimeout);

                    transaction.Commit();

                    return result;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.Instance.Error(ex);
                    throw;
                }
            }
        }
    }

    public async Task<T> GetAsync<T>(Func<SqlConnection, SqlTransaction, int, Task<T>> command)
    {
        return await CommandAsync(command);
    }

    public async Task<IList<T>> SelectAsync<T>(Func<SqlConnection, SqlTransaction, int, Task<IList<T>>> command)
    {
        return await CommandAsync(command);
    }

    public async Task ExecuteAsync(string sql, object parameters)
    {
        await CommandAsync(async (conn, trn, timeout) =>
        {
            await conn.ExecuteAsync(sql, parameters, trn, timeout);
                return 1;
        });

    public async Task<T> GetAsync<T>(string sql, object parameters)
    {
        return await CommandAsync(async (conn, trn, timeout) =>
        {
            T result = await conn.QuerySingleAsync<T>(sql, parameters, trn, timeout);
            return result;
        });
    }

    public async Task<IList<T>> SelectAsync<T>(string sql, object parameters)
    {
        return await CommandAsync<IList<T>>(async (conn, trn, timeout) =>
        {
            var result = (await conn.QueryAsync<T>(sql, parameters, trn, timeout)).ToList();
            return result;
        });
    }
}

Repository class:

public class Repository<Entity> : IRepository<Entity>
{
    protected readonly IDb _db;

    public Repository(IDb db)
    {
        _db = db ?? throw new
            ArgumentException(nameof(db));
    }

    public async Task Add(Entity entity)
    {
        await _db.ExecuteAsync("INSERT INTO ... VALUES...", entity);
    }

    public async Task Update(Entity entity)
    {
        await _db.ExecuteAsync("UPDATE ... SET ...", entity);
    }

    public async Task Remove(Entity entity)
    {
        await _db.ExecuteAsync("DELETE FROM ... WHERE ...", entity);
    }

    public async Task<Entity> FindByID(int id)
    {
        return await _db.GetAsync<Entity>("SELECT ... FROM ... WHERE Id = @id", new { id });
    }

    public async Task<IEnumerable<Entity>> FindAll()
    {
        return await _db.SelectAsync<Entity>("SELECT ... FROM ... ", new { });
    }
}

Db can be extended with other generic method, for example, ExecuteScalar, which you would need in your repositories. Hope it helps.

How should I manage Generic Repository Pattern when the works of different entities are pretty much different?

How should I manage Generic Repository Pattern when the works of different entities are pretty much different?

This is the core problem with Generic Repository pattern; that is why it is considered an anti-pattern.

I read this here:

No matter what clever mechanism I tried, I always ended up at the same problem: a repository is a part of the domain being modeled, and that domain is not generic. Not every entity can be deleted, not every entity can be added, not every entity has a repository. Queries vary wildly; the repository API becomes as unique as the entity itself.

Why generic repository is anti-pattern?

1. A repository is a part of the domain being modeled, and that domain is not generic.
   
   
   • Not every entity can be deleted.
   • Not every entity can be added
   • Not every entity has a repository.
   • Queries vary wildly; the repository API becomes as unique as the entity itself.
   • For GetById(), identifier types may be different.
   • Updating specific fields (DML) not possible.
2. Generic query mechanism is the responsibility of an ORM.
   
   
   • Most of the ORMs expose an implementation that closely resemble with Generic Repository.
   • Repositories should be implementing the SPECIFIC queries for entities by using the generic query mechanism exposed by ORM.
3. Working with composite keys is not possible.
4. It leaks DAL logic in Services anyway.
   
   
   • Predicate criteria if you accept as parameter needs to be provided from Service layer. If this is ORM specific class, it leaks ORM into Services.

I suggest you read these (1, 2, 3, 4, 5) articles explaining why generic repository is an anit-pattern.

Better approach is:

1. Skip the Generic Repository. Implement concrete repositories.
2. Use Generic Repository as abstract base repository. Derive all concrete repositories from it.

In any case, do not expose generic repository to calling code. Also, do not expose IQueryable from concrete repositories.

Generic repository with Dapper

Define an interface like so.

public interface ITypeWithId {
    int Id {get;}
}

And make sure your User type implements that interface.

Now apply it to your class as a generic constraint.

public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class, ITypeWithId

---

If you have types that are stored in the repository but DO Not have an Id property then make your delete type constraint specific to the method and not the class. This will allow you to still use the same repository type even with types that might key on something else like a string or a compound (multi) key.

public void Delete<T>(T entity) where T : class, ITypeWithId
{
    using (IDbConnection cn = Connection)
    {

        cn.Open();
        cn.Execute("DELETE FROM " + _tableName + " WHERE Id=@ID", new { ID = entity.Id });
    }
}

When using a repository pattern with Dapper, is it necessary to open and close db connections?

Dapper will indeed open and close automatically if it detects a closed connection. So if you don't want to have to do that bit: it should still work fine. The main downside about that would be that you can't easily spare a transaction over a closed connection, so if you intend to use connection-level transactions it may be better to start with connection lifetime management built in too.

How to implement expressions in Generic Repository?

The repository pattern is already implemented by the DbSet<T> and consists in few operations over your entity to store and retrive him from an abstracted data store. Just your entity, it's very important on DDD.

But, I know that sometimes we need to put another layer of abstraction over this, to deal with another databases like nosql, for example.

In this case, usually we create a gereneric repository, and it's needed to supply a way to make operations based on what type this repository is. To accomplish this, we need to define a common interface for our entities with an Id and implement this on those entities:

public interface IEntity
{
    Guid Id (get; set;}
}

That way, constraining your generic repository to this type of interface provides you ability to access the Id on the methods.

public class GenericRepository<T> : IGenericRepository<T> where T : IEntity
{
private readonly NexusContext _context;
IMapper _mapper;
public GenericRepository(NexusContext context)
{
    _context = context;
}

public  GenericLookupDTO GetById(Guid id)
{
    var obj =  _context.Set<T>().FirstOrDefault(x => x.Id = id);
    var objMapped = AutoMapping.mapper.Map<GenericLookupDTO>(obj);
    return objMapped;
 }

}

I really recomend you to don't return DTOs from repository, if you need to aggregate data from many different entities that are not related, use a different layer of data access, very simplified, and create freely your own queries, using dapper or even EF but projecting directly DTOs.
If the DTO is identical of an entity, in this case use the repository to retrieve the entity and on application layer map this entity to a DTO.

When you have time, take a look at DDD principles to clarify a little bit more those subjects.

Back to your example, on the controller you will need to inject the right type of generic repository, like:

IGenericRepository<Customer> customerRepository

and configure your dependecy injection container to resolve generic types, like:

services.AddTransient<IGenericRepository<>, GenericRepository<>>();

The service will rely just on IGenericRepository<T> as you did.

But, if you want to query freely your entities, I recommend you make use of OData or GraphQl, that will provides you more control over queries.

I'm tried to be very simplistic here, so, I hope that i could clarify things a little bit more for you!

---

Related Topics