Remove reference navigation keys from ORDER BY

[stevendarby]

In the below, adding [p].[PersonId] to the ORDER BY is (possibly) redundant as there is only one value per Blog, whose key came first in the ORDER BY.

var blogs = context.Blogs
    .Include(x => x.Owner)
    .Include(x => x.Posts)
    .ToList();    

SELECT [b].[BlogId], [b].[OwnerId], [b].[Url], [p].[PersonId], [p].[Name], [p0].[PostId], [p0].[BlogId], [p0].[Content], [p0].[Title]
FROM [Blogs] AS [b]
INNER JOIN [Person] AS [p] ON [b].[OwnerId] = [p].[PersonId]
LEFT JOIN [Post] AS [p0] ON [b].[BlogId] = [p0].[BlogId]
ORDER BY [b].[BlogId], [p].[PersonId]

Full code

using Microsoft.EntityFrameworkCore;

using (MyDbContext context = new())
{
    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();

    var blogs = context.Blogs
        .Include(x => x.Owner)
        .Include(x => x.Posts)
        .ToList();    
}

public class MyDbContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
            .UseSqlServer("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=ReferenceOrderBy;Integrated Security=SSPI");    
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }

    public int OwnerId { get; set; }
    public Person Owner { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

public class Person
{
    public int PersonId { get; set; }
    public string Name { get; set; }

    public List<Blog> OwnedBlogs { get; set; }
}

This is also relevant for projection, e.g. if the query is changed to

var blogs = context.Blogs
    .Select(blog => new
    {
        blog.Url,
        Owner = new { blog.Owner.Name },
        Posts = blog.Posts.Select(post => new { post.Title })
    })
    .ToList();

SELECT [b].[Url], [p].[Name], [b].[BlogId], [p].[PersonId], [p0].[Title], [p0].[PostId]
FROM [Blogs] AS [b]
INNER JOIN [Person] AS [p] ON [b].[OwnerId] = [p].[PersonId]
LEFT JOIN [Post] AS [p0] ON [b].[BlogId] = [p0].[BlogId]
ORDER BY [b].[BlogId], [p].[PersonId]

Also note that in split query mode, all reference navigations are included in each split query (#29182) and so these redundant ORDER BYs are also repeated in each split query.

[roji]

Thanks @stevendarby.

Note that we also have #29171, which is about removing all orderings. However, we're unlikely to do that in all cases, since that wouldn't leave any way to stream query results; for example, we may leave orderings for the non-tracked query case, in which case implementing this optimization may still be worth it.