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