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 Query
- 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
- How do I use CURRENT_TIMESTAMP in a LINQ update statement using Entity Framework Core?
- how to use "not equal to" in where condition using linq with entity framework
- Is it possible to prevent LINQ from using variables in queries and instead use string literals?
- 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
- Does Linq in Entity Framework code first use SQL or does it get the whole table first?
- Entity Framework Linq foreach performance vs using Select() instead
- How to make Entity Framework use 1 update query instead of 1 for each record?
- 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
- Use Linq to shortlist parent records by using Entity Framework relations
- Using stored procedure in Entity Framework with input parameters - how to use in Linq query?
- 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
- Why would Entity Framework not be able to use ToString() in a LINQ statement?
- Cast Entity to Implemented Interface in a Generic Method Using LINQ for Entity Framework
- Use a Inline Table-Valued Functions with Linq and Entity Framework Core
- If it's bad to use inline SQL, how does using LINQ to perform queries differ in practice?
- 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
More Query from same tag
- C# How to parse an Object[] to find all List<Int> objects using Linq?
- Linq where clause confusion
- Entity Framework filter on inner list
- ToList slow performance vs foreach slow performance
- Shortest method to convert an array to a string in c#/LINQ
- Linq Get totals using group by
- Reverse a dictionary from <Parent, IEnumerable<Child>> to <Child, IEnumerable<Parent>>
- Get count of a row if value exists
- Issue with dynamic LINQ queries containing brackets
- Confusion over storing a type in a temp variable in a LINQ query
- Sort and group a list of objects by multiple properties and conditions
- In C#, what is the best way to sort a list of objects by a string property and get correct order?
- How can I dynamically combine a set of LINQ queries at runtime?
- How to add two records with LINQ?
- Fill data in ObservableCollection using LINQ
- How to compare Date in LINQ(Likewise Like in SQL query)
- EF6 Querying for the string with newline character
- The "GenerateResource" task failed unexpectedly
- Entity Framework CodeFirst Move Data From One table To another
- Check all properties in list of objects is equal
- Ordered Distinct
- VB.NET LINQ to XML Left Join with ambiguous column name
- Partitioning a list given total partitions and partition number
- Reset all component's properties except excluded ones
- how to write linq "group by" and get count based on row in asp.net mvc
- Reference ENUM description when building selectList for dropdown list control
- Linq Lambda Expression to check null or empty
- LINQ Join: Object reference not set to an instance of an object
- C# boolean logic with dynamic LINQ?
- Find least Rate in a List<T> in a specific group