在Asp.net Web窗体和实体框架中使用单个查询进行分页

Question

我有一个页面列出了一些内容。我用下面的代码为页面创建了一个分页。但正如你所看到的，我必须创建第二个查询来获得RowCount。我可以成为没有第二个查询数据库的分页？

// ContentList.aspx.cs 
MySiteEntities db = new MySiteEntities(); 

my_ContentList = db.Content.Where(it => it.Language == "En" && it.Type == QueryType && it.Active == true && it.Deleted == false).OrderBy(it => it.Sort).Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList(); 

RowCount = db.Content.Where(it => it.Language == "En" && it.Type == QueryType && it.Active == true && it.Deleted == false).Count(); 

PageCount = RowCount/PageSize + 1; 




// ContentList.aspx 

<%if (RowCount > PageSize) {%> 

    <%if (PageIndex > 1) {%><a href="Content/<%=Type%>/<%=PageIndex-1%>" class="page-numbers prev">Previous</a><%}%> 

    <% 
    int Position = 1; 
    do 
    { 
    %> 
     <a href="Content/<%=Type%>/<%=Position%>" class="page-numbers <%if (Position == PageIndex) {%>current<%}%>"><%=Position%></a> 
    <% 
    Position++; 
    } while (Position < PageCount+1); 
    %> 

    <%if (PageIndex != PageCount) {%><a href="Content/<%=Type%>/<%=PageIndex+1%>" class="page-numbers next">Next</a><%}%> 

<%}%> 

Answer 1

解决方案1： 使用从EntityFramework.Extended库就像下面的查询，这是从溶液中2更好的性能！

var query = db.Content.Where(it => it.Language == "En" && it.Type == QueryType && it.Active == true && it.Deleted == false); 

var countQuery= query.FutureCount(); 

var itemsQuery= query.OrderBy(it => it.Sort) 
    .Skip(PageSize * (PageIndex - 1)) 
    .Take(PageSize) 
    .Future(); 

int RowCount = countQuery.Value; 
var my_ContentList = itemsQuery.ToList(); 

解决方案2： 下面的查询会得到一个访问数据库的次数和页面结果，但是如果您检查SQL中SqlProfiler，你会看到，它是不是很漂亮。

var query = db.Content.Where(it => it.Language == "En" && it.Type == QueryType && it.Active == true && it.Deleted == false); 

var pageResult = query.OrderBy(it => it.Sort) 
    .Skip(PageSize * (PageIndex - 1)) 
    .Take(PageSize) 
    .GroupBy (p => new { Total = query.Count() }) 
    .First(); 

int RowCount = pageResult .Key.Total; 
var my_ContentList = pageResult .Select(p => p); 

检查这个 link1和link2