Select projection with an (uncorrelated) subquery produces two separate SQL queries #33012
Description
A Select projection with an (uncorrelated) subquery seems to produce two separate SQL queries, which is not what I expect as a developer. For example:
IQueryable<Employee> allEmployees = context
.Employees
.Where(x => x.Department == "Engineering");
Employee[] result = allEmployees
.Where(x => x.Salary > 1000)
.Select(e => new {
Employee = e,
TotalCount = allEmployees.Count()
})
.Skip(2).Take(10)
.ToArray();produces:
info: Executed DbCommand (16ms) [...]
SELECT COUNT(*)
FROM [Employees] AS [e]
WHERE [e].[Department] = N'Engineering'
info: Executed DbCommand (26ms) [...]
SELECT [e].[Id], [e].[Department], [e].[Manager], [e].[Name], [e].[Salary], @__Count_0 AS [TotalCount]
FROM [Employees] AS [e]
WHERE [e].[Department] = N'Engineering' AND [e].[Salary] > 1000.0
ORDER BY (SELECT 1)
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLYCan I somehow force EF to combine these two queries into one (which is what I expect as a developer when I write the above query):
SELECT [e].[Id], [e].[Department], [e].[Manager], [e].[Name], [e].[Salary], (
SELECT COUNT(*)
FROM [Employees] AS [e]
WHERE [e].[Department] = N'Engineering') AS [TotalCount]
FROM [Employees] AS [e]
WHERE [e].[Department] = N'Engineering' AND [e].[Salary] > 1000.0
ORDER BY (SELECT 1)
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLYIn my real application the query is quite complex (with filters and DISTINCTs) and it is measurable faster to combine the two (I guess because SQL Server can reuse quite some intermediate query results). But there might also be other reasons, why developers want to combine the two queries, for example if there are hard consistency requirements.
Here is a .NET Fiddle with full repro.