Entity Framework Core; using ORDER BY in query against a (MS) SQL Server

Question

I'm trying to use the following query in combination with Entity Framework Core against a Microsoft SQL Server 2016:

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

I use this query like so:

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

I use this in a .NET Core REST API with pagination and I'd like to have the records sorted (alphabetically) to make the pagination more usable. I get the following error when executing the above 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. 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.

Looking for similar issues I found these some other posts (1, 2, 3) but none of which where used in combination with EF Core and/or they were using it in a different context which does not apply in my case (e.g. subquery).

I tried to use the .OrderBy(..) syntax of EF instead of in the ORDER BY in the query but this doesn't solve the problem. I also tried adding TOP 100 PERCENT after the SELECT in the query in combination with the ORDRE BY; this worked but didn't order the column. It just got ignored. This limitation is described under the EF Limitations. I also found this post that replace the TOP 100 PERCENT... with TOP 99.99 PERCENT... or TOP 9999999... `. This seems like it should work but it doesn't 'feel' right. This issue in general is further explained 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.

Further I'm confused by the word "view". For me, the term views refers to the usage of the ones created by the CREATE VIEW .. syntax. Is a plain, 'normal' SQL query also considered a view? Or is EF Core wrapping the request in some sort of view and this is the real issue causing this error?

I'm not sure, but so far all the 'solutions' I found seem kind of 'hacky'. Thoughts?

Solution

Let's simplify things a bit. Here's what I came up for testing. I've also added 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}";
}

When we look at the value of generated we see what the sql of the query is:

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]

Notice that there three order by clauses, the inner-most one is the one from rawSql.

We can look at the error message to see why it's not legal:

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

The middle order by does include offset, so that's valid even though it's inside a subquery.

To fix this, just simply remove the ordering from your rawSql and keep using the OrderBy() linq method.

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

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

This generates:

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]

Now, all order by clauses are either not in subqueries, or have an offset clause.

Comments

[товіаѕ]

this is a fantastic answer, thank you. the Problem is only that I want to order it first before skipping and taking, because I'd like the whole result set ro basically seem sorted

[товіаѕ]

imagine this: xzy-acb-oqp vs. xyz-abc-opq vs. abc-opq-xyz

[gunr2171]

Simply reorder the linq methods: .FromSql(rawSql) .OrderBy(x => x.Title) .Skip(1) .Take(4);

[товіаѕ]

I tried this already, yet without success. I still get the same exception. But I get a good idea on how to solve the issue. I could, in theory, just add an OFFSET 0 to my inner query, couldn't I? This would fulfil the requirements for a subquery. Also, do you know why dis limitation exists? I don't see why I shouldn't be able to order a result without declaring an offset for it

[товіаѕ]

I tested it again and you're right. The OrderBy(..) does work; tbh I don't know why it didn't during my test phase, maybe I forgot to reset something. Anyhow, I also tested my assumption about OFFSET 0 ROWS - it works. This means that there are two good solutions to this problems, the first being the OrderBy(..) and the second one to use ORDER BY [x] OFFSET 0 ROWS. I'll extend your answer and accept it