代码之家 › 专栏 › 技术社区 › ÑÐ¾Ð²ÑÐ°Ñ

实体框架核心;对(MS)SQL Server使用ORDER BY in查询

entity-framework-core sql-order-by entity-framework sql-server c#

ÑÐ¾Ð²ÑÐ°Ñ · 技术社区 · 6 年前

我正试图结合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()

我在a中使用这个。NET Core REST API和分页,我希望对记录进行排序(按字母顺序),以使分页更可用。执行上述语句时,我出现以下错误:

ORDER BY子句在视图、内联函数、派生中无效表、子查询和常用表表达式,除非是TOP、OFFSET 也指定了or FOR XML。中的下一个选项的用法无效 FETCH语句。ORDER BY子句在视图内联中无效函数、派生表、子查询和公共表表达式, 除非还指定了TOP、OFFSET或FOR XML。的无效用法 FETCH语句中的选项NEXT。

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

我试着用 .OrderBy(..) EF的语法,而不是 ORDER BY 但这并不能解决问题。我还尝试添加 TOP 100 PERCENT 在 SELECT 在查询中结合 ORDRE BY ; 这起到了作用,但没有对列进行排序。它只是被忽视了。该限制在 EF Limitations . 我还发现 this post 替换 TOP 100 PERCENT... 具有 TOP 99.99 PERCENT... 或 TOP 9999999... `. 这似乎应该奏效,但感觉不对。这一问题将得到进一步解释 here .

摘要:不建议在视图中使用ORDER BY。使用订单依据在视图之外。事实上,正确的设计也意味着同样的道理。如果您将TOP与视图一起使用,视图很有可能不返回表的所有行,或忽略ORDER BY 彻底地

此外,我对“视图”这个词感到困惑。对我来说,视图一词指的是 CREATE VIEW .. 语法。普通的“普通”SQL查询也被视为视图吗?或者EF Core是否在某种视图中包装请求,而这才是导致此错误的真正问题?

我不确定,但到目前为止,我找到的所有“解决方案”似乎都有点“黑客”。思想?

1 回复 | 直到 6 年前

gunr2171 6 年前

让我们把事情简化一点。下面是我要测试的内容。我还添加了 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 是:

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 .

我们可以查看错误消息以了解其不合法的原因:

ORDER BY子句在[…]中无效子查询[…]除非偏移[…]也指定了。

中间顺序由做包括偏移量,这样即使它在子查询中也是有效的。

要解决此问题,只需从 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子句。