/// <summary>Attempts to bind a model.</summary>
/// <param name="bindingContext">The <see cref="T:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingContext" />.</param>
/// <returns>
/// <para>
/// A <see cref="T:System.Threading.Tasks.Task" /> which will complete when the model binding process completes.
/// </para>
/// <para>
/// If model binding was successful, the <see cref="P:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingContext.Result" /> should have
/// <see cref="P:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingResult.IsModelSet" /> set to <c>true</c>.
/// </para>
/// <para>
/// A model binder that completes successfully should set <see cref="P:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingContext.Result" /> to
/// a value returned from <see cref="M:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingResult.Success(System.Object)" />.
/// </para>
/// </returns>
public Task BindModelAsync(ModelBindingContext bindingContext)
{
if (bindingContext == null)
{
throw new ArgumentNullException(nameof(bindingContext));
}
if (bindingContext.ModelType != typeof(FilterParameters))
{
return(Task.CompletedTask);
}
// Try to fetch the value of the argument by name
var queryParameters = bindingContext.HttpContext.Request.Query;
if (!queryParameters.Any())
{
var par = new FilterParameters();
bindingContext.Result = ModelBindingResult.Success(par);
return(Task.CompletedTask);
}
FilterParameters model = ParseModel(queryParameters);
if (model == null)
{
return(Task.CompletedTask);
}
bindingContext.Result = ModelBindingResult.Success(model);
return(Task.CompletedTask);
}
private string CreatePagging(FilterParameters filterParameters)
{
var pageData = PageDataExtensions.GetPageData(filterParameters.PageSize, filterParameters.CurrentPage);
if (pageData.skip >= 0)
{
return($"OFFSET {pageData.skip} ROWS FETCH NEXT {pageData.take} ROWS ONLY");
}
return(string.Empty);
}
private string CreateOrderBy(FilterParameters filterParameters)
{
var orderColumn = _parameters.DefaultOrderColumn;
if (!string.IsNullOrWhiteSpace(filterParameters.OrderBy))
{
orderColumn = filterParameters.OrderBy;
}
if (!typeof(TFilterableEntity).PropertyExists(orderColumn)) //prevent sql injection
{
throw new Exception($"The property {orderColumn} should exists in {typeof(TFilterableEntity).Name}");
}
return($" ORDER BY {orderColumn} {filterParameters.OrderKind} ");
}
private FilterParameters ParseModel(IQueryCollection queryParameters)
{
var columnNames = queryParameters[FilterColumnName];
var groups = columnNames.GroupBy(s => s).Select(
s => new { Name = s.Key, Count = s.Count() });
var columns = groups.ToDictionary(arg => arg.Name, arg1 => arg1.Count);
var filter = new FilterParameters();
foreach (var name in columns.Keys)
{
var expectedCount = columns[name];
var valueParameter = string.Format(FilterColumnValue, name);
var values = new string[expectedCount];
if (queryParameters.ContainsKey(valueParameter))
{
var parameterValues = queryParameters[valueParameter];
values = parameterValues;
}
var operatorParameter = string.Format(FilterParameterOperator, name);
var comparisonOperators = ParseEnum(queryParameters, operatorParameter, expectedCount, FilterComparisonOperators.None);
var logicalParameter = string.Format(FilterParameterNext, name);
var logicalOperators = ParseEnum(queryParameters, logicalParameter, expectedCount, FilterLogicalOperators.None);
for (int i = 0; i < expectedCount; i++)
{
if (!string.IsNullOrWhiteSpace(values[i]) && comparisonOperators[i] != FilterComparisonOperators.None)
{
var filterStatement = new FilterStatement();
filterStatement.ParameterName = name;
filterStatement.ParameterValue = values[i];
filterStatement.ComparisonOperator = comparisonOperators[i];
filterStatement.LogicalOperator = logicalOperators[i];
filter.Statements.Add(filterStatement);
}
}
}
if (queryParameters.ContainsKey(PageSize))
{
if (int.TryParse(queryParameters[PageSize], NumberStyles.None, CultureInfo.InvariantCulture,
out var res))
{
filter.PageSize = res;
}
}
filter.PageSize = ParseInt(queryParameters, PageSize, -1);
filter.CurrentPage = ParseInt(queryParameters, CurrentPage, -1);
if (queryParameters.ContainsKey(OrderBy))
{
filter.OrderKind = ParseEnum(queryParameters, OrderKind, 1, Filterable.OrderKind.Asc).First();
filter.OrderBy = queryParameters[OrderBy];
}
return(filter);
}
private (string sql, object[] parameters) CreateWhere(FilterParameters filterParameters)
{
return(_filterStatementParser.Parse(filterParameters.Statements, _parameters));
}
public async Task <PagedResult <TFilterableEntity> > FilterAsync(DbContext context, IResourceMandatoryPredicateFactory <TEntity, TKey> mandatoryPredicateFactory, FilterParameters filterParameters)
{
var orderByClause = CreateOrderBy(filterParameters);
var paggingClause = CreatePagging(filterParameters);
var whereParameters = CreateWhere(filterParameters);
var sqlCount = _parameters.Sql
.Replace(OrderByClause, string.Empty, StringComparison.OrdinalIgnoreCase)
.Replace(PaggingClause, string.Empty, StringComparison.OrdinalIgnoreCase)
.Replace(WhereClause, whereParameters.sql, StringComparison.OrdinalIgnoreCase);
var sqlResult = _parameters.Sql
.Replace(OrderByClause, orderByClause, StringComparison.OrdinalIgnoreCase)
.Replace(PaggingClause, paggingClause, StringComparison.OrdinalIgnoreCase)
.Replace(WhereClause, whereParameters.sql, StringComparison.OrdinalIgnoreCase);
var count = await context.Set <TFilterableEntity>().FromSqlRaw(sqlCount, whereParameters.parameters).LongCountAsync();
var result = await context.Set <TFilterableEntity>().FromSqlRaw(sqlResult, whereParameters.parameters).ToArrayAsync();
return(new PagedResult <TFilterableEntity>(count, result));
}
public async Task <PagedResult <TFilterableEntity> > FilterAsync(DbContext context, IResourceMandatoryPredicateFactory <TEntity, TKey> mandatoryPredicateFactory, FilterParameters filterParameters)
{
var mandatoryPredicate = mandatoryPredicateFactory.GetMandatoryPredicates();
var predicate = LinqKit.PredicateBuilder.New <TEntity>(true);
foreach (var filter in filterParameters.Statements)
{
ISpecification <TEntity> specification = Create(filter);
if (specification == null)
{
continue;
}
if (filter.LogicalOperator == FilterLogicalOperators.Or)
{
predicate.Or(specification.IsSatisfiedBy());
}
else
{
predicate.And(specification.IsSatisfiedBy());
}
}
var count = await context.Set <TEntity>().AsExpandable().Where(mandatoryPredicate).Where(predicate).LongCountAsync();
var entityQuery = context.Set <TEntity>().AsQueryable();
if (_parameters.IncludeProperties != null)
{
foreach (var includedProperty in _parameters.IncludeProperties)
{
entityQuery = entityQuery.Include(includedProperty);
}
}
var query = entityQuery.AsExpandable().Where(mandatoryPredicate).Where(predicate);
if (!string.IsNullOrWhiteSpace(filterParameters.OrderBy))
{
var sortOrder = filterParameters.OrderKind == OrderKind.Asc? ListSortDirection.Ascending: ListSortDirection.Descending;
var orderByColumn = filterParameters.OrderBy.ToLower();
if (_parameters.SortFieldsMaping.ContainsKey(orderByColumn))
{
orderByColumn = _parameters.SortFieldsMaping[orderByColumn];
}
query = query.OrderBy(orderByColumn, sortOrder);
}
var pageData = PageDataExtensions.GetPageData(filterParameters.PageSize, filterParameters.CurrentPage);
if (pageData.skip >= 0)
{
query = query.Skip(pageData.skip).Take(pageData.take);
}
TFilterableEntity[] entities;
if (_parameters.DirectProject)
{
entities = await _mapper.ProjectTo <TFilterableEntity>(query).ToArrayAsync();
}
else
{
var queryEntities = await query.ToArrayAsync();
entities = _mapper.Map <TFilterableEntity[]>(queryEntities);
}
return(new PagedResult <TFilterableEntity>(count, entities));
}
