Implementing a generic repository with table-agnostic methods

Question

I'm working on refactoring a persistence layer to use a true generic repository, and want to minimise the number of similar queries being executed on different tables - think things like get by id from table a, b, or c, where the query only differs by the table.

My repository so far looks like this:

public interface IRepository<T>
{
    void Insert(T entity);
    void Update(T entity);
}

public class BaseRepository<TEntity> : IRepository<TEntity> where TEntity : class
{
    /// ctor stuff omitted ...

    public void Insert(TEntity entity)
    {
        _db.Insert<TEntity>(entity);
    }

    public void Update(TEntity entity)
    {
        _db.Update<TEntity>(entity);
    }
}

public interface IDerivedRepository : IRepository<MyEntity>
{
    // defines interface methods not found on the base IRepository
}

public class DerivedRepository : BaseRepository<MyEntity>, IDerivedRepository
{
    // implements methods defined on IDerivedRepository, and inherits Insert and Update from BaseRepository
}

This works nicely in that any new repository can inherit the methods defined on the base repo, which are type agnostic in that I can simply send an entity and my ORM (NPoco) manages the insert/update.

I want to extend that to allow generic base definitions for simple get/fetch type methods - get by id or a simple count being obvious examples. At the moment, I implement these in the appropriate repository so end up with multiple repository methods (in separate repositories) calling essentially the same code.

Example below is simplified (_db manages scope etc) but highlights what I'm trying to avoid - the repeated GetById methods where the table and return type differ

public class DerivedRepositoryA : BaseRepository<A>, IDerivedARepository
{
    public A GetById(int id) {
        return _db.Fetch<A>("select * from TableA where id = @0", id);
    }
}

public class DerivedRepositoryB : BaseRepository<B>, IDerivedBRepository
{
    public B GetById(int id) {
        return _db.Fetch<B>("select * from TableB where id = @0", id);
    }
}

public class DerivedRepositoryC : BaseRepository<C>, IDerivedCRepository
{
    public C GetById(int id) {
        return _db.Fetch<C>("select * from TableC where id = @0", id);
    }
}

Is it possible, and how might I go about it?

Answer 1

The below BaseRepository<TEntity> implementation uses the type name as the table name by default, but allows for a custom table name which differs from the type name if required.

public class BaseRepository<TEntity> : IRepository<TEntity> where TEntity : class
{
    private readonly string tableName;

    public BaseRepository() : this(typeof(TEntity).Name)
    {

    }

    public BaseRepository(string tableName)
    {
        this.tableName = tableName;
    }

    public TEntity GetById(int id)
    {
        return _db.Fetch<TEntity>($"select * from Table{tableName} where id = {id}");
    }
}

Answer 2

You don't need the table name, this would work

    return _db.Single<TEntity>("where id = @id", id);  //Or Fetch

You could do something like this and let NPoco handle the SQL. You can also use this for a Save< T >() or Delete < T >() also

    public T GetByID<T>(Int32 ID)
    {
        try
        {
            if (ID == 0)
                throw (new ArgumentNullException("ID cannot be 0"));

            return _db.SingleOrDefaultById<T>(ID);
        }
        catch { throw; }
    }