I won't bother with a detailed description of what LINQ to SQL is. Mr. Guthrie does an excellent job of that himself in his series of articles. Suffice to say that it allows us to query data sources (or data contexts) using a set of standard query operators in a .Net language of your choice. I will be using the Northwind Sql Server database as the basis for my data context for this example. At the moment, the Linq to Sql Classes designer only supports Sql Server 2000 and 2005 (including Express). To bring LINQ to SQL into play, just go to 'Add New Item...' after right-clicking on the name of your project in Solution Explorer, and select LINQ to SQL Classes. Change the name from DataClasses.dbml to Northwind.dbml.
Next, drag the Orders, Order Details, Categories and Products tables from the Server Explorer pane onto the designer. The result will appear as follows, and in the meantime, the IDE will be busy creating entity classes for each of the tables.
Now that the hard work is out of the way, create a new Web Form called CustomPaging.aspx, and drag a GridView and a Literal control onto it.
Now, the code-behind. we'll use the query that retrieves 4 columns of data (or propeties of the generated classes) that's provided in Scott Gu's blog entry:
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)
};
The above LINQ Query Syntax uses the new standard LINQ query operators which are made available with the 2008 versions of Visual Studio and Visual Web Developer. It's a declarative shorthand which is compiled into explicit method invocation code against the entity classes created by the designer. The compiled code utilises the new 3.5 framework Extension Methods and Lambda Expressions. Much more about this can be read here.
The query syntax will be translated to an SQL statement, and without knowing too much about the LINQ standard operators or query syntax, you may already guess that it will return the ProductID under an alias of ID, the ProductName (Name), the total of Orders for each product (NumOrders) and the revenue per product calculated by multiplying the unit price per product by the quantity sold for all orders(Revenue). The result will be returned to an IEnumerable<Product> called query, which can be bound to any control that supports IEnumerable. At the moment, though, the code won't do anything, because execution is deferred until you actually try to do something with the results, such as iterate over them. Consequently, by adding the following lines:
GridView1.DataSource = query;
GridView1.DataBind();
The code will be executed at the point that DataBind() is called, and not before. However, that does not really serve our purpose. If the GridView is configured for paging, the default Javascript links will appear, and if it isn't, the entire results will appear. Our current GridView is not configured for anything. So we will implement the paging within the code-behind.
LINQ has many very useful operators, two of which are Skip() and Take(). These come into their own with scenarios like paging, because they are translated to the use of ROW_NUMBER with Sql Server 2005, which allows you to skip a certain number of rows, and only take a limited number from that point. This basically means that if you only want to show 10 records per page, you only need to select 10 records, and not the entire resultset as is the default for the GridView and a SqlDataSource, for example. So we need to add some code to calculate which page we are on so that we can tell the database how many rows to skip and take. We also need to know how many total pages there are in the results of the query. The following does that:
int totalRecords = query.Count();
int pageSize = 10;
int totalPages = totalRecords/pageSize;
totalRecords uses the Count() method. LINQ will actually translate that to a SELECT COUNT(*) query when it is run against the database. intPageSize sets the number of records to be displayed per page. intTotalPages calculates the total number of pages required to show all the records, and is simply the result of the number of records divided by the page size returned as a whole number.
Now that we have that, we just need to create the html for the paging:
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=\"CustomPaging.aspx?StartRow=" + pageSize * i + "\">" + pageNo.ToString() + "</a> ");
}
If the modulo of the total number of records divided by the number of pages is greater than 0, we need to add a page to the total to cater for the extra records. For example, assume the totalRecords value is 73. 73 mod 10 is 3, and those extra 3 records need to be shown on the 8th page, so that needs to be added to the total because totalRecords/pageSize returned 7, being the whole number part of the result from the calculation. From there, a StringBuilder is used within a loop to build the html that will contain the paging links. I know there are only 70-odd records in this example, so I have not catered for a situation where large numbers of pages could be returned. Additional logic could be added for this, ensuring that a vast number of paging links don't spread across the page.
You will notice that the paging links include a query string value, StartRow, and each time the loop runs, the value of this is set to pageSize(10) * the value of the loop counter. This provides the method with the row number to start taking records from.
Now, the records need to be bound to the GridView, and the paging links applied to the Literal control:
Literal1.Text = "Page: " + sb.ToString();
GridView1.DataSource = query.Skip(startRow).Take(10);
GridView1.DataBind();
The Take() method is passed the value of pageSize, in this case 10, which tells LINQ to fetch 10 rows at a time. The Skip method is passed another variable - startRow which is the QueryString["StartRow"] value. So all that remains to be done is to wrap this code in a method which accepts an int, and then to call the method during Page_Load(). Here's the final code:
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=\"CustomPaging.aspx?StartRow=" + pageSize * i + "\">" + pageNo.ToString() + "</a> ");
}
Literal1.Text = "Page: " + sb.ToString();
GridView1.DataSource = query.Skip(startRow).Take(10);
GridView1.DataBind();
Command.Text = "<p>" + db.GetCommand(query.Skip(startRow).Take(10)).CommandText + "</p>";
This is a nice efficient way of paging results, which is also search engine friendly. However, there is still one small problem. Two queries are executed against the database: one to get the 10 rows of data, and another to get the total number of records. This is no worse than using the new LinqDataSource and built-in paging with the GridView, which does exactly the same thing. But if you are concerned with minimising database calls, it is a simple matter to store the value of totalRecords in an Application level variable, and use that instead. Any code that might alter the totalRecords value, such as an Insert, Update or Delete can be finished off with setting the value of the Application variable to 0, and the next time the page is run, it will have the job of repopulating the variable:
int totalRecords;
if (Convert.ToInt32(Application["count"]) == 0)
{
totalRecords = query.Count();
Application["count"] = totalRecords;
}
else
{
totalRecords = Convert.ToInt32(Application["count"]);
}
