Subsonic 3.0.0.3 SQL Paging using Linq

Question

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().

Answer 1

You can sort GetPaged by adding "desc" to the sort field, but...

Paging should work - I'm looking at the paging SQL in front of me and it's not done in memory. How are you testing this? If you use "ToList()" that will execute the query - have a look at the profiler then.

Answer 2

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?

Answer 3

A bit late but ...

Repository.Find()

returns IList so the query is executed, hence SQL executing without paging then

.Skip(x).Take(x)

is done in memory. Try

Repository.All().Where(expression).Skip(x).Take(x)

all of which return IQueryable and non of which enumerate the objects and so paging is done in SQL using the ROW_NUMBER() function.

Having said that Subsonic 3 simple repository is generating the following SQL

exec sp_executesql N'SELECT [t0].[Id], [t0].[IsDeleted], [t0].[Name], [t0].[ParentUuid], [t0].[Uuid]
FROM ( SELECT [t1].[Id], [t1].[IsDeleted], [t1].[Name], [t1].[ParentUuid], ROW_NUMBER() OVER() AS rownum, [t1].[Uuid]
FROM [Sites] AS t1
WHERE (([t1].[ParentUuid] = @p0) AND ([t1].[IsDeleted] = 0))) AS t0
WHERE [t0].[rownum] BETWEEN (20 + 1) AND (20 + 10)',N'@p0 uniqueidentifier',@p0='00000000-0000-0000-0000-000000000000'

which throws an exception

Unhandled Exception: System.Data.SqlClient.SqlException: The ranking function "ROW_NUMBER" must have an ORDER BY clause.

so it would seem that there is a bug in Subsonic :-(