Generic repository with Dapper

Question

I'm trying to build a generic repository with Dapper. However, I have some difficulties to implement the CRUD-operations.

Here is some code from the repository:

 public class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class
{
    internal IDbConnection Connection
    {
        get
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["SoundyDB"].ConnectionString);
        }
    }

    public GenericRepository(string tableName)
    {
        _tableName = tableName;
    }

    public void Delete(TEntity entity)
    {
        using (IDbConnection cn = Connection)
        {

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

As you can see, my delete-method takes a TEntity as parameter which is a parameter of type class.

I call my Delete-method from my UserRepository like this:

public class UserRepository : GenericRepository<User>, IUserRepository
{
    private readonly IConnectionFactory _connectionFactory;

    public UserRepository(IConnectionFactory connectionFactory) : base("User")
    {
        _connectionFactory = connectionFactory;
    }

    public async Task<User> Delete(User model)
    {
        var result = await Delete(model);
        return result;
    }
}

The thing is that I can't write entity.Id in my Delete-opration in my generic repository. I get a error. So how can I easily implement CRUD-operations like this?

Here is the error message:

TEntity does not contain a definition of "Id" and no extension method "Id" accepting a argument of type "TEntity" could be found

Answer 1

In case it helps, I've just published a library Harbin.DataAccess which implements Generic Repositories (Generic Repository Pattern) using "raw" Dapper, Dapper.FastCRUD, and DapperQueryBuilder:

• The Inserts/Updates/Deletes are automatically generated by Dapper FastCRUD (class should be decorated with attributes for keys/autoincrement columns)
• Supports FastCRUD bulk update, bulk delete, and async methods.
• Repositories can be extended with custom Queries and custom Commands (allows/promotes CQRS separation)
• Queries can be defined manually (raw sql) or using Dapper FastCRUD syntax
• Dynamic Queries (dynamic number of conditions) can be built using DapperQueryBuilder
• There are Read-only Connection Wrappers and Read-only Repositories, so it's easy to use read-replicas (or multiple databases)
• Support for ADO.NET transactions
• Support for mocking Queries and Commands

Sample Insert/Update/Delete (Generic Repository - this uses Dapper FastCRUD):

var conn = new ReadWriteDbConnection(new System.Data.SqlClient.SqlConnection(connectionString));

// Get a IReadWriteRepository<TEntity> which offers some helpers to Query and Write our table:
var repo = conn.GetReadWriteRepository<ContactType>();

var contactType = repo.QueryAll().First();

// Updating a record
contactType.ModifiedDate = DateTime.Now;
repo.Update(contactType);

// Adding a new record
var newContactType = new ContactType() { Name = "NewType", ModifiedDate = DateTime.Now };
repo.Insert(newContactType);
// FastCRUD will automatically update the auto-generated columns back (identity or guid)

// Deleting a record
repo.Delete(newContactType);

[Table("ContactType", Schema = "Person")]
public class ContactType
{
    [Key] // if column is part of primary key
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] // if column is auto-increment
    public int ContactTypeId { get; set; }

    public DateTime ModifiedDate { get; set; }

    public string Name { get; set; }
}

Sample Dynamic Queries:

var conn = new ReadDbConnection(new System.Data.SqlClient.SqlConnection(connectionString));


// Get a IReadRepository<TEntity> which offers some helpers to Query our table:
var repo = conn.GetReadRepository<Person>();

// Custom Query (pure Dapper)
var people = repo.Query("SELECT * FROM Person.Person WHERE PersonType = @personType ", new { personType = "EM" } );

// DapperQueryBuilder allows to dynamically append conditions using string interpolation (but injection-safe)

string type = "EM"; string search = "%Sales%";

var dynamicQuery = repo.QueryBuilder(); // if not specified query is initialized with "SELECT * FROM tablename"
dynamicQuery.Where($"PersonType = {type}");
dynamicQuery.Where($"ModifiedDate >= {DateTime.Now.AddDays(-1)} ");
dynamicQuery.Where($"Name LIKE {search}");

// Result is SELECT * FROM [Person].[Person] WHERE PersonType = @p0 AND ModifiedDate >= @p1 AND Name LIKE @p2
var people = dynamicQuery.Query();

Extending Repositories (adding custom Queries and Commands) using Inheritance:

public class PersonRepository : ReadWriteDbRepository<Person>
{
  public PersonRepository(IReadWriteDbConnection db) : base(db)
  {
  }
  public virtual IEnumerable<Person> QueryRecentEmployees()
  {
    return this.Query("SELECT TOP 10 * FROM [Person].[Person] WHERE [PersonType]='EM' ORDER BY [ModifiedDate] DESC");
  }
  public virtual void UpdateCustomers()
  {
    this.Execute("UPDATE [Person].[Person] SET [FirstName]='Rick' WHERE [PersonType]='EM' ");
  }
}

public void Sample()
{
  // Registers that GetReadWriteRepository<Person>() should return a derived type PersonRepository
  ReadWriteDbConnection.RegisterRepositoryType<Person, PersonRepository>();

  var conn = new ReadWriteDbConnection(new System.Data.SqlClient.SqlConnection(connectionString));  
  
  // we know exactly what subtype to expect, so we can just cast.
  var repo = (PersonRepository) conn.GetReadWriteRepository<Person>();
  
  repo.UpdateCustomers();
  var recentEmployees = repo.QueryRecentEmployees();
}

Full documentation here.

Answer 2

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 });
    }
}

Answer 3

Please don't do this! Your generic repository adds more confusion than value. It's fragile code (string literals for _tableName, invalid cast errors on the id parameter), and introduces a gaping security hole (sql injection via _tableName). If you've chosen Dapper, it's because you want to be in control of your sql, so it makes no sense to generate the sql you send to Dapper.

Answer 4

you have to define an interface like below

public interface IIdentityEntity
{
  public int Id { get; set;}
}

all your entities which want to use the class, must implement the IIdentityEntity.

and the first line should be changed to the following

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

and what was the problem is that you only described the TEntity as class and class does not have an Id in its description so you have to notify compiler that the Generic type implemented an Interface that holds an Id field inside it