Building Custom Paging and Sorting Queries with LINQ to SQL and wrap it in Extension Method
Introduction:
Since my early days with web application and I used to observe people writing different techniques for paging data coming from the database. Many articles where written, and different techniques for different databases. All that I am talking about paging data on the database before returning results to the application which will renders it to the client.
One Technique:
You might be familiar with queries like this one:
1: SELECT TOP (10) [R1].[ProductID], [R1].[ProductName]
2: FROM (
3: SELECT TOP (77) [R0].[ProductID], [R0].[ProductName]
4: FROM [dbo].[Products] AS [R0]
5: ORDER BY [R0].[ProductID] DESC
6: ) AS [R1]
7: ORDER BY [R1].[ProductID]
The above query is one of the techniques used for paging data on the database side. When wrapping this query with a stored procedure it will look like this:
1: CREATE PROCEDURE [dbo].[usp_Product_GETPAGE]
2: (
3: @page int,
4: @pagelength int,
5: @sortfield varchar(100),
6: @descending bit,
7: @rowcount int output
8: )
9: AS
10: BEGIN
11: SET NOCOUNT OFF
12: DECLARE @Err int
13: SELECT @rowcount = COUNT(*) from [Products]
14: declare @innerrows int
15: declare @sortdesc varchar(100)
16: declare @sortasc varchar(100)
17: declare @a varchar(6)
18: declare @b varchar(6)
19: IF @descending=0
20: BEGIN
21: set @a = ' DESC '
22: set @b = ' ASC '
23: END
24: ELSE
25: BEGIN
26: set @a = ' ASC '
27: set @b = ' DESC '
28: END
29: IF charindex(@sortfield, ' [ProductID]') > 0
30: BEGIN
31: set @sortdesc = ''
32: set @sortasc = ''
33: END
34: ELSE
35: BEGIN
36: set @sortdesc = ', [ProductID] ' + @a
37: set @sortasc = ', [ProductID] ' + @b
38: END
39: set @innerrows = @rowcount - (@page * @pagelength)
40: DECLARE @sql nvarchar(1000)
41: SET @sql = 'SELECT TOP ' + STR(@pagelength) + ' [ProductID], [ProductName] FROM
42: (
43: SELECT TOP ' + STR(@innerrows) + ' [ProductID],
44: [ProductName]
45: FROM
46: [Products]
47: ORDER BY [Products].' + @sortfield + @a + @sortdesc + '
48: ) Alias
49: ORDER BY Alias.' + @sortfield + @b + @sortasc
50: EXEC (@sql);
51: END
The above procedure is somehow complex because it support paging and that is why it build the SQL statement and use EXEC function.
I'll not discuss its performance; my target is to discuss how to convert this query into LINQ query using extension methods then generalize it to be as an Extension Method for IQueryable Interface.
LINQ Equivalent:
The LINQ Equivalent is very simple using Extension Methods of the IQueryable and IOrderedQueryable interfaces. The following code shows that:
1: //Page Size
2: int pageSize = 10;
3: //Page Index
4: int page = 0;
5: //Total rows
6: int rowsCount = ndc.Products.Count();
7: //Calculate inner query rows
8: int innerRows = rowsCount - (page * pageSize);
9: //Build the Query
10: IEnumerable<Product> query = ndc.Products.OrderByDescending(p => p.ProductID)
11: .Take(innerRows).OrderBy(p => p.ProductID)
12: .Take(pageSize);
The above LINQ to SQL assumes that there is a DataContext for northwind database. This LINQ statement doesn't apply sorting, but when we wrap it with Extension Method we will make it support for that.
I've build a simple Consol Application that shows how this work. And it also display the Generated SQL statement by LINQ at runtime, below is the generated SQL:
SELECT COUNT(*) AS [value]
FROM [dbo].[Products] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
SELECT TOP (10) [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued]
FROM (
SELECT TOP (77) [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
ORDER BY [t0].[ProductID] DESC
) AS [t1]
ORDER BY [t1].[ProductID]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8Below is a screen shot from the running sample:
Wrapping with Extension Method:
After making this, I thought to wrap it with extension method to support sorting and make it generic to be able to use it with any entity when using LINQ to SQL. Because when I used to build this with SQL Stored Procedure, I had to build a procedure for each entity. Now I can do it using LINQ to SQL without the need to build special paging procedure for each table -entity- I have.
Below is the Extension Method that wraps the Custom Paging for LINQ to Sorting and support sorting:
1: public static class LinqExtensions
2: { 3: /// <summary>
4: /// Perform custom paging using LINQ to SQL
5: /// </summary>
6: /// <typeparam name="T">Type of the Datasource to be paged</typeparam>
7: /// <typeparam name="TResult"></typeparam>
8: /// <param name="obj">Object to be paged through</param>
9: /// <param name="page">Page Number to fetch</param>
10: /// <param name="pageSize">Number of rows per page</param>
11: /// <param name="keySelector">Sorting Expression</param>
12: /// <param name="asc">Sort ascending if true. Otherwise descending</param>
13: /// <param name="rowsCount">Output parameter hold total number of rows</param>
14: /// <returns>Page of result from the paged object</returns>
15: public static IQueryable<T> Page<T, TResult>(this IQueryable<T> obj, int page, int pageSize,
16: System.Linq.Expressions.Expression<Func<T, TResult>> keySelector,
17: bool asc, out int rowsCount)
18: { 19: rowsCount = obj.Count();
20: int innerRows = rowsCount - (page * pageSize);
21: if(asc)
22: { 23: return obj.OrderByDescending(keySelector).Take(innerRows)
24: .OrderBy(keySelector).Take(pageSize).AsQueryable();
25: }
26: else
27: return obj.OrderBy(keySelector).Take(innerRows).
28: OrderByDescending(keySelector).Take(pageSize).AsQueryable();
29: }
30: }
Next Step:
I'll post soon another sample using ASP.NET 3.5 to apply custom paging using LINQ to SQL, ListView, DataPager and ObjectDataSource. Then will follow it with the same sample but will not ObjectDataSource and apply custom paging with DataPager.
Conclusion:
As I mentioned above, I'm not talking or evaluating performance. I'm showing how much you can save of queries by applying LINQ to SQL custom paging. Click here to download the sample
