internal PrintedEditionRequestParameters MapToRequestParameters()
{
PrintedEditionRequestParameters parameters = new PrintedEditionRequestParameters();
if (Page != default(int))
{
parameters.Page = Page;
}
if (PageSize != default(int))
{
parameters.PageSize = PageSize;
}
if (!string.IsNullOrWhiteSpace(KeyWord))
{
parameters.KeyWord = KeyWord;
}
if (PriceFrom != default(int))
{
parameters.PriceFrom = PriceFrom;
}
if (PriceTo != default(int))
{
parameters.PriceTo = PriceTo;
}
if (SortCriteria != SortCriteria.None)
{
parameters.SortCriteria = SortCriteria;
}
if (PrintedEditionType != PrintedEditionType.None)
{
parameters.PrintedEditionType = PrintedEditionType;
}
return(parameters);
}
public async Task <PrintedEditionResponseModel> GetAsync(PrintedEditionRequestModel requestModel)
{
PrintedEditionRequestParameters parameters = requestModel.MapToRequestParameters();
(List <PrintedEditionWithNestedObjects> printedEditions, int count) = await _printedEditionRepository.GetWithNestedObjectsAsync(parameters);
PrintedEditionResponseModel responseModel = new PrintedEditionResponseModel(count, printedEditions);
return(responseModel);
}
public async Task <(List <PrintedEditionWithNestedObjects>, int)> GetWithNestedObjectsAsync(PrintedEditionRequestParameters requestParameters)
{
IQueryable <PrintedEditionWithNestedObjects> printedEditions = _dbSet.GroupJoin(_context.AuthorInBooks.Include(item => item.Author),
outerKeySelector => outerKeySelector.Id,
innerKeySelector => innerKeySelector.PrintedEditionId,
(printedEdition, authorInBooks) => new PrintedEditionWithNestedObjects
{
PrintedEdition = printedEdition,
AuthorInBooks = authorInBooks.ToList()
});
printedEditions = printedEditions.Where(item => (item.PrintedEdition.Price >= requestParameters.PriceFrom && item.PrintedEdition.Price <= requestParameters.PriceTo));
if (!string.IsNullOrWhiteSpace(requestParameters.KeyWord))
{
printedEditions = printedEditions.Where(item => (item.PrintedEdition.Name.Contains(requestParameters.KeyWord) || item.PrintedEdition.Description.Contains(requestParameters.KeyWord)));
}
if (requestParameters.PrintedEditionType != PrintedEditionType.None)
{
printedEditions = printedEditions.Where(item => item.PrintedEdition.Type == requestParameters.PrintedEditionType);
}
if (requestParameters.SortCriteria == SortCriteria.PriceAsc)
{
printedEditions = printedEditions.OrderBy(item => item.PrintedEdition.Price);
}
if (requestParameters.SortCriteria == SortCriteria.PriceDesc)
{
printedEditions = printedEditions.OrderByDescending(item => item.PrintedEdition.Price);
}
if (requestParameters.SortCriteria == SortCriteria.CurrencyAsc)
{
printedEditions = printedEditions.OrderBy(item => item.PrintedEdition.Currency);
}
if (requestParameters.SortCriteria == SortCriteria.CurrencyDesc)
{
printedEditions = printedEditions.OrderByDescending(item => item.PrintedEdition.Currency);
}
if (requestParameters.SortCriteria == SortCriteria.None)
{
printedEditions = printedEditions.OrderByDescending(item => item.PrintedEdition.CreationDate);
}
int count = await printedEditions.CountAsync();
int countToSkip = (--requestParameters.Page) * requestParameters.PageSize;
printedEditions = printedEditions.Skip(countToSkip).Take(requestParameters.PageSize);
List <PrintedEditionWithNestedObjects> result = await printedEditions.AsNoTracking().ToListAsync();
return(result, count);
}
public async Task <(List <PrintedEditionWithNestedObjects>, int)> GetWithNestedObjectsAsync(PrintedEditionRequestParameters parameters)
{
int skipCount = (parameters.Page - 1) * parameters.PageSize;
int count = default(int);
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template countExpression = sqlBuilder.AddTemplate(
$@"SELECT COUNT (DISTINCT PrintedEditions.Id) FROM PrintedEditions
LEFT JOIN AuthorInBooks ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId
LEFT JOIN Authors ON Authors.Id = AuthorInBooks.AuthorId
/**where**/");
SqlBuilder.Template itemsExpression = sqlBuilder.AddTemplate(
$@"SELECT * FROM (SELECT DISTINCT PrintedEditions.* FROM PrintedEditions
LEFT JOIN AuthorInBooks ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId
LEFT JOIN Authors ON Authors.Id = AuthorInBooks.AuthorId
/**where**/ /**orderby**/ ) AS FilteredPrintedEditions
LEFT JOIN AuthorInBooks ON FilteredPrintedEditions.Id = AuthorInBooks.PrintedEditionId
LEFT JOIN Authors ON Authors.Id = AuthorInBooks.AuthorId");
sqlBuilder.Where("PrintedEditions.Price >= @PriceFrom AND PrintedEditions.Price <= @PriceTo");
if (!string.IsNullOrWhiteSpace(parameters.KeyWord))
{
parameters.KeyWord = parameters.KeyWord.ToLower();
sqlBuilder.Where($@"LOWER(PrintedEditions.Name + ' ' + PrintedEditions.Description) LIKE '%' + @KeyWord + '%'");
}
if (parameters.PrintedEditionType != PrintedEditionType.None)
{
sqlBuilder.Where("PrintedEditions.Type = @PrintedEditionType");
}
count = await _connection.ExecuteScalarAsync <int>(countExpression.RawSql, parameters);
if (parameters.SortCriteria == SortCriteria.PriceAsc)
{
sqlBuilder.OrderBy($"PrintedEditions.Price ASC OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
if (parameters.SortCriteria == SortCriteria.PriceDesc)
{
sqlBuilder.OrderBy($"PrintedEditions.Price DESC OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
if (parameters.SortCriteria == SortCriteria.CurrencyAsc)
{
sqlBuilder.OrderBy($"PrintedEditions.Currency ASC OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
if (parameters.SortCriteria == SortCriteria.CurrencyDesc)
{
sqlBuilder.OrderBy($"PrintedEditions.Price DESC OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
if (parameters.SortCriteria == SortCriteria.None)
{
sqlBuilder.OrderBy($"PrintedEditions.Id OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
Dictionary <int, PrintedEditionWithNestedObjects> printedEditionWithNestedObjectsDictionary = new Dictionary <int, PrintedEditionWithNestedObjects>();
IEnumerable <PrintedEditionWithNestedObjects> response = await _connection.QueryAsync <PrintedEdition, AuthorInBook, Author, PrintedEditionWithNestedObjects>(itemsExpression.RawSql,
(printedEdition, authorInBook, author) =>
{
bool isExsist = printedEditionWithNestedObjectsDictionary.TryGetValue(printedEdition.Id, out PrintedEditionWithNestedObjects printedEditionWithNestedObjects);
if (!isExsist)
{
printedEditionWithNestedObjects = new PrintedEditionWithNestedObjects();
printedEditionWithNestedObjects.PrintedEdition = printedEdition;
printedEditionWithNestedObjectsDictionary.Add(printedEdition.Id, printedEditionWithNestedObjects);
}
if (!(authorInBook is null))
{
authorInBook.Author = author;
printedEditionWithNestedObjects.AuthorInBooks.Add(authorInBook);
}
return(printedEditionWithNestedObjects);
},
