protected virtual SqlQueryAndBinds ApplyPaging(SqlQueryAndBinds source, FilterPagingDto paging)
{
paging ??= new FilterPagingDto {
PageSize = _appSettingsConfiguration.DefaultPageSize
};
source.SqlQuery += " LIMIT @Limit OFFSET @Offset ";
source.Binds.Limit = paging.PageSize;
source.Binds.Offset = (paging.PageNumber - 1) * paging.PageSize;
return(source);
}
protected SqlQueryAndBinds ApplySortField(SqlQueryAndBinds source, FilterSortDto sort)
{
if (sort.IsDescending)
{
source.SqlQuery += $@" ORDER BY {sort.ColumnName} Desc";
}
else
{
source.SqlQuery += $@" ORDER BY {sort.ColumnName} ASC";
}
return(source);
}
protected virtual SqlQueryAndBinds ApplySort(SqlQueryAndBinds source, FilterSortDto sort)
{
sort ??= new FilterSortDto
{
ColumnName = nameof(BaseModel.DateCreated),
IsDescending = true
};
return(sort.ColumnName switch
{
nameof(BaseModel.DateCreated) => ApplySortField(source, sort),
nameof(BaseModel.IsActive) => ApplySortField(source, sort),
nameof(BaseModel.DateDelete) => ApplySortField(source, sort),
_ => source
});
/// <summary>
/// Get filtered entities
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public virtual async Task <IEnumerable <TModel> > GetFiltered(BaseFilterDto filter)
{
var result = new SqlQueryAndBinds()
{
SqlQuery = GetBaseSelectQuery(),
Binds = new ExpandoObject()
};
result = ApplyFilter(result, filter);
result = ApplySort(result, filter.Sort);
result = ApplyPaging(result, filter.Paging);
using IDbConnection db = new NpgsqlConnection(ConnectionString);
return(await db.QueryAsync <TModel>(result.SqlQuery, (object)result.Binds));
}
