Simple multilevel projection results in N+1 SQL Queries.

[cdwaddell]

At first I thought this was an issue with AutoMapper, until converting the code back to a simple projection, which has the same results.

The code to reproduce this is here github

Example Code

aDto = context.As
	//Includes don't affect the results
	//.Include(a => a.Bs).ThenInclude(b => b.Cs) 
	.Where(a => a.Name == "Root")
	.Select(a =>
		new ADto
		{
			Name = a.Name,
			Bs = a.Bs.Select(b =>
				new BDto
				{
					Name = b.Name,
					Id = b.Id,
					Cs = b.Cs.Select(c => new CDto
					{
						Name = c.Name,
						Id = c.Id
					}).ToList()
				}
			).ToList()
		}
	).SingleOrDefault();

I can get the expected results by using AsEnumerable() and Includes.

aDto = context.As.Include(a => a.Bs).ThenInclude(b => b.Cs)
	.Where(a => a.Name == "Root")
	.AsEnumerable()
	.Select(a => ...

Output

Here is output from query based on sample data in the project. It appears to go to the database once for each second level table.

Opening connection to database 'TestAutomapper' on server '(localdb)\mssqllocaldb'.
Executed DbCommand (15ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [a].[Name], [a].[Id]
FROM [As] AS [a]
WHERE [a].[Name] = N'Root'
Executed DbCommand (6ms) [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='30']
SELECT [b].[Name], [b].[Id]
FROM [Bs] AS [b]
WHERE @_outer_Id = [b].[AEntityId]
Executed DbCommand (13ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Executed DbCommand (2ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Executed DbCommand (1ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Executed DbCommand (2ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT [c].[Name], [c].[Id]
FROM [Cs] AS [c]
WHERE @_outer_Id1 = [c].[BEntityId]
Closing connection to database 'TestAutomapper' on server '(localdb)\mssqllocaldb'.

Further technical details

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2015

[maumar]

dupe of #4007