c# - 使 Entity Framework (使用 Linq 查询)使用自定义字段的别名而不是重做子查询

Question

有没有办法强制 Entity Framwork 使用子查询的别名而不是将其加倍用于 OrderBy？

var results = Users.Select( u => new 
  { 
    u.idUser, 
    u.Name, 
    nbChilds = u.Children.Count 
  })
  .OrderBy( u => u.nbChilds );

这将在 SQL 中转换为:

SELECT [u].[idUser] AS [idUser], [u].[name] AS [Name], (
    SELECT COUNT(*)
    FROM [Children] AS [c0]
) AS [nbChilds]
FROM [Users] AS [u]
ORDER BY (
    SELECT COUNT(*)
    FROM [Children] AS [c]
)

这意味着它将对 child 计数两次。就我而言，这是我想避免的对性能的严重影响。有没有办法告诉 Linq 在选择中使用别名，这样输出将是:

SELECT [u].[idUser] AS [idUser], [u].[name] AS [Name], (
    SELECT COUNT(*)
    FROM [Children] AS [c0]
) AS [nbChilds]
FROM [Users] AS [u]
ORDER BY [nbChilds]

Answer 1

EF6 在生成此查询方面做得很好。 EF Core 重复 ORDER BY 中的子查询，这对于 SQL Server 至少会导致更昂贵的计划。

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

这是我们希望它生成的 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, 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.