Subsonic 3.0.0.3 SQL Paging Using Linq

Question

Just updated from Subsonic 2.2 ActiveRecord to 3.0.0.3. I am trying to use LINQ to execute a query with a search query (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 look at the SQL generated by this query using the SQL Server Profiler, there is no paging in SQL, all the paging is done in memory in C #. Now, the Subsonic query language has a good GetPaged procedure that works correctly, but I thought LINQ should have done this. Am I missing something or is this a LINQ constraint?

I know the function Repository.GetPaged(), but it does not have enough parameters - I need to do dynamic sorting as well Find().

Answer 1

With 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 is returned correctly, unloaded in sql.

The only conclusion I can come to is that when you use the method chain syntax, once you are outside the original lamda expression

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

subsonic SQL interpreter stops creating SQL for any methods encoded at the end

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

Or am I just doing something completely wrong? Does anyone have an understanding?

Answer 2

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

- SQL , . ? "ToList()", , .

Answer 3

, ...

Repository.Find()

IList, , SQL ,

.Skip(x).Take(x)

executed in memory. Try

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

all of which return IQueryable and do not include an enumeration of objects, and therefore paging is performed in SQL using the ROW_NUMBER () function.

Having said that Subsonic 3 simple repository generates 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.

therefore, it would seem that there is an error in Subsonic: - (