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:More...