Friday, November 30, 2007

Paging With LINQ

Here is a simple C#3.0 extension method to page LINQ queries. It will allow you to declare where in the results you would like to start, and then how many results to return from that point.


public static class PagingExtensions
{
//used by LINQ to SQL
public static IQueryable<TSource> Page<TSource>(this IQueryable<TSource> source, int page, int pageSize)
{
return source.Skip((page - 1)*pageSize).Take(pageSize);
}

//used by LINQ
public static IEnumerable<TSource> Page<TSource>(this IEnumerable<TSource> source, int page, int pageSize)
{
return source.Skip((page - 1)*pageSize).Take(pageSize);
}

}

class Program
{
static void Main(string[] args)
{
List<string> names = new List<string>();
names.AddRange(new string[]{"John","Frank","Jeff","George","Bob","Grant", "McLovin"});
foreach (string name in names.Page(2, 2))
{
Console.WriteLine(name);
}

}
}


Since counting starts at 1, the above example will take Jeff and George

15 comments:

w!ldc@t said...

How about BIG data? If i need work with 1 000 000 records. In this case i can use linq?

Anonymous said...

I think there is a bug here.

it should be

Skip(Page * PageSize).Take(PageSize)

Please comment if i am wrong with skip..

Thanks
Atul

K Jacobson said...

I don’t think there is a bug there. You need to pass into the skip the position of where you want to start in the results. The developer using this function would have to be responsible for keeping track of that position.

If we multiplied the start position by the page size, we wouldn’t get the correct start position.

If would used the implementation you described with the example above, passing in 2,2 to that function would result in returning ‘Bob’, ‘Grant’

Anonymous said...

...Skip((Page-1)*PageSize).Take(PageSize);

thus 2,2 will give you:

...Skip(1*2).Take(2) which is correct.

the skip number represents the amount of records to bypass.Check out this link
(http://msdn.microsoft.com/en-us/vcsharp/aa336757.aspx#SkipSimple)

K Jacobson said...

Agreed. Thanks for the input! Will make that change to the example.

Nkosi said...
This comment has been removed by the author.
Codebased said...

ho hu it works :-)

Samsudeen B said...

Hi

I also have the same problem and it is solved now

also i have added one more feature to the method to get the number of qualified pages

see the below link
http://samsudeenb.blogspot.com/2008/10/paging-using-linq.html

Patrick said...

Just a small question on paging, its not related to xml rather a ms sql database. I removed all GridView's from our site about a year ago. Reason for this is that for the paging in a gridview to work, you need to select all posts from the database. This slows down performace, lets say that you have 10.000 posts in a forum, then the gridview requires you to load 10.000 posts everytime you switch a page.
Does anyone know if linq behaves the same way with the Skip and Take?
Does it load everything first, then sorts out what it needs?

K Jacobson said...

In the LINQ to SQL case here is the SQL generated to perform a paging operation. This is for a simple Person object with four properties. It appears to filter out the records in the SQL query.

Generated SQL:
SELECT [t1].[Id], [t1].[FirstName], [t1].[MiddleName], [t1].[LastName]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Id], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName]) AS [ROW_NUMBER], [t0].[Id], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName]
FROM [Person] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

Boudewijn van Veen said...

Trying to perform a similar task i ran into the error message: "The Text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator" because one of my columns was of the "Text" type.

(see: http://spsnippets.blogspot.com/2009/07/linq-paging-data-with-text-fields.html)

Any solution for that ?

Anonymous said...

Hi there,

Thanks for this.

I've done lots of paging in the past using CTE in SQL Serv 2005, but am new to LINQ-ENTITIES.

Would the same approach be taken with LINQ to Entities as in this example?

...which would result in a good SQL query.

What is the best way to get the total records count at the same time, for controlling the paging links?

Regards,
Kevin

Max said...

FYI, the IQueryable implementation is redundant since it inherits from IEnumerable...

~Cheers.

K Jacobson said...

Max,

While that statement may be true, the interface used does have an effect on how the query is executed. In the case with the Page extension method, if we got rid of the one that uses the IQueryable interface, that portion of the query would not get run in SQL Server but would get run as a in memory LINQ query. So all the paging logic, in this case, would not happen on SQL Server. In the two examples below, I show you the SQL that gets generated to execute LINQ to SQL query using both Page extension methods.

This first example is a LINQ to SQL query using the Page extension method that uses IQueryable:

SELECT [t1].[ID] AS [Id], [t1].[FirstName], [t1].[LastName]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ID], [t0].[FirstName], [t0].[LastName]) AS [ROW_NUMBER], [t0].[ID], [t0].[FirstName], [t0].[LastName]
FROM [Person] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]


This second example uses Max’s recommendation to just use the IEnumerable as an input parameter.

SELECT [t0].[ID] AS [Id], [t0].[FirstName], [t0].[LastName]
FROM [Person] AS [t0]


As you can see, the paging in the second case will not occur in SQL Server.

Anonymous said...

Thanks a lot...sorry but copied as it is....gr8 and simple

Sandesh Daddi