c# - Entity Framework 核心；在针对 (MS) SQL Server 的查询中使用 ORDER BY

Question

我正在尝试将以下查询与 Entity Framework Core 结合使用以针对 Microsoft SQL Server 2016:

SELECT [a], [b], [c]
FROM [x]
WHERE [a] = {0}
ORDER BY  [b]

我这样使用这个查询:

context.MySet.AsNoTracking()
  .FromSql(MyQuery, aValue)
  .Skip(pageSize * page)
  .Take(pageSize)
  .Select(x => x.ToJsonDictionary())
  .ToList()

我在带有分页的 .NET Core REST API 中使用它，我希望对记录进行排序(按字母顺序)以使分页更有用。执行上述语句时出现以下错误:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.Invalid usage of the option NEXT in the FETCH statement. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.Invalid usage of the option NEXT in the FETCH statement.

在寻找类似问题时，我发现了其他一些帖子(1、2、3)，但没有一个与 EF Core 结合使用和/或他们在不同的上下文中使用它不适用于我的情况(例如子查询)。

我尝试使用 EF 的 .OrderBy(..) 语法而不是查询中的 ORDER BY ，但这并没有解决问题。我还尝试在查询中的 SELECT 之后添加 TOP 100 PERCENT 并结合 ORDRE BY;这有效但没有订购专栏。它只是被忽略了。此限制在 EF Limitations 下进行了描述.我还找到了this post将 TOP 100 PERCENT... 替换为 TOP 99.99 PERCENT... 或 TOP 9999999... `。这似乎应该有效，但它“感觉”不对。这个问题一般有进一步解释here .

Summary: It is not advisable to use ORDER BY in Views. Use ORDER BY outside the views. In fact, the correct design will imply the same. If you use TOP along with Views, there is a good chance that View will not return all the rows of the table or will ignore ORDER BY completely.

此外，我对“ View ”一词感到困惑。对我来说，术语 View 是指使用 CREATE VIEW .. 语法创建的 View 。普通的“普通”SQL 查询是否也被视为 View ？或者 EF Core 是否将请求包装在某种 View 中，这是导致此错误的真正问题？

我不确定，但到目前为止，我发现的所有“解决方案”似乎都有些“hacky”。想法？

Answer 1

让我们稍微简化一下。这是我想来进行测试的。我还添加了 some code for printing the generated sql from EF queries .

class Program
{
    static void Main(string[] args)
    {
        DbClient context = new DbClient();

        var rawSql = "select [Id], [Title] from Post order by [Title]";

        var query = context.Posts.AsNoTracking()
            .FromSql(rawSql)
            .Skip(1)
            .Take(4)
            .OrderBy(x => x.Title);

        var generated = query.ToSql();

        var results = query.ToList();
    }
}

class DbClient : DbContext
{
    public DbSet<Post> Posts { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("conn_string");
    }
}

class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public override string ToString() => $"{Id} | {Title}";
}

当我们查看 generated 的值时，我们会看到 query 的 sql 是什么:

SELECT [t].[Id], [t].[Title]
FROM (
    SELECT [p].[Id], [p].[Title]
    FROM (
        select [Id], [Title] from Post order by [Title]
    ) AS [p]
    ORDER BY (SELECT 1)
    OFFSET 1 ROWS FETCH NEXT 4 ROWS ONLY
) AS [t]
ORDER BY [t].[Title]

请注意，有三个 order by 子句，最里面的一个是来自 rawSql 的子句。

我们可以查看错误信息来了解为什么它不合法:

The ORDER BY clause is invalid in [...] subqueries [...] unless OFFSET [...] is also specified.

的中间顺序确实包含偏移量，因此即使它在子查询中也是有效的。

要解决此问题，只需从 rawSql 中删除排序并继续使用 OrderBy() linq 方法即可。

var rawSql = "select [Id], [Title] from Post";

var query = context.Posts.AsNoTracking()
    .FromSql(rawSql)
    .Skip(1)
    .Take(4)
    .OrderBy(x => x.Title);

这会产生:

SELECT [t].[Id], [t].[Title]
FROM (
    SELECT [p].[Id], [p].[Title]
    FROM (
        select [Id], [Title] from Post
    ) AS [p]
    ORDER BY (SELECT 1)
    OFFSET 1 ROWS FETCH NEXT 4 ROWS ONLY
) AS [t]
ORDER BY [t].[Title]

现在，所有 order by 子句要么不在子查询中，要么有一个 offset 子句。