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"); }