Linq to SQL Custom Paging

microsoft.public.dotnet.framework.adonet

15 Nov 2007 12:15 PM

Mike

Not sure if this is the right group. Please point me to alternatives if it
isn't.

I'm playing with the Linq To Sql designer in VWD Orcas, and am looking at
how to apply custom paging to a GridView for display on a web page. I want
to use <a href> for paging because I don't like the javascript links that
the GridView employs with it's built-in paging. The following works:

protected void Page_Load(object sender, EventArgs e)
{
int startRow = Convert.ToInt32(Request.QueryString["StartRow"]);
PageProducts(startRow);
}

void PageProducts(int startRow)
{
NorthwindDataContext db = new NorthwindDataContext();

var query = from p in db.Products
    where p.Order_Details.Count > 2
    select new
    {
       ID = p.ProductID,
       Name = p.ProductName,
       NumOrders = p.Order_Details.Count,
       Revenue = p.Order_Details.Sum(o => o.UnitPrice * o.Quantity)
    };

int totalRecords = query.Count();
int pageSize = 10;
int totalPages = totalRecords/pageSize;
if (totalRecords % 10 > 0)
{
totalPages += 1;
}
StringBuilder sb = new StringBuilder();
for(int i = 0; i < totalPages; i++)
{
int pageNo = i + 1;
sb.Append("<a href=\"Paging.aspx?StartRow=" + pageSize * i + "\">" +
pageNo.ToString() + "</a> ");
}

Literal1.Text = "Page: " + sb.ToString();

GridView1.DataSource = query.Skip(startRow).Take(10);
GridView1.DataBind();

}

But I am guessing that it causes two calls to the database: one to populate
totalRecords, which gets all the records for calculating the paging html,
and the second that retrieves just the records for display. Would I be
right in my guess, and if so, is there a more efficient way of obtaining the
count?