`DbSet.Count()` in `Select()` lambda not translating to scalar subquery expression

[Bretttt]

It appears a DbSet.Count() expression is getting evaluated ahead of time and parameterized.

Example:

var query = dbContext.MyDbSet.Select(v => new
{
    v.Id,
   Count = dbContext.MyDbSet.Count()
});
string sql = query.ToQueryString();

Expected:

SELECT [v].[Id], (select COUNT(1) from [MyTable]) AS [Count]
FROM [MyTable] AS [v]

Actual:

DECLARE @__Count_0 int = 20;

SELECT [v].[Id], @__Count_0 AS [Count]
FROM [MyTable] AS [v]

Maybe this is by design for some reason I'm not understanding. Thank you

[maumar]

@Bretttt you can prevent parameterization by adding a noop queryable method before count, e.g.

var query = dbContext.MyDbSet.Select(v => new
{
v.Id,
Count = dbContext.MyDbSet.Where(x => true).Count()
});

[maumar]

note: EF8 had the same behavior, so we didn't change/break anything when doing funcletizer rewrite. But this behavior is a bit awkward. e.g.

var query = await ctx.MyEntities.Select(x => new { x.Id, Foo = ctx.MyEntities.Order().FirstOrDefault().Name }).ToListAsync();

will evaluate the subquery (extra round trip):

SELECT TOP(1) [m].[Id], [m].[Name]
FROM [MyEntities] AS [m]
ORDER BY [m].[Id]

exec sp_executesql N'SELECT [m].[Id], @__FirstOrDefault_Name_0 AS [Foo]
FROM [MyEntities] AS [m]',N'@__FirstOrDefault_Name_0 nvarchar(4000)',@__FirstOrDefault_Name_0=N'foo'

while:

 var query = await ctx.MyEntities.Select(x => new { x.Id, Foo = ctx.MyEntities.OrderBy(x => x.Id).FirstOrDefault().Name }).ToListAsync();

will inline:

SELECT [m].[Id], (
    SELECT TOP(1) [m0].[Name]
    FROM [MyEntities] AS [m0]
    ORDER BY [m0].[Id]) AS [Foo]
FROM [MyEntities] AS [m]

[maumar]

/cc @roji

[roji]

The EF8 change with separate evaluation of subqueries was fixed in #33012, which indeed seems related... I agree that the behavior above seems quite wrong, and we shouldn't be evaluating.

@maumar @cincuranet one of you feel like taking a look at this?

[maumar]

I can take a look, since I peeked into that code already when doing the initial investigation