ASP.NET MVC2 LINQ - 库模式，应该在哪里分页code去了？ [英] ASP.NET MVC2 LINQ - Repository pattern, where should the pagination code go?

问题描述

我的工作增加了对分页的的HtmlHelper，但我不确定在哪里合适的和/或最有利的地方放分页code的某些部分从性能和可维护性的立场。

我不确定是否跳过（），以（）和COUNT（）的LINQ的部分，以SQL数据操作应库或控制器内生活。

我也不能确定，如果他们的订单，并在那里他们被用来影响任何性能。

如果他们生活在存储库中，从我的理解，这是它如何工作的：

1 我要传递的PageIndex和pageSize的作为参数从数据库中抓取的数据存储库的方法。

2 然后抓住完整的数据从数据库中设置的。

3 ，然后存储完整的数据在一个变量设置TOTALITEMS的计数。

4。，然后应用跳过（）和Take（），这样的数据集只保留我需要的页面。

5。显示部分数据设置为在视图中的单个页面。

如果他们住在离我的理解控制器，这是它如何工作的：
1.我会抓住完整的数据从存储库设置，并将其存储到一个变量在控制器内。
2.然后得到TOTALITEMS的计数为完整的数据集。
3.然后应用跳过（）和Take（），这样的数据集只保留我需要的页面。
4.显示部分数据设置为在视图中的单个页面。

控制器内（我知道我会得到不正确的页数这里，而不是TOTALITEMS）：

 
字符[] = charactersToShow charactersRepository.GetCharactersByRank（this.PageIndex，this.PageSize）;
RankViewModel视图模型=新RankViewModel
{
    字符= charactersToShow，
    PaginationInfo =新PaginationInfo
    {
        当前页= this.PageIndex，
        ItemsPerPage = this.PageSize，
        TOTALITEMS = charactersToShow.Count（）
    }
};
 

在存储库：

 
公共字[] GetCharactersByRank（INT的PageIndex，诠释每页）
{
    IQueryable的字符数=（从C在db.Characters
        排序依据c.Kill降
        选择新的角色{
            CharID = c.CharID，
            CHARNAME = c.CharName，
            级别= c.Level
        }）;
    字符=的PageIndex> 1？ characters.Skip（（的PageIndex  -  1）*每页）。取（每页）：characters.Take（每页）;
    返回characters.ToArray（）;
}
 

这code是我如何实施跳过（）的部分例子，以（）和count（）code居住在库中。我其实没有得到落实并返回TOTALITEMS因为当时我意识到，我不知道正确的地方藏起来。

的我不能确定在哪里把这些部分原因是，我不知道LINQ到SQL如何工作的引擎盖底下，因此我不知道如何进行性能优化。我也不知道这是即使在这种情况下一个问题。

它必须抓住从数据库中的所有记录，当你做对LINQ的一个.Count之间的（）到SQL？
是否有作出单独的查询，如果我做一个.Count之间的（），再后来做了.Skip（）和。取（）？
是否有使用.Count之间的（）之前的.Skip（）和。取（）？任何可能的性能问题。

这是使用ORM我第一次，所以我不知道会发生什么。我知道我可以看到的LINQ to SQL运行查询，但是我觉得，听有经验的人在这种情况下会更好地利用我的时间。

我想明白这一点更深入，任何有识之士将AP preciated。

解决方案

我发现这个在的NerdDinner 网站，马尔科上面提到的，它回答了很多我的问题。

从的NerdDinner 第8页的底部：

IQueryable的是一个非常强大的功能，使各种有趣的延迟执行的情况（如分页和组成基于查询）。如同所有的强大功能，你要小心你如何使用它并确保它不被滥用。

要认识到从你的资料库返回一个IQueryable这个结果使能调用code至追加对链式运营商的方法吧，等参加最终的查询执行是非常重要的。如果你不想提供调用code这个能力，那么你就应该返回回的IList或IEnumerable的结果 - 它包含已执行的查询的结果。

对于分页情况下，这将需要你的实际数据分页的逻辑推到被调用的库法。在这种情况下，我们可能会更新我们的FindUpcomingDinners（）finder方法有一个签名，要么返回PaginatedList：

      PaginatedList＆LT;晚餐> FindUpcomingDinners（INT的PageIndex，诠释的pageSize）{}

