Subsonic 3.0.0.3使用Linq进行SQL分页

Question

我刚从Subsonic 2.2 ActiveRecord升级到3.0.0.3。我试图使用LINQ来执行分页查找查询，如下所示（我的对象/表名为“Repository”）：

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

当我使用SQL Server Profiler查看此查询生成的SQL时，SQL中没有分页，所有分页都在C#内存中完成。现在，Subsonic查询语言确实有一个不错的GetPaged过程可以正常工作，但我认为LINQ也应该能做到这一点。我错过了什么还是LINQ的局限性？

我知道Repository.GetPaged()函数，但它没有足够的参数 - 我需要进行动态排序和Find()。

- Steve

Answer 1

在进行进一步的测试后，这个语句是正确的：

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

执行上述linq语句时，sql已经正确分页。

我唯一能得出的结论是，在使用方法链接语法时，一旦超出初始lamda表达式范围。

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

子音SQL解释器停止为在末尾链接的任何方法创建SQL。

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

或者，我在这里完全做错了什么吗？有人有一些见解吗？

- Steve

---

进行了更多的研究。显然，这是由于C#编译linq表达式的方式不同。编译器必须在设计时知道语句将是什么。如果不知道，那么该语句无法被SubSonic的“ExpressionVisitor”（这与SubSonic无关）反射，因为它不再是单个“表达式”。 - Steve

有第三方库可以让您将 Linq 语句分成多个步骤构建，但这会给您的项目增加更多外部依赖。SubSonic 实际上似乎已经有一些类似的功能，但我现在还不够了解，无法提出使用它们来完成此任务的建议。 - Steve

Answer 2

您可以通过将“desc”添加到排序字段来对GetPaged进行排序，但是...

分页应该是可行的 - 我正在查看面前的分页SQL，并且它不是在内存中完成的。您是如何测试这个的？如果使用“ToList()”，那么将执行查询 - 然后查看分析器。

- user1151

---

GetPaged函数是理想的，但我还需要它执行Find()操作。所以请称之为FindGetPaged()。这是我从查询中看到的SQL（我也试过ToList<Repository>()，将IEnumerable传递给ListView数据源）：exec sp_executesql N'SELECT [t0].[CategoryName], [t0].[DateCreated], [t0].[DocumentFileType], [t0].[DocumentTitle], [t0].[FileContent], [t0].[RepositoryId] FROM [dbo].[mag_Repository] AS t0 WHERE ([t0].[DocumentTitle] LIKE ''%'' + @p0 + ''%'')',N'@p0 nvarchar(4000)',@p0=N'' - Steve

为了测试它，我附加了VS调试器，仅针对我的DB启动了分析器，然后在此函数处中断：List<Repository> retList = Repository .Find(item => item.DocumentTitle.Contains(searchTerm)) .OrderByDescending(i => i.DocumentTitle) .Skip((currentPage - 1) * itemsPerPage) .Take(itemsPerPage).ToList<Repository>();当我按F10时，观察SQL语句通过分析器传递。 - Steve

我还进行了GetPaged的测试，以确保我正确地看到了SQL，并且它使用RowNumber()分页SQL语句正常运行。所以我不知道我在这里做错了什么。 - Steve

Answer 3

有点晚了，但是...

Repository.Find()

返回IList，以便执行查询，因此SQL在没有分页的情况下执行

.Skip(x).Take(x)

是在内存中完成的。 尝试

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

所有这些都返回IQueryable，没有一个枚举对象，因此分页是在SQL中使用ROW_NUMBER()函数完成的。

话虽如此，Subsonic 3简单仓储正在生成以下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'

抛出异常

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

看起来Subsonic有一个bug :-(

- Scott Rickman

---

我不会说这是SubSonic的错误，因为SubSonic将您的查询转换为普通的SQL。由于异常告诉您ROW_NUMBER必须有一个ORDER BY子句，因此您还必须在linq查询中添加orderby关键字，以便subsonic也生成一个order by（顺便说一下，在这种情况下，mysql不会抛出异常，因为LIMIT（x，y）不需要ORDER BY）。 - Jürgen Steinblock

也许不是一个 bug，我同意。我已经更新了本地的 subsonic 构建版本，以测试是否已经添加了 order by 子句到包含 ROW_NUMBER 函数的任何查询中，如果没有 order by，我将在 ROW_NUMBER 的 OVER 部分插入 ORDER BY Id，从而避免异常。这并不理想，而且它是特定于环境的（所有我的 DOM 类都必须继承 DomBase 并且将具有一个 ID 属性）。 - Scott Rickman