EF is producing multiple queries (n+1) instead of a single query with a subquery when the selection contains the entire element instead of just part of it.
Set up a project as per https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/new-db?tabs=visual-studio
context.Blogs.Select(a => new { a.Url, a.Posts.Count }).ToList(); runs this
SELECT [a].[Url], (
SELECT COUNT(*)
FROM [Posts] AS [p]
WHERE [a].[BlogId] = [p].[BlogId]
) AS [Count]
FROM [Blogs] AS [a]
But
context.Blogs.Select(a => new { a, a.Posts.Count }).ToList(); runs this
SELECT [a].[BlogId], [a].[Url]
FROM [Blogs] AS [a];
exec sp_executesql N'SELECT COUNT(*)
FROM [Posts] AS [p0]
WHERE @_outer_BlogId = [p0].[BlogId]',N'@_outer_BlogId int',@_outer_BlogId=2
How can I rework the linq to select the entire Blog object without generating multiple queries? Using include isn't helping from what i can see.