使用 LINQ to SQL 对搜索结果进行分页 [英] Paginated search results with LINQ to SQL

问题描述

使用 LINQ to SQL 获得分页结果的最佳模式是什么?

What's the best pattern to get paginated results with LINQ to SQL?

我有以下场景:

假设我想通过描述搜索items表.我可以轻松做到:

Suppose I want to search items table by description. I can easily do:

public IQueryable<Item> FindItemsByDescription(string description)
{
   return from item in _dc.Items
          where item.Description.Contains(description);
}

现在，对这个结果集进行分页的最佳方法是什么?

Now, what would be the best way to paginate this result set?

1. 我是否应该在执行此操作之前执行 count 查询以找出结果集大小，然后根据我的需要限制此查询?我觉得这是要走的路.
2. 我是否应该执行完整查询，从数组大小中获取计数并仅返回该数组中的一个分页子集?如果结果集足够大，我觉得这将是一个巨大的时间浪费......或者 LINQ to SQL 在这里做了什么魔术?

1. Should I perform a count query before doing this to find out the result set size and then limit this query according to what I want? I feel like this is the way to go.
2. Should I perform the full query, take the count from the array size and return only a paginated subset from this array? I feel like this will be a huge waste of time if the resultset is big enough... Or is LINQ to SQL doing some magic here?

是否有用于执行此操作的 LINQ to SQL 通用模式?

Is there a LINQ to SQL common pattern for performing this operation?

我必须澄清一件小事.我知道 Take 和 Skip 方法.但是，在使用 Take 和 Skip 之前，我应该如何获得查询将检索到的结果的总数?

I must clarify a one little thing. I am aware of Take and Skip methods. But, before using Take and Skip, how should I get the total count of results that query would retrieve?

推荐答案

分页的模式非常简单.它涉及到 Skip() 和 Take() 扩展方法的使用如下:

The pattern for paging is very simple. It involves the use of the Skip() and Take() extension methods as follows:

public IQueryable<Item> FindItemsByDescription(string description, int pageIndex, int pageSize)
{
   return from item in _dc.Items
          where item.Description.
          Contains(description).
          Skip((pageIndex - 1) * pageSize).
          Take(pageSize);
}

更新:要获得总计数，只需使用 Count() 方法:

UPDATE: To get the total count simply use the Count() method:

int totalCount = from item in _dc.Items
                 where item.Description.
                 Contains(description).Count();

int numberOfPages = (int)(totalCount/pageSize);

根据您将如何显示记录，您可以使用 numberOfPages 来显示带有第 X 页，共 Y 页"的导航栏……第 1 页，共 10 页等.

Depending on how you are going to the display the records, you can use the numberOfPages to display a navigation bar with "Page X of Y" ... Page 1 of 10, etc..

这篇关于使用 LINQ to SQL 对搜索结果进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！