public async Task <(List <AuthorWithNestedObjects>, int)> GetWithParamsAsync(AuthorRequestParameters parameters)
{
int count = default(int);
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template countExpression = sqlBuilder.AddTemplate(
$@"SELECT COUNT (DISTINCT Authors.Id) FROM Authors
LEFT JOIN AuthorInBooks ON Authors.Id = AuthorInBooks.AuthorId
LEFT JOIN PrintedEditions ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId
/**where**/");
SqlBuilder.Template itemsExpression = sqlBuilder.AddTemplate($@"SELECT * FROM
(SELECT DISTINCT Authors.* FROM Authors
LEFT JOIN AuthorInBooks ON Authors.Id = AuthorInBooks.AuthorId
LEFT JOIN PrintedEditions ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId
/**where**/ /**orderby**/) AS FilteredAuthors
LEFT JOIN AuthorInBooks ON FilteredAuthors.Id = AuthorInBooks.AuthorId
LEFT JOIN PrintedEditions ON PrintedEditions.Id = AuthorInBooks.PrintedEditionId");
if (!string.IsNullOrWhiteSpace(parameters.Name))
{
parameters.Name = parameters.Name.ToLower();
sqlBuilder.Where($@"LOWER(Authors.Name) LIKE '%' + @Name + '%'");
}
if (parameters.IgnoreAuthorsList.Any())
{
sqlBuilder.Where($"Authors.Id NOT IN @IgnoreAuthorsList");
}
count = await _connection.ExecuteScalarAsync <int>(countExpression.RawSql, parameters);
if (parameters.WithPagination)
{
sqlBuilder.OrderBy($"Authors.Name OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY");
}
Dictionary <int, AuthorWithNestedObjects> authorWithNestedObjectsDictionary = new Dictionary <int, AuthorWithNestedObjects>();
IEnumerable <AuthorWithNestedObjects> response = await _connection.QueryAsync <Author, AuthorInBook, PrintedEdition, AuthorWithNestedObjects>(itemsExpression.RawSql,
(author, authorInBook, printedEdition) =>
{
bool isExist = authorWithNestedObjectsDictionary.TryGetValue(author.Id, out AuthorWithNestedObjects authorWithNestedObjects);
if (!isExist)
{
authorWithNestedObjects = new AuthorWithNestedObjects();
authorWithNestedObjects.Author = author;
authorWithNestedObjectsDictionary.Add(author.Id, authorWithNestedObjects);
}
if (!(authorInBook is null))
{
authorInBook.PrintedEdition = printedEdition;
authorWithNestedObjects.AuthorInBooks.Add(authorInBook);
}
return(authorWithNestedObjects);
}, parameters);
public async Task <AuthorResponseModel> GetAsync(AuthorRequestModel requestModel)
{
AuthorRequestParameters parameters = requestModel.MapToRequestParameters();
(List <AuthorWithNestedObjects> authors, int count) = await _authorRepository.GetWithParamsAsync(parameters);
List <AuthorModel> authorModels = authors.Select(item => new AuthorModel(item)).ToList();
AuthorResponseModel responseModel = new AuthorResponseModel
{
Count = count,
AuthorsList = authorModels
};
return(responseModel);
}
public AuthorRequestParameters MapToRequestParameters()
{
AuthorRequestParameters parameters = new AuthorRequestParameters();
if (Page != default(int))
{
parameters.Page = Page;
}
if (PageSize != default(int))
{
parameters.PageSize = PageSize;
}
if (!string.IsNullOrWhiteSpace(Name))
{
parameters.Name = Name;
}
parameters.WithPagination = WithPagination;
if (!(IgnoreAuthorsList is null))
{
parameters.IgnoreAuthorsList = IgnoreAuthorsList.Select(item => item.Id).ToList();
}
return(parameters);
}
public async Task <(List <AuthorWithNestedObjects>, int)> GetWithParamsAsync(AuthorRequestParameters parameters)
{
IQueryable <AuthorWithNestedObjects> authors = _dbSet.GroupJoin(_context.AuthorInBooks.Include(item => item.PrintedEdition),
outerKeySelector => outerKeySelector.Id,
innerKeySelector => innerKeySelector.AuthorId,
(author, authorInBooks) => new AuthorWithNestedObjects
{
Author = author,
AuthorInBooks = authorInBooks.ToList()
});
if (!string.IsNullOrWhiteSpace(parameters.Name))
{
authors = authors.Where(item => item.Author.Name.Contains(parameters.Name, StringComparison.OrdinalIgnoreCase));
}
if (parameters.IgnoreAuthorsList.Any())
{
authors = authors.Where(item => !parameters.IgnoreAuthorsList.Contains(item.Author.Id));
}
authors = authors.OrderByDescending(item => item.Author.CreationDate);
int count = default(int);
if (parameters.WithPagination)
{
count = await authors.CountAsync();
int skipCount = (--parameters.Page) * parameters.PageSize;
authors = authors.Skip(skipCount).Take(parameters.PageSize);
}
List <AuthorWithNestedObjects> result = await authors.AsNoTracking().ToListAsync();
return(result, count);
}