[Source Code]-Make Entity Framework (using Linq queries) use alias of custom field instead of redoing the subquery-LINQ,C#

EF6 did a good job generating this query. EF Core repeates the subquery in the ORDER BY, which for SQL Server at least causes a more expensive plan.

SQL Generation remains an active area of improvement in EF Core. Please file an issue against EF Core for this: https://github.com/aspnet/EntityFrameworkCore/issues

In EF 6 query like this

        var q = from b in db.Blogs
                orderby b.Posts.Count
                select new
                {
                    b.Name,
                    NumPosts = b.Posts.Count
                };

        var l = q.ToList();

Is translated like this:

SELECT
    [Project2].[Id] AS [Id],
    [Project2].[Name] AS [Name],
    [Project2].[C2] AS [C1]
    FROM ( SELECT
        [Project1].[Id] AS [Id],
        [Project1].[Name] AS [Name],
        [Project1].[C1] AS [C1],
        (SELECT
            COUNT(1) AS [A1]
            FROM [dbo].[Posts] AS [Extent3]
            WHERE [Project1].[Id] = [Extent3].[BlogId]) AS [C2]
        FROM ( SELECT
            [Extent1].[Id] AS [Id],
            [Extent1].[Name] AS [Name],
            (SELECT
                COUNT(1) AS [A1]
                FROM [dbo].[Posts] AS [Extent2]
                WHERE [Extent1].[Id] = [Extent2].[BlogId]) AS [C1]
            FROM [dbo].[Blogs] AS [Extent1]
        )  AS [Project1]
    )  AS [Project2]
    ORDER BY [Project2].[C1] ASC

But on EF Core the subquery is repeated in the ORDER BY clause:

Here's a repro

using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;

namespace EfCoreTest
{

    public class Category
    {
        public int CategoryId { get; set; }
        public virtual ICollection<Product> Products { get; } = new HashSet<Product>();
    }
    public class Product
    {
        public int ProductId{ get; set; }
        public string Name { get; set; }
        public DateTime Date { get; set; }
        public Category Category { get; set; }


    }

    public class Db : DbContext
    {
        public DbSet<Category> Categorys { get; set; }
        public DbSet<Product> Products { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("server=.;database=EfCoreTest;Integrated Security=true");
            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }



    class Program
    {



        static void Main(string[] args)
        {

            using (var db = new Db())
            {

                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                for (int i = 0; i < 100; i++)
                {
                    var t = new Category();

                    for (int j = 0; j < 1000; j++)
                    {
                        var product = new Product()
                        {
                            Category = t,
                            Date = DateTime.Now,
                            Name = $"Category {j}{i}"

                        };
                        db.Add(product);
                    }
                    db.Add(t);


                }
                db.SaveChanges();

            }
            using (var db = new Db())
            {
                var q = from c in db.Categorys
                        orderby c.Products.Count
                        select new
                        {
                            c.CategoryId,
                            ProductCount = c.Products.Count
                        };

                var l = q.ToList();

                Console.WriteLine("Hit any key to exit.");
                Console.ReadKey();

            }

        }
    }
}

Here's the SQL it generates

SELECT [c].[CategoryId], (
    SELECT COUNT(*)
    FROM [Products] AS [p0]
    WHERE [c].[CategoryId] = [p0].[CategoryId]
) AS [ProductCount]
FROM [Categorys] AS [c]
ORDER BY (
    SELECT COUNT(*)
    FROM [Products] AS [p]
    WHERE [c].[CategoryId] = [p].[CategoryId]
)

and the Time and IO stats for that query:

(100 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Categorys'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Products'. Scan count 2, logical reads 960, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 43 ms.

Here's the SQL we wish it generated:

SELECT [c].[CategoryId], (
    SELECT COUNT(*)
    FROM [Products] AS [p0]
    WHERE [c].[CategoryId] = [p0].[CategoryId]
) AS [ProductCount]
FROM [Categorys] AS [c]
ORDER BY (
    [ProductCount]
)

And the Time and IO stats:

(100 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Categorys'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Products'. Scan count 1, logical reads 480, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 20 ms.