private static string FormFilter(ISortFilterPage options)
{
const string start = "WHERE ([b].[SoftDeleted] = 0) ";
switch (options.FilterBy)
{
case BooksFilterBy.NoFilter:
return(start);
case BooksFilterBy.ByVotes:
return(start + @"AND ((
SELECT AVG(CAST([y0].[NumStars] AS float))
FROM [Review] AS [y0]
WHERE [b].[BookId] = [y0].[BookId]
) > @filterVal)");
case BooksFilterBy.ByTags:
return(start + @"AND
(@filterVal IN (SELECT [t].[TagId] FROM BookTag AS t
WHERE [t].[BookId] = [b].[BookId])) ");
case BooksFilterBy.ByPublicationYear:
return(start +
@"AND (DATEPART(year, [b].[PublishedOn]) = @filterVal)
AND ([b].[PublishedOn] <= GETUTCDATE()) ");
}
throw new NotImplementedException();
}
private static string BuildQueryString //#H
(ISortFilterPage options, bool justCount) //#H
{
var selectOptTop = FormSelectPart(options, justCount); //#I
var filter = FormFilter(options); //#J
if (justCount) //#K
{
return(selectOptTop + filter); //#K
}
var sort = FormSort(options); //#L
var optOffset = FormOffsetEnd(options); //#M
return(selectOptTop + filter //#N
+ sort + optOffset + "\n"); //#N
}
public static async Task <IEnumerable <BookListDto> > //#A
DapperBookListQueryAsync(this BookDbContext context, //#B
ISortFilterPage options) //#C
{
var command = BuildQueryString(options, false); //#D
using (new LogDapperCommand(command, context)) //#E
{
return(await context.Database.GetDbConnection() //#F
.QueryAsync <BookListDto>(command, new //#G
{ //#G
pageSize = options.PageSize, //#G
skipRows = options.PageSize //#G
* (options.PageNum - 1), //#G
filterVal = options.FilterValue //#G
}));
}
}
private static string FormSelectPart(ISortFilterPage options, bool justCount)
{
if (justCount)
{
return("SELECT COUNT(*) FROM [Books] AS [b] ");
}
var selectOpt = options.PageNum <= 1
? "SELECT TOP(@pageSize) "
: "SELECT ";
return(selectOpt +
@"[b].[BookId], [b].[Title], [b].[OrgPrice], [b].[ActualPrice],
[b].[PublishedOn],
[b].[PromotionalText] AS [PromotionPromotionalText],
[dbo].AuthorsStringUdf([b].[BookId]) AS [AuthorsOrdered],
[dbo].TagsStringUdf([b].[BookId]) AS [TagsString],
( SELECT COUNT(*) FROM [Review] AS [r] WHERE [b].[BookId] = [r].[BookId] ) AS [ReviewsCount],
( SELECT AVG(CAST([y].[NumStars] AS float)) FROM [Review] AS [y] WHERE [b].[BookId] = [y].[BookId] ) AS [ReviewsAverageVotes]
FROM [Books] AS [b]
");
}
private static string FormSort(ISortFilterPage options)
{
const string start = "ORDER BY ";
switch (options.OrderByOptions)
{
case OrderByOptions.SimpleOrder:
return(start + "[b].[BookId] DESC ");
case OrderByOptions.ByVotes:
return(start + "[ReviewsAverageVotes] DESC ");
case OrderByOptions.ByPublicationDate:
return(start + "[b].[PublishedOn] DESC ");
case OrderByOptions.ByPriceLowestFirst:
return(start + "[ActualPrice] ");
case OrderByOptions.ByPriceHighestFirst:
return(start + "[ActualPrice] DESC ");
}
throw new NotImplementedException();
}
private static string FormOffsetEnd(ISortFilterPage options)
{
return(options.PageNum <= 1
? ""
: " OFFSET @skipRows ROWS FETCH NEXT @pageSize ROWS ONLY");
}