或返回回一个IList，并使用TOTALCOUNT出参数返回晚餐的总数：

      IList的FindUpcomingDinners（INT的PageIndex，诠释的pageSize，OUT INT TOTALCOUNT）{}

I'm working on adding an HtmlHelper for pagination, but I am unsure where the proper and/or most beneficial place to put certain parts of the pagination code from a performance and maintainability standpoint.

I am unsure if the Skip(), Take() and Count() portions of Linq to SQL data manipulation should live within the repository or the controller.

I am also unsure if their order and where they are used affects performance in any way.

If they live within the repository from my understanding this is how it would work:
1. I would pass the pageIndex and pageSize as arguments to the repository's method that grabs the data from the database.
2. Then grab the full data set from the database.
3. Then store the count of TotalItems of that full data set in a variable.
4. Then apply the Skip() and Take() so the data set retains only the page I need.
5. Display the partial data set as a single page in the view.

If they live in the controller from my understanding this is how it would work: 1. I would grab the full data set from the repository and store it into a variable inside of the controller. 2. Then get the count of TotalItems for the full data set. 3. Then apply the Skip() and Take() so the data set retains only the page I need. 4. Display the partial data set as a single page in the view.

Inside the controller (I realize I will incorrectly get the page count here and not TotalItems):

Character[] charactersToShow = charactersRepository.GetCharactersByRank(this.PageIndex, this.PageSize);
RankViewModel viewModel = new RankViewModel
{
    Characters = charactersToShow,
    PaginationInfo = new PaginationInfo
    {
        CurrentPage = this.PageIndex,
        ItemsPerPage = this.PageSize,
        TotalItems = charactersToShow.Count()
    }
};

Inside the repository:

public Character[] GetCharactersByRank(int PageIndex, int PageSize)
{
    IQueryable characters = (from c in db.Characters
        orderby c.Kill descending
        select new Character {
            CharID = c.CharID,
            CharName = c.CharName,
            Level = c.Level
        });
    characters = PageIndex > 1 ? characters.Skip((PageIndex - 1) * PageSize).Take(PageSize) : characters.Take(PageSize);
    return characters.ToArray();
}

This code is a partial example of how I was implementing the Skip(), Take() and Count() code living in the repository. I didn't actually implement getting and returning the TotalItems because that was when I realized I didn't know the proper place to put this.

Part of the reason I am unsure where to put these is that I don't know how Linq to SQL works underneath the hood, and thus I don't know how to optimize for performance. Nor do I know if this is even an issue in this case.

Does it have to grab ALL the records from the database when you do a .Count() on the Linq to SQL? Does it have to make separate queries if I do a .Count(), then later do a .Skip() and .Take()? Is there any possible performance problems with using .Count() prior to a .Skip() and .Take()?

This is my first time using an ORM so I'm not sure what to expect. I know I can view the queries Linq to SQL is running, however I feel that listening to someone with experience in this case would be better use of my time.

I would like to understand this more in depth, any insight would be appreciated.

解决方案

I found this on the NerdDinner site that Marko mentioned above and it answered a lot of my questions.

From NerdDinner on the bottom of page 8:

IQueryable is a very powerful feature that enables a variety of interesting deferred execution scenarios (like paging and composition based queries). As with all powerful features, you want to be careful with how you use it and make sure it is not abused.

It is important to recognize that returning an IQueryable result from your repository enables calling code to append on chained operator methods to it, and so participate in the ultimate query execution. If you do not want to provide calling code this ability, then you should return back IList or IEnumerable results - which contain the results of a query that has already executed.

For pagination scenarios this would require you to push the actual data pagination logic into the repository method being called. In this scenario we might update our FindUpcomingDinners() finder method to have a signature that either returned a PaginatedList:

PaginatedList< Dinner> FindUpcomingDinners(int pageIndex, int pageSize) { }

Or return back an IList, and use a "totalCount" out param to return the total count of Dinners:

IList FindUpcomingDinners(int pageIndex, int pageSize, out int totalCount) { }

这篇关于ASP.NET MVC2 LINQ - 库模式，应该在哪里分页code去了？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！