Ejemplo n.º 1
0
        public async Task <AuthorResponseDataModel> FilterAsync(AuthorRequestDataModel authorRequestDataModel)
        {
            var query = $@"SELECT t.Id, t.Name, t.IsRemoved, authorInPrintingEdition.Id,
	                        authorInPrintingEdition.AuthorId, authorInPrintingEdition.PrintingEditionId,
	                        authorInPrintingEdition.aInPEId, authorInPrintingEdition.Currency,
	                        authorInPrintingEdition.Description, authorInPrintingEdition.IsRemoved,
	                        authorInPrintingEdition.Price, authorInPrintingEdition.CreationDate,
	                        authorInPrintingEdition.Title, authorInPrintingEdition.Type
	                          FROM (
	                              SELECT a.Id, a.IsRemoved, a.Name
	                              FROM {tableName} AS a
	                              WHERE a.IsRemoved != 1
	                              ORDER BY a.Id {authorRequestDataModel.SortType.ToString().ToUpper()}
	                              OFFSET {authorRequestDataModel.Paging.CurrentPage * authorRequestDataModel.Paging.ItemsCount} ROWS 
                                  FETCH NEXT {authorRequestDataModel.Paging.ItemsCount} ROWS ONLY
	                          ) AS t
	                          LEFT JOIN (
	                              SELECT aInPe.Id, aInPe.AuthorId, aInPe.PrintingEditionId, p.Id AS aInPeId, p.Currency,
	                                p.Description, p.IsRemoved, p.Price, p.CreationDate, p.Title, p.Type
	                              FROM {Constants.AUTHOR_IN_PRINTING_EDITIONS_TABLE_NAME} AS aInPe
	                              LEFT JOIN {Constants.PRINTING_EDITIONS_TABLE_NAME} AS p ON aInPe.PrintingEditionId = p.Id
	                          ) AS authorInPrintingEdition ON t.Id = authorInPrintingEdition.AuthorId
	                        ORDER BY t.Id {authorRequestDataModel.SortType.ToString().ToUpper()}, authorInPrintingEdition.Id"    ;

            using (var dbContext = new SqlConnection(connectionString))
            {
                await dbContext.OpenAsync();

                var authors = await dbContext.QueryAsync <Author, PrintingEdition, Author>(
                    query, (author, printingEdition) =>
                {
                    if (printingEdition != null)
                    {
                        author.PrintingEditions.Add(printingEdition);
                    }
                    return(author);
                });

                authors = authors
                          .GroupBy(author => author.Id)
                          .Select(group =>
                {
                    var result = group.FirstOrDefault();
                    result.PrintingEditions = group.Select(author => author.PrintingEditions.SingleOrDefault()).ToList();
                    return(result);
                });
                query = $"SELECT COUNT(*) FROM {tableName} WHERE IsRemoved != 1";
                var totalCount = await dbContext.QueryFirstOrDefaultAsync <int>(query.ToString());

                var result = new AuthorResponseDataModel
                {
                    Authors    = authors.ToList(),
                    TotalCount = totalCount
                };
                return(result);
            }
        }
Ejemplo n.º 2
0
        public static AuthorRequestDataModel Map(AuthorRequestModel filterModel)
        {
            if (filterModel is null)
            {
                return(new AuthorRequestDataModel());
            }
            var filter = new AuthorRequestDataModel
            {
                Paging           = _pagingMapper.Map(filterModel.Paging),
                SortPropertyName = filterModel.SortPropertyName,
                SortType         = filterModel.SortType
            };

            return(filter);
        }