score:1

Accepted answer

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]

Related Query

More Query from same tag