当我有PagedList< Products>时，如何将零数量产品移到列表末尾? [英] How to Move Zero Qty Products to the end of List when i have a PagedList<Products>?

问题描述

我在控制器中有一个方法，该方法将PagedList返回到包含产品(基于当前 页码 和 页面大小 :

I have a method in my controller which returns a PagedList to my category-page View that contains Products (based current Page-Number and Page-Size which user has selected) from SQL Server stored-procedure like blow :

var products = _dbContext.EntityFromSql<Product>("ProductLoad",
pCategoryIds,
pManufacturerId,
pOrderBy,
pageIndex,
pageSize).ToList(); // returning products based selected Ordering by.
var totalRecords = pTotalRecords.Value != DBNull.Value ? Convert.ToInt32(pTotalRecords.Value) : 0;
var allPrd= new PagedList<Product>(products, pageIndex, pageSize, totalRecords);

将参数发送到db存储过程的示例是:

("ProductLoad",
[1,10,13],
[653],
"Lowest Price",
 2,
 64) // so it returns second 64 products with those category-ids and Brand-ids sorting by lowest to highest price

它工作正常，但是我想做的是始终将数量为0的产品发送到列表末尾.

It's working fine , but what i am trying to do is always sending products with 0 quantity to the end of list.

例如: 如果我有1万种产品，其中2k种产品的数量为0，则需要先显示这8k种产品，然后在列表末尾显示2k种不可用的产品)

For example : if i had 10k products which 2k of them have 0 quantity , i need to show this 8k products first and then 2k unavailable products in the end of list)

到目前为止我已经尝试过的东西始终会加载所有没有页面大小和页面索引的产品，然后通过此方法将零数量的产品发送到列表的末尾，最后是Pagedlist并进行修复页面大小:

what i have tried so far is always loading All products without page-size and page-index first then send zero qty products to the end of the list by this and finally Pagedlist with fixing page size :

 ("ProductLoad",
 [1,10,13],
 [653],
 "Lowest Price",
  0,
  10000) // fixed page size means loading all products   

 var zeroQty= from p in products
                    where p.StockQuantity==0
                    select p;

  var zeroQtyList= zeroQty.ToList();
  products = products.Except(zeroQtyList).ToList();
  products.AddRange(zeroQtyList);

  var totalRecords = pTotalRecords.Value != DBNull.Value ? Convert.ToInt32(pTotalRecords.Value) : 0;
  var allPrd= new PagedList<Product>(products, pageIndex, 64, totalRecords); 

这将导致所有零数量的产品转到列表的末尾.

It cause all zero qty Products goes to the end of list.

但是它总是加载所有产品，这不是一个好主意，并且肯定不是一种优化的方式，有时用户会导致页面加载超时， (因为类别页面在每个页面的索引号中显示64个产品)每次用户在网站上打开一个页面时，所有产品都会加载，并且会导致加载页面上的延迟.

But it always loads all products that is not a good idea and for sure not an optimized way , sometime users get page loading time-out, (because category-page show 64 products in every page-index-number) every time user opens a page in the website, all products would loads and it cause delay in loading page.

反正有解决此问题的方法(有一个PagedList 包含所有不止零个数量的产品和0个数量的产品 第二)不更改存储过程? (修复加载页面 延迟)

Is there anyway to solve this problem (have a PagedList which contains all more than zero qty products first and 0 qty products second) without changing stored-procedure? (fixing loading page delays)

P.S:我之所以避免更改存储过程，是因为它已经有太多的连接，临时表Union和Order by.

P.S : The reason i avoid changing stored-procedure is it has already too much join,temp-table Union and Order by.

任何帮助将不胜感激.

推荐答案

您将需要使用这是我以前做过的一个例子.希望您能够将其适应您的SP.

This is an example of how I have done this before. Hopefully you will be able to adapt it to your SP.

--temp table to hold the message IDs we are looking for
CREATE TABLE #ids (
     MessageId UNIQUEIDENTIFIER
    ,RowNum INT PRIMARY KEY
);

--insert all message IDs that match the search criteria, with row number
INSERT INTO #ids
SELECT m.[MessageId]
      ,ROW_NUMBER() OVER (ORDER BY CreatedUTC DESC)
  FROM [dbo].[Message] m WITH (NOLOCK)
 WHERE ....

DECLARE @total INT;
SELECT @total = COUNT(1) FROM #ids;

--determine which records we want to select
--note: @skip and @take are parameters of the procedure
IF @take IS NULL
    SET @take = @total;
DECLARE @startRow INT, @endRow INT;
SET @startRow = @skip + 1;
SET @endRow = @skip + @take;

-- select the messages within the requested range
SELECT m.* FROM [dbo].[Message] WITH (NOLOCK)
INNER JOIN #ids i ON m.MessageId = i.MessageId
WHERE i.RowNum BETWEEN @startRow AND @endRow;

这篇关于当我有PagedList< Products>时，如何将零数量产品移到列表末尾?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！