public async Task <GenericModel <AuthorDataModel> > GetFilteredAuthorsAsync(BaseFilterModel filter)
{
var authors = _context.Authors
.Where(x => x.IsRemoved == false)
.Where(x => x.AuthorInPrintingEditions.Select(z => z.AuthorId == x.Id).FirstOrDefault())
.Include(x => x.AuthorInPrintingEditions)
.ThenInclude(x => x.PrintingEdition);
Expression <Func <Author, object> > predicate = x => x.Id;
if (filter.SortType == Enums.SortType.Name)
{
predicate = x => x.Name;
}
var responseModel = new GenericModel <AuthorDataModel>
{
CollectionCount = authors.Count()
};
var authorsPage = await PaginationAsync(filter, predicate, authors);
var result = authorsPage.Select(x => new AuthorDataModel
{
Id = x.Id,
Name = x.Name,
PrintingEditionTitles = x.AuthorInPrintingEditions
.Select(z => z.PrintingEdition.Title).ToArray()
});
responseModel.Collection = result;
return(responseModel);
}
/// <summary>
/// </summary>
/// <param name="baseFilterModel"></param>
/// <param name="filterString"></param>
protected void AddPageSizeNumberAndSortingInFilterString(BaseFilterModel baseFilterModel,
ref string filterString)
{
if (baseFilterModel.PageSize > 0)
{
filterString += ODataFilterConstant.Inlinecount;
filterString += string.Format("{0}{1}", ODataFilterConstant.Top, baseFilterModel.PageSize);
}
if (baseFilterModel.PageNumber > 0)
{
filterString += string.Format("{0}{1}", ODataFilterConstant.Skip, (baseFilterModel.PageNumber - 1) * baseFilterModel.PageSize);
}
if (string.IsNullOrWhiteSpace(baseFilterModel.SortColumn))
{
return;
}
filterString += string.Format("{0}{1}", ODataFilterConstant.Orderby, baseFilterModel.SortColumn);
if (baseFilterModel.SortDirection.Contains("Desc"))
{
filterString += " desc";
}
else
{
filterString += " asc";
}
}
public async Task <ActionResult <GetTraderInfoDTO[]> > GetByCopyCountAsync([FromQuery] BaseFilterModel filter)
{
var entities = await _manager.GetByCopyCountAsync(filter);
var dtos = _mapper.Map <TraderInfoEntity[], GetTraderInfoDTO[]>(entities);
return(Ok(dtos));
}
public async Task <BaseResponse> ListFilter(BaseFilterModel baseFilterModel)
{
return(_baseResponse
.SetResult(await _service.ListFilterAsync(baseFilterModel))
.SetTotalCount(await _service.CountFilterAsync(baseFilterModel, includePaging: false))
.SetPage(MathExtensions.UpDivision(baseFilterModel.PagingBy.Skip + baseFilterModel.PagingBy.Take, baseFilterModel.PagingBy.Take))
.SetTotalPage(MathExtensions.UpDivision(_baseResponse.Result.TotalCount, baseFilterModel.PagingBy.Take)));
}
public Task <TraderInfoEntity[]> GetAllAsync(BaseFilterModel filter)
{
return(_entities
.AsNoTracking()
.Skip(filter.Skip)
.Take(filter.Take)
.ToArrayAsync());
}
public Task <TraderInfoEntity[]> GetByCopyCountAsync(BaseFilterModel filter)
{
return(_entities
.AsNoTracking()
.OrderByDescending(ti => ti.CopyCount)
.Skip(filter.Skip)
.Take(filter.Take)
.ToArrayAsync());
}
public async Task <TraderInfoEntity[]> GetByCopyCountAsync(BaseFilterModel filter)
{
var entities = await _repository.GetByCopyCountAsync(filter);
if (entities == null)
{
throw new NotFoundException(ErrorCode.TraderInfoNotFound);
}
return(entities);
}
public async Task <IActionResult> PostAllFiltered(BaseFilterModel filter)
{
var query = _dataContext.GroupOfIndicators.AsQueryable();
if (filter.TypeOfForm != null)
{
query = query.Where(i => i.TypeOfForm.Id == filter.TypeOfForm.Id);
}
return(Ok(await query.Select(m => new { m.Id, m.Name }).ToListAsync()));
}
private static IEnumerable <string> ToQuerySortString(this BaseFilterModel baseFilterModel)
{
for (int i = 0; i < baseFilterModel.SortBy.ColumnName.Length; i++)
{
switch (baseFilterModel.SortBy.Type[i])
{
case BaseFilterModel.OrderType.DESC:
yield return($"{baseFilterModel.SortBy.ColumnName[i]} descending");
break;
default:
yield return($"{baseFilterModel.SortBy.ColumnName[i]} ascending");
break;
}
}
}
protected async Task <IEnumerable <TModel> > PaginationAsync <TModel>(BaseFilterModel filter, Expression <Func <TModel, object> > predicate, IQueryable <TModel> entities)
{
if (filter.SortState.Equals(Enums.SortState.Asc))
{
entities = entities.OrderBy(predicate);
}
if (filter.SortState.Equals(Enums.SortState.Desc))
{
entities = entities.OrderByDescending(predicate);
}
var result = await entities
.Skip((filter.Page - 1) *filter.PageSize)
.Take(filter.PageSize)
.ToAsyncEnumerable()
.ToList();
return(result);
}
public async Task <EnglishTaskInfoModel> FindRandomInfoEnglishTaskAsync(BaseFilterModel baseFilterModel)
{
if (baseFilterModel == null || baseFilterModel.IsEmpty())
{
return(null);
}
var persistenceFilter = _mapper.Map <BaseFilter>(baseFilterModel);
IReadOnlyList <EnglishTaskInfo> englishTasks = await _taskRepository.FindAllInfoByFilters(persistenceFilter);
if (!englishTasks.Any())
{
return(null);
}
var englishTask = englishTasks.GetRandomElement();
var englishTaskModel = _mapper.Map <EnglishTaskInfoModel>(englishTask);
return(englishTaskModel);
}
protected string GetFilterStringForLookup(BaseFilterModel searchModel)
{
var filterString = string.Empty;
if (searchModel != null)
{
if (!string.IsNullOrWhiteSpace(searchModel.FilterText))
{
if (string.IsNullOrWhiteSpace(filterString))
{
filterString = ODataFilterConstant.Filter + $"contains(Name,'{searchModel.FilterText}') eq true";
}
else
{
filterString += $" or contains(Name,'{searchModel.FilterText}') eq true";
}
}
AddPageSizeNumberAndSortingInFilterString(searchModel, ref filterString);
}
return(filterString);
}
protected void AddPageSizeNumberAndSortingInFilterString(BaseFilterModel baseFilterModel, ref string filterString)
{
baseFilterModel.PageNumber = baseFilterModel.PageNumber > 0 ? baseFilterModel.PageNumber : 1;
baseFilterModel.PageSize = baseFilterModel.PageSize > 0 ? baseFilterModel.PageSize : 8;
filterString += ODataFilterConstant.Inlinecount;
filterString += $"{ODataFilterConstant.Top}{baseFilterModel.PageSize}";
filterString += $"{ODataFilterConstant.Skip}{(baseFilterModel.PageNumber - 1) * baseFilterModel.PageSize}";
if (string.IsNullOrWhiteSpace(baseFilterModel.SortColumn))
{
return;
}
filterString += $"{ODataFilterConstant.Orderby}{baseFilterModel.SortColumn}";
if (baseFilterModel.SortDirection != null && baseFilterModel.SortDirection.ToLower().Contains("desc"))
{
filterString += " desc";
}
else
{
filterString += " asc";
}
}
public async Task <GenericModel <AuthorDataModel> > GetAllAuthorsAsync(BaseFilterModel filter)
{
var responseModel = new GenericModel <AuthorDataModel>();
var mainSql = new StringBuilder($@"SELECT a.Id, a.Name FROM Authors AS a
WHERE a.IsRemoved = 0
ORDER BY a.Name
OFFSET {(filter.Page - 1) * filter.PageSize} ROWS FETCH NEXT {filter.PageSize} ROWS ONLY
SELECT COUNT(a.Id) FROM Authors AS a
WHERE a.IsRemoved = 0");
using (var connection = GetSqlConnection())
{
var result = await connection.QueryMultipleAsync(mainSql.ToString());
responseModel.Collection = await result.ReadAsync <AuthorDataModel>();
responseModel.CollectionCount = (await result.ReadAsync <int>()).FirstOrDefault();
}
return(responseModel);
}
public async Task <GenericModel <AuthorDataModel> > GetAllAuthorsAsync(BaseFilterModel filter)
{
var authors = _context.Authors
.AsNoTracking()
.Where(x => x.IsRemoved == false)
.Select(x => new AuthorDataModel()
{
Id = x.Id,
Name = x.Name
});
var responseModel = new GenericModel <AuthorDataModel>()
{
CollectionCount = await authors.CountAsync()
};
var authorsPage = await PaginationAsync(filter, x => x.Name, authors);
responseModel.Collection = authorsPage;
return(responseModel);
}
public async Task <IReadOnlyList <EnglishTaskInfoModel> > FindAllInfoEnglishTaskAsync(BaseFilterModel baseFilterModel)
{
if (baseFilterModel == null || baseFilterModel.IsEmpty())
{
return(Array.Empty <EnglishTaskInfoModel>());
}
var persistenceFilter = _mapper.Map <BaseFilter>(baseFilterModel);
var englishTasks = await _taskRepository.FindAllInfoByFilters(persistenceFilter);
var englishTaskModels = _mapper.Map <IReadOnlyList <EnglishTaskInfoModel> >(englishTasks);
return(englishTaskModels);
}
public async Task <int> CountFilterAsync(BaseFilterModel baseFilterModel, bool includeFilters = true, bool includePaging = true)
{
return(await _baseRepository.CountFilterAsync(baseFilterModel, includeFilters, includePaging));
}
public async Task <int> CountFilterAsync(BaseFilterModel baseFilterModel, bool includeFilters = true, bool includePaging = true)
{
return(await _entities.AsQueryable().ToLinqFromBaseFilter(baseFilterModel, includeFilters, includePaging).CountAsync(cancellationToken: _cancellationToken));
}
public async Task <IEnumerable <TEntity> > ListFilterAsync(BaseFilterModel baseFilterModel)
{
return(await _entities.AsQueryable().ToLinqFromBaseFilter(baseFilterModel).ToListAsync(cancellationToken: _cancellationToken));
}
public async Task <IReadOnlyList <EnglishTaskModel> > FindRandomCountEnglishTask(int count, BaseFilterModel baseFilterModel)
{
if (baseFilterModel == null || baseFilterModel.IsEmpty())
{
return(null);
}
var persistenceFilter = _mapper.Map <BaseFilter>(baseFilterModel);
IReadOnlyList <EnglishTask> englishTasks = await _taskRepository.FindAllByFilters(persistenceFilter);
if (!englishTasks.Any())
{
return(Array.Empty <EnglishTaskModel>());
}
IEnumerable <EnglishTask> randomedEnglishTasks = englishTasks.GetRandomCountOfElements(count);
var englishTaskModels = _mapper.Map <IReadOnlyList <EnglishTaskModel> >(randomedEnglishTasks);
return(englishTaskModels);
}
private static string ToQueryFilterString(this BaseFilterModel baseFilterModel, string separateOperator = "&&")
{
string queryOperator = string.Empty;
StringBuilder predicateBuilder = new StringBuilder("i=>");
for (int i = 0; baseFilterModel.FilterBy != null && i < baseFilterModel.FilterBy.Count; i++)
{
switch (baseFilterModel.FilterBy[i].Type)
{
case BaseFilterModel.FilterType.Contains: // Strings
case BaseFilterModel.FilterType.NotContains:
case BaseFilterModel.FilterType.Equal: // Int,Date,Strings,Double
case BaseFilterModel.FilterType.NotEqual:
case BaseFilterModel.FilterType.StartsWith: // Strings
case BaseFilterModel.FilterType.EndWith:
{
BaseFilterModel.FilterType type = baseFilterModel.FilterBy[i].Type.Value;
string functionName = string.Empty;
switch (type)
{
case BaseFilterModel.FilterType.Contains:
{
functionName = "Contains";
queryOperator = "";
break;
}
case BaseFilterModel.FilterType.NotContains:
{
functionName = "EndsWith";
queryOperator = "!";
break;
}
case BaseFilterModel.FilterType.Equal:
{
functionName = "Equals";
queryOperator = "";
break;
}
case BaseFilterModel.FilterType.NotEqual:
{
functionName = "Equals";
queryOperator = "!";
break;
}
case BaseFilterModel.FilterType.StartsWith:
{
functionName = "StartsWith";
queryOperator = "";
break;
}
case BaseFilterModel.FilterType.EndWith:
{
functionName = "EndsWith";
queryOperator = "";
break;
}
}
predicateBuilder.Append($"new[]{{{string.Join(",", ToNormalizeValues(baseFilterModel.FilterBy[i].Value))}}}.Any(_j_{i} => {queryOperator}i.{baseFilterModel.FilterBy[i].ColumnName}.{functionName}(_j_{i}))");
break;
}
case BaseFilterModel.FilterType.LessThan: // Int,Date,Double
predicateBuilder.Append($"i.{baseFilterModel.FilterBy[i].ColumnName} < {baseFilterModel.FilterBy[i].Value.First()}");
break;
case BaseFilterModel.FilterType.LessEqualThan: // Int,Date,Double
predicateBuilder.Append($"i.{baseFilterModel.FilterBy[i].ColumnName} <= {baseFilterModel.FilterBy[i].Value.First()}");
break;
case BaseFilterModel.FilterType.GreaterThan: // Int,Date,Double
predicateBuilder.Append($"i.{baseFilterModel.FilterBy[i].ColumnName} > {baseFilterModel.FilterBy[i].Value.First()}");
break;
case BaseFilterModel.FilterType.GreaterEqualThan: // Int,Date,Double
predicateBuilder.Append($"i.{baseFilterModel.FilterBy[i].ColumnName} >= {baseFilterModel.FilterBy[i].Value.First()}");
break;
}
if (i + 1 < baseFilterModel.FilterBy.Count)
{
predicateBuilder.Append(separateOperator);
}
}
return(predicateBuilder.Length > 3 ? predicateBuilder.ToString() : null);
}
public static IQueryable <TEntity> ToLinqFromBaseFilter <TEntity>(this IQueryable <TEntity> query, BaseFilterModel baseFilterModel, bool includeFilters = true, bool includePaging = true)
{
new[] { 1, 2, 3 }.All(j => j.Equals(5));
// TODO ...
if (baseFilterModel.SortBy != null && baseFilterModel.SortBy.IsValid)
{
string sortString = string.Join(",", baseFilterModel.ToQuerySortString());
query = query.OrderBy(sortString);
}
if (includePaging && baseFilterModel.PagingBy != null && baseFilterModel.PagingBy.IsValid)
{
query = query.Skip(baseFilterModel.PagingBy.Skip).Take(baseFilterModel.PagingBy.Take);
}
if (includeFilters)
{
string predicate = baseFilterModel.ToQueryFilterString();
if (!string.IsNullOrEmpty(predicate))
{
query = query.Where(predicate);
}
}
return(query);
}
public async Task <IEnumerable <TEntity> > ListFilterAsync(BaseFilterModel baseFilterModel)
{
return(await _baseRepository.ListFilterAsync(baseFilterModel));
}
public async Task <BaseResponse> CountFilter(BaseFilterModel baseFilterModel)
{
return(_baseResponse.SetResult(await _service.CountFilterAsync(baseFilterModel)));
}
public async Task <GenericModel <AuthorDataModel> > GetFilteredAuthorsAsync(BaseFilterModel filter)
{
var responseModel = new GenericModel <AuthorDataModel>();
var sortType = "Id";
if (filter.SortType == Enums.SortType.Name)
{
sortType = "Name";
}
var filterSql = $@"a.{sortType}";
var sort = string.Empty;
if (filter.SortState.Equals(Enums.SortState.Asc))
{
sort = "ASC";
}
if (filter.SortState.Equals(Enums.SortState.Desc))
{
sort = "DESC";
}
var columnSql = "a.Id, a.Name, p.Id, p.Title";
var offset = string.Empty;
var orderSql = string.Empty;
var countBuilder = new StringBuilder($@"SELECT COUNT(DISTINCT a.Id)
FROM AuthorInPrintingEditions AS aInP
LEFT JOIN PrintingEditions AS p ON aInP.PrintingEditionId = p.Id
INNER JOIN (
SELECT a.Id, a.Name
FROM Authors AS a
WHERE a.IsRemoved = 0" );
if (!string.IsNullOrWhiteSpace(filter.SearchString))
{
countBuilder.Append($@"AND (LOWER(a.Name) LIKE @SearchString+'%')");
}
var mainBuilder = new StringBuilder(countBuilder.ToString()).Replace("COUNT(DISTINCT a.Id)", columnSql);
var endSql = $") AS a ON aInP.AuthorId = a.Id; ";
countBuilder.Append(endSql);
orderSql = $" ORDER BY {filterSql} {sort} ";
offset = $@"OFFSET (@Page - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY ";
mainBuilder.Append(orderSql)
.Append(offset)
.Append(endSql);
var mainSql = mainBuilder.Append(countBuilder.ToString()).ToString();
var authors = new List <AuthorDataModel>();
using (var connection = GetSqlConnection())
{
var result = await connection.QueryMultipleAsync(mainSql, filter);
var dict = new Dictionary <long, AuthorDataModel>();
authors = result.Read <Author, PrintingEdition, AuthorDataModel>(
(author, printingEdition) =>
{
AuthorDataModel model;
if (!dict.TryGetValue(author.Id, out model))
{
model = new AuthorDataModel()
{
Id = author.Id,
Name = author.Name
};
model.PrintingEditionTitles = new List <string>();
dict.Add(model.Id, model);
}
model.PrintingEditionTitles.Add(printingEdition.Title);
return(model);
},
splitOn: "Id")
.Distinct()
.ToList();
responseModel.CollectionCount = result.Read <int>().FirstOrDefault();
}
responseModel.Collection = authors;
return(responseModel);
}