public async Task <IEnumerable <ProductDto> > Handle(GetProductsQuery request, CancellationToken cancellationToken) { if (request == null) { throw new ArgumentNullException(nameof(request)); } const string query = @" SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY p.Name) AS PagedNumber, p.Id, p.Name, p.ListPrice, p.ModelYear, JSON_QUERY(( select Id, Name from production.Brands where Id=p.BrandId FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as [Brand], JSON_QUERY(( select Id, Name from production.Categories where Id=p.CategoryId FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as [Category] FROM production.Products p WHERE p.Name like @Term ) AS u WHERE PagedNumber BETWEEN ((@PagedNumber-1) * @RowsPerPage + 1) AND (@PagedNumber * @RowsPerPage) FOR JSON PATH "; var @params = new { PagedNumber = request.Page, RowsPerPage = request.PageSize, Term = $"%{request.SearchProductName}%" }; return(await _cacheService.HashGetOrSetAsync( $"{CacheKeys.ProductsKey}:{request.SearchProductName}_{request.Page}_{request.PageSize}", $"{request.SearchProductName}_{request.Page}_{request.PageSize}", async() => await _connection.QueryData <List <ProductDto> >(query, @params))); }