Paging with single query in Asp.net Web Forms and Entity Framework

Question

I have a page that list some contents. I have created a paging for the page with the code below. But as you see i had to create a second query to get RowCount. Can i achive paging without a second query to database?

// 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><%}%>

<%}%>

score 0 · Answer 1 · edited May 23 '17 at 10:28

solution 1: use from EntityFramework.Extended library like following query, this is better performance from solution 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();

solution 2: The following query will get the count and page results in one trip to the database, but if you check the SQL in SqlProfiler, you'll see that it's not very pretty.

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

check this link1 and link2

Paging with single query in Asp.net Web Forms and Entity Framework

1 Answers1