c# – Make Entity Framework(使用Linq查询)使用自定义字段的别

SQL生成仍然是EF Core的一个活跃的改进领域.请为此针对EF Core提出问题：https://github.com/aspnet/EntityFrameworkCore/issues

在这样的EF 6查询中

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

        var l = q.ToList();

翻译成这样：

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

但是在EF Core上,子查询在ORDER BY子句中重复：

这是一个复制品

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();

            }

        }
    }
}

这是它生成的SQL

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]
)

以及该查询的时间和IO统计信息：

(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,lob read-ahead reads 0.
Table 'Products'. Scan count 2,logical reads 960,lob read-ahead reads 0.

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

这是我们希望它生成的SQL：

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

以及时间和IO统计数据：

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

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