Paginated search results with LINQ to SQL

Paginated search results with LINQ to SQL

醉生梦死 发布于 2021-11-26 字数 1271 浏览 864 回复 2 原文

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

I have the following scenario:

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. 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?

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

EDIT: 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?

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

儭儭莪哋寶赑 2022-06-07 2 楼

You can use the Take extension method:

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

You can take this one step further and use Skip for subsequent "pages":

public IQueryable<Item> FindItemsByDescription(string description, int resultAmount, int page)
{
   return from item in _dc.Items
          where item.Description.Contains(description).Skip(resultAmount * page).Take(resultAmount);
}
故事还在继续 2022-06-07 1 楼

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

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

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..