[Solved]-Make Entity Framework (using Linq queries) use alias of custom field instead of redoing the subquery-LINQ,C#

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.