Linq – Subsonic 3.0.0.3 SQL Paging using Linq

linqpagingsubsonicsubsonic3

Have just updated from Subsonic 2.2 ActiveRecord to 3.0.0.3. I am trying to use LINQ to do a paged Find query like this (my object/table is called "Repository"):

Repository.Find(item => item.DocumentTitle.Contains(searchTerm))
    .OrderBy(i => i.DocumentTitle).Skip((currentPage - 1) * itemsPerPage)
    .Take(itemsPerPage);

When I view the SQL generated by this query using SQL Server Profiler, there is no paging in the SQL, all the paging is being done in memory in C#. Now, the Subsonic query language does have a nice GetPaged procedure that does work right, but I thought that LINQ was supposed to do this as well. Have I missed something here or is this a limitation of LINQ?

I am aware of the Repository.GetPaged() function, but that doesn't have enough parameters – I need to do a dynamic sort, as well as a Find().

Best Answer

Upon doing further testing, this statement works correctly:

(from i in dataContext.Repositories 
 where i.DocumentTitle.Contains(searchTerm) 
 orderby i.DateCreated ascending select i)
 .Skip((currentPage - 1) * itemsPerPage).Take(itemsPerPage);

When executed, the above linq statement comes back properly paged in sql.

The only conclusion that I can come to is that when you are using method chaining syntax, once you are outside the initial lamda expression

Repository.Find(item => item.DocumentTitle.Contains(searchTerm))

the subsonic SQL interpreter stops creating SQL for any methods chained on the end

.OrderBy(i => i.DocumentTitle).Skip(15).Take(10);

Or, am I just totally doing something wrong here? Anybody have some insight?