score:1
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.
score:0
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]
Source: stackoverflow.com
Related Articles
- Make Entity Framework (using Linq queries) use alias of custom field instead of redoing the subquery
- SELECT NEW with a potentially null field using LINQ and Entity Framework
- Using custom method inside Linq Select with Entity Framework
- How to write Linq Queries for CRUD operations using Entity Framework with Asp.Net?
- Join into with column and variable value compared using Linq or Entity Framework queries
- Entity Framework LINQ Query using Custom C# Class Method - Once yes, once no - because executing on the client or in SQL?
- LINQ - Entity framework code first - Grouped results to custom class
- Entity Framework Linq foreach performance vs using Select() instead
- Getting InvalidCastException when trying to implement sorting in Entity Framework Code First using Linq
- c# WPF bind combobox to TPH in Entity Framework code first using LINQ
- How to make updatable DataGridView of joined Entity Framework tables using Linq query
- How to avoid Query Plan re-compilation when using IEnumerable.Contains in Entity Framework LINQ queries?
- Entity Framework 6 Code First Custom Functions
- Programmatically chain OrderBy/ThenBy using LINQ / Entity Framework
- Cast Entity to Implemented Interface in a Generic Method Using LINQ for Entity Framework
- Deleting multiple records with Entity Framework using a single LINQ query
- Dynamic linq query expression tree for sql IN clause using Entity framework
- Retrieve single Entity Framework entities using a LINQ query or GetObjectKey?
- Entity Framework Linq Query to List - Error when using contains: Only primitive types, enumeration types and entity types are supported
- Entity Framework with Oracle using odp.net not taking parameters in linq query
- Entity Framework Core : LINQ advise needed on better approach using include for relational tables
- Behaviour when including the same field twice in entity framework linq
- Left join after a into group in Linq using entity framework (core)
- Using Specification Pattern and Expressions in Entity Framework and Linq to Entities
- C# entity framework using LINQ
- Linq to entity framework error when using datetimeoffset
- Giving Multiple List<int> type criteria by using linq in Entity Framework
- How to write LINQ Queries for CRUD using Entity Framework?
- How to get a distinct, case-insensitive list using Linq and Entity Framework
- Encrypting Decrypting Data via Entity Framework using separate class exposing encrypted/decrypted values: Linq statements fails
- linq Group By and take first and lats records
- LINQ OrderBy with more than one field
- Getting error in LINQ Expression or converting service to client simple mapping
- LINQ to Entities does not recognize the method System.String
- LINQ SQL Connection String Not Working When Read From TXT File
- In Entity Framework, how can I use aggregate functions in the Select clause?
- How to group C# class object values. Is this possible with LINQ?
- How to join 3 tables with lambda expression?
- Taking advantage of PLINQ with custom Enumerable Extensions
- Read all XML child nodes of each specific node
- Sum duplicated objects in an array
- Linq query - returning a sum
- {"LINQ to Entities does not recognize the method 'Boolean IsLetter(Char)' method, and this method cannot be translated into a store expression."}
- Collect indexes from list
- Why is LINQ non-deterministic?
- XDocument producing invalid XML
- Retrieve three columns and group by one
- Test if all items in list are completed before a certain item
- Linq: how return property of specific object
- Join with multiple options