public async Task Update(PrintedEditionModel model)
{
PrintedEditionWithNestedObjects printedEdition = await _printedEditionRepository.GetWithNestedObjectsByIdAsync(model.Id);
printedEdition.PrintedEdition = model.MapToEntity(printedEdition.PrintedEdition);
List <int> authorIdsFromModel = model.AuthorModels.Select(item => item.Id).ToList();
List <int> authorIdsFromEntity = printedEdition.AuthorInBooks.Select(item => item.AuthorId).ToList();
List <AuthorInBook> listToRemove = printedEdition.AuthorInBooks.Where(item => !authorIdsFromModel.Contains(item.AuthorId)).ToList();
List <AuthorInBook> listToCreate = model.AuthorModels.Where(item => !authorIdsFromEntity.Contains(item.Id)).Select(item => new AuthorInBook {
PrintedEditionId = printedEdition.PrintedEdition.Id, AuthorId = item.Id
}).ToList();
await _printedEditionRepository.UpdateAsync(printedEdition.PrintedEdition);
if (listToRemove.Any())
{
await _authorInBookRepository.RemoveRangeAsync(listToRemove);
}
if (listToCreate.Any())
{
await _authorInBookRepository.AddRangeAsync(listToCreate);
}
}
internal PrintedEditionModel(PrintedEditionWithNestedObjects printedEdition) : this(printedEdition.PrintedEdition)
{
if (!(printedEdition.AuthorInBooks is null))
{
AuthorModels = printedEdition.AuthorInBooks.Select(item => new AuthorModel(item.Author)).ToList();
}
}
public async Task <PrintedEditionModel> GetByIdAsync(int id)
{
PrintedEditionWithNestedObjects printedEdition = await _printedEditionRepository.GetWithNestedObjectsByIdAsync(id);
PrintedEditionModel printedEditionModel = new PrintedEditionModel(printedEdition);
return(printedEditionModel);
}
public async Task <PrintedEditionWithNestedObjects> GetWithNestedObjectsByIdAsync(int id)
{
PrintedEditionWithNestedObjects printedEdition = await _dbSet.Where(item => (item.Id == id)).GroupJoin(_context.AuthorInBooks.Include(authorInBook => authorInBook.Author),
outerKeySelector => outerKeySelector.Id,
innerKeySelector => innerKeySelector.PrintedEditionId,
(product, authorInBooks) => new PrintedEditionWithNestedObjects
{
PrintedEdition = product,
AuthorInBooks = authorInBooks.ToList()
}).FirstOrDefaultAsync();
return(printedEdition);
}
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);
},
