Description
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.