.NET Core and Dapper data access layer?

Question

ASP.NET

I am developing a REST API using ASP.NET Core. Data from the API will mainly be received, recording will occur much less frequently.
Getting data should be functional enough: it should be possible to filter, sort data for many fields, get only the required fields, etc. I am using Dapper as ORM.
Previously, I used the repository pattern and the Entity Framework.
My code looked like this:

public class BaseRepository : IDisposable
{
    public string ConnectionString { get; set; }
    protected BaseRepository()
    {
    }

    protected BaseRepository(string connectionString)
    {
        ConnectionString = connectionString;
    }

    protected string GetConnectionStringValue(string connectionStringName) { ... }
}

public abstract class EntityBaseRepository : BaseRepository
{
    private DbContext _context;
    protected EntityBaseRepository(): base("connectionString")
    {
    }

    protected DbContext Context => _context ?? (_context = CreateEntityContext(GetConnectionStringValue(ConnectionString)));
    protected abstract DbContext CreateEntityContext(string connectionString);
    protected virtual void SaveEntity<T>(T entity) where T : Entity { ... }
    protected virtual void DeleteEntity<T>(T entity) where T : Entity{ ... }
}

public abstract class EntityBaseRepository<TEntity, TSearchOptions, TLoadOptions> : EntityBaseRepository
where TEntity : Entity
where TSearchOptions : SearchOptions
where TLoadOptions : LoadOptions
{
    protected IEnumerable<TEntity> GetEntities(TSearchOptions searchOptions, TLoadOptions loadOptions) { ... }
    protected async Task<IEnumerable<TEntity>> GetEntitiesAsync(TSearchOptions searchOptions, TLoadOptions loadOptions) { ... }
    protected int GetCount(TSearchOptions searchOptions) { ... }
    protected TEntity GetEntity(long id, TLoadOptions loadOptions) { ... }
    protected virtual IQueryable<TEntity> ApplySearchOptions(DbContext context, IQueryable<TEntity> entities, TSearchOptions searchOptions) { ... }
    protected virtual IQueryable<TEntity> ApplyLoadOptions(DbContext context, IQueryable<TEntity> entities, TLoadOptions loadOptions) { ... }
}

Load options were applied in the ApplyLoadOptions method (eg number of records to get, offset, sorting, etc.).
ApplySearchOptions applied the search options.
For each entity, I implemented a separate repository that was inherited from EntityBaseRepository, as well as model-specific search and load options.
There were no problems with this approach when using EF. But when switching to Dapper, I encountered the fact that constructing queries with this approach is quite problematic. In order to simplify this task, I wrote a SQL query builder (something like https://laravel.com/docs/5.4/queries) and an entity mapper (something like the EF Fluent API). But I still have one more problem, the solution for which I have not yet been able to find.
The base search options class looks like this:

public class SearchOptions
{
    public List<object> IDs { get; set; }
}

public class SearchOptions<TKey> : SearchOptions
{
    public new List<TKey> IDs { get; set; }
}

Using the classes I wrote, I apply the search options like this:

protected virtual Query ApplySearchOptions(Query query, TSearchOptions searchOptions)
{
    if (searchOptions.IDs.Any())
        query.WhereIn(FluentMapper.KeyColumnName<TEntity>(), searchOptions.IDs);

    return query;
}

The problem is related to alias. If somewhere I need to define an Alias ​​for the table, then I will catch an exception.
I thought for a long time about how best to implement the project. Yesterday I came across CQRS. I found this approach very interesting. But I don’t know how best to implement this approach in my project (I don’t need EventSource).
Now everything is completely broken in my head. I can't think of a way to make a project and it annoys me wildly. Can't think of a good architectural approach.
How do you implement such projects? What approaches do you use?

Answer 1

Why is the choice of Dapper ORM justified ?
If you really need the speed with which the dapper maps, but you are ready to sacrifice the speed of writing code, then do this:
1. Implement SqlBuilder for all needs:

public interface ISqlBuilder
    {
        SqlBuilder.Template AddTemplate(string sql, dynamic parameters = null);
        ISqlBuilder LeftJoin(string sql, dynamic parameters = null);
        ISqlBuilder PagingLeftJoin(string sql, dynamic parameters = null);
        ISqlBuilder Where(string sql, dynamic parameters = null);
        ISqlBuilder PagingWhere(string sql, dynamic parameters = null);
        ISqlBuilder OrderBy(string sql, dynamic parameters = null);
        ISqlBuilder PagingOrderBy(string sql);
        ISqlBuilder Select(string sql, dynamic parameters = null);
        ISqlBuilder AddParameters(dynamic parameters);
        ISqlBuilder Join(string sql, dynamic parameters = null);
        ISqlBuilder GroupBy(string sql, dynamic parameters = null);
        ISqlBuilder Having(string sql, dynamic parameters = null);
    }

2. For each query construction, we write our own class, implement the IQueryBuilder interface for further dependency injection in services:

using Dapper;

    public class MyEntityQueryBuilder : IQueryBuilder<T>
        where T: IEntity
    {
        private readonly ISqlBuilder _sqlBuilder;
        private readonly SqlBuilder.Template _entityTemplate;
        private readonly string entity_template = @"
               select 
                     /**select**/ 
               from 
                     entity_table e 
               /**leftjoin**/
               /**where**/ 
               /**orderby**/
        ";
        private readonly string split_on = "Id";

        public MyEntityQueryBuilder(ISqlBuilder sqlBuilder)
        {
            _sqlBuilder = sqlBuilder.AddTemplate(entity_template);
        }

        public Query Build(EntityFilter filter)
        {
            Select();
            LeftJoin();
            Where(filter);
            Order();
            return new Query
            {
                Sql = _entityTemplate.RawSql,
                Parameters = _entityTemplate.Parameters,
                SplitOn = split_on,
            };
        }

        private void Select()
        {
            _builder.Select("e.Id");
            _builder.Select("e.Title");
        }

        private void LeftJoin()
        {
            _builder.LeftJoin("asn_Responsibles r1 on r1.Id = e.MainResponsibleId");
            _builder.LeftJoin("asn_EventResponsibles er on er.EventId = e.Id");
        }

        private void Where(EntityFilter filter)
        {
            _builder.Where("e.IsDeleted = 0");
            if (filter == null)
            {
                return;
            }
            if (filter.Id.HasValue)
            {
                _builder.Where("e.Id = @id", new { id = filter.Id });
            }
            if (filter.IsRootOnly)
            {
                _builder.Where("e.ParentId is null");
            }
        }

        private void Order()
        {
            _builder.OrderBy("e.CreateDate");
        }
    }

3. Need an abstract repository? Implement IRepository and inject your freshly baked QueryBuilders there for further mapping by dapper.
Good luck (: