Remove ORDER BY applied to singleton queries

[ranma42]

When a (sub)query results in 0 or 1 elements (First(), Last(), FirstOrDefault(), LastOrDefault(), Take(1), ...), OrderBy/ThenBy has no effect and can be ignored. This can avoid a pushdown and in general more complex queries.

An example that could be improved:

// @nuget: Microsoft.EntityFrameworkCore.Sqlite -Version 8.0.8

using System;
using System.Data;
using System.Linq;
using Microsoft.EntityFrameworkCore;

using var db = new BloggingContext();

var query = (from blogs in db.Blogs
	select (from posts in blogs.Posts select posts).OrderBy(e => e.SortKey).Take(1).OrderBy(e => e.Id).Single().Id);

Console.WriteLine(query.ToQueryString());

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options
            .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
            .UseSqlite($"Data Source=test.db");
}

public class Blog
{
    public int Id { get; set; }
    public Post[] Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public Blog Blog { get; set; }
    public int SortKey { get; set; }
}

In this case the SQL query is

SELECT (
    SELECT "t"."Id"
    FROM (
        SELECT "p"."Id", "p"."BlogId", "p"."SortKey"
        FROM "Posts" AS "p"
        WHERE "b"."Id" = "p"."BlogId"
        ORDER BY "p"."SortKey"
        LIMIT 1
    ) AS "t"
    ORDER BY "t"."Id"
    LIMIT 1)
FROM "Blogs" AS "b"

but it could simply be

SELECT (
        SELECT "p"."Id"
        FROM "Posts" AS "p"
        WHERE "b"."Id" = "p"."BlogId"
        ORDER BY "p"."SortKey"
        LIMIT 1)
FROM "Blogs" AS "b"

This can combine with #34482 to further simplify queries.