Is there a way to force Entity Framwork to use the alias of a subquery instead of doubling it for an OrderBy?
var results = Users.Select( u => new
{
u.idUser,
u.Name,
nbChilds = u.Children.Count
})
.OrderBy( u => u.nbChilds );
This will be converted in SQL to :
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]
)
This means that it will run the count of children twice. In my case, that is a serious hit on performance that I'd like to avoid. Is there a way to tell Linq to use the alias in the select so the output would be :
SELECT [u].[idUser] AS [idUser], [u].[name] AS [Name], (
SELECT COUNT(*)
FROM [Children] AS [c0]
) AS [nbChilds]
FROM [Users] AS [u]
ORDER BY [nbChilds]
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.
Actually it is converted to SQL like this (if it was Linq To SQL):
SELECT[t2].[idUser], [t2].[Name], [t2].[value] AS [nbChilds]
FROM (
SELECT[t0].[idUser], [t0].[Name],
(SELECT COUNT(*)
FROM [Children] AS [t1]
WHERE [t1].[idUser] = [t0].[idUser]
) AS[value]
FROM[Customers] AS[t0]
) AS[t2]
ORDER BY[t2].[value]
However if you traced it that converting as the one you showed then you could do:
var results = Users.Select( u => new
{
u.idUser,
u.Name,
nbChilds = u.Children.Count
})
.AsEnumerable()
.OrderBy( u => u.nbChilds );
That would be converted to:
SELECT [t0].[idUser], [t0].[Name], (
SELECT COUNT(*)
FROM [Children] AS [t1]
WHERE [t1].[idUser] = [t0].[idUser]
) AS [nbChilds]
FROM [Customers] AS [t0]