Have a base entity class with CRUD implementation and a primary key ID that can be changed by the classes implementing it

Question

In order to not rewrite basic CRUD for each entity I want to implement a base entity class with the base CRUD operations that can be implemented by any other entity.

The problem is I'm using Dapper for mapping with Dapper.Contrib and my database tables primary keys are never named Id - I can't use the Dapper mapper then.

I can't figure out a way to have a base class for a simple CRUD for each entity.

Instead of putting those methods in POCO, if you agree for Repository, have a look at `BaseRepository` class and `SetDbId` method [here](https://stackoverflow.com/a/45460483/5779732). That is implemented with Dapper Extensions and you are using Contrib; but that should not change much. — Amit Joshi, Feb 10 '20 at 14:43

score 1 · Answer 1 · answered Feb 10 '20 at 12:00

There is a very nice implementation without contrib of the generic repository pattern here: https://itnext.io/generic-repository-pattern-using-dapper-bd48d9cd7ead

Effectively you create the insert like this:

 public async Task InsertAsync(T t)
        {
            var insertQuery = GenerateInsertQuery();

            using (var connection = CreateConnection())
            {
                await connection.ExecuteAsync(insertQuery, t);
            }
        }

private string GenerateInsertQuery()
        {
            var insertQuery = new StringBuilder($"INSERT INTO {_tableName} ");

            insertQuery.Append("(");

            var properties = GenerateListOfProperties(GetProperties);
            properties.ForEach(prop => { insertQuery.Append($"[{prop}],"); });

            insertQuery
                .Remove(insertQuery.Length - 1, 1)
                .Append(") VALUES (");

            properties.ForEach(prop => { insertQuery.Append($"@{prop},"); });

            insertQuery
                .Remove(insertQuery.Length - 1, 1)
                .Append(")");

            return insertQuery.ToString();
        }

Woah, okay - this is another way to solve my problem that I couldn't find a solution to before which brought me to dapper.contrib to avoid writing a lot of SQL. Thank you, I will consider this approach as well. — StefanJM, Feb 10 '20 at 12:13
Indeed, while contrib really helps, this could also work too! — Athanasios Kataras, Feb 10 '20 at 12:15

StefanJM · Answer 2 · 2020-02-25T21:23:13.807

Even though @Athanasios Kataras answer works I still went with using the Dapper.contrib extension because it's more clear in my opinion.

I went the route of using Dapper.contrib and creating a Base Repository class which then was used by every other repository. Primary Keys (IDs) were handled by using data annotations that Dapper provides on the entities ([Key], [ExplicitKey]).

Example of some repository class implementing the Base Repository:

public class ACRepository : BaseRepository<ACEntity>, IACRepository
{
    private readonly IDbConnection _dbConnection;

    public ACRepository(IDbConnection _dbconn) : base(_dbconn)
    {
        _dbConnection = _dbconn;
    }

    // Some other methods that require more complicated queries
}

Base Repository class:

using Dapper.Contrib.Extensions;

public class BaseRepository<T> : IBaseRepository<T> where T : class
{
    private readonly IDbConnection _dbConnection;

    public BaseRepository(IDbConnection _dbconn)
    {
        _dbConnection = _dbconn;
    }

    public async Task Add(T entity)
    {
        var result = await _dbConnection.InsertAsync(entity);
    }

    public async Task<bool> Delete(T entity)
    {
        return await _dbConnection.DeleteAsync(entity);
    }

    public async Task<bool> Update(T entity)
    {
        return await _dbConnection.UpdateAsync(entity);
    }

    public async Task<T> GetById(object id)
    {
        return await _dbConnection.GetAsync<T>(id);
    }

    public async Task<IEnumerable<T>> GetAll()
    {
        return await _dbConnection.GetAllAsync<T>();
    }
}

An Entity:

using Dapper.Contrib.Extensions;

[Table("table_name")]
public class SquawkRegisterPMEEntity
{
    [ExplicitKey]
    public string SomeKey { get; set; }
    [Key]
    public int SomeOtherKey{ get; set; }
    public string SomeProperty { get; set; }
    public string SomeProperty1 { get; set; }
}

[Key] is used if the value is generated by the database.

[ExplicitKey] is used when the Id is specified manually.

Have a base entity class with CRUD implementation and a primary key ID that can be changed by the classes implementing it

2 Answers2