Using .include in a framework entity creates a huge request?

Question

I did not expect a generated request like this ...

let go back if I turn on one of them, the request looks good, it makes a simple left join

Query:

using (var db = new Context()) { var data = db.MainTables.Include(x => x.LookupTables) .Where(d => d.MainId == 10) .FirstOrDefault(); } 

Generated by SQL:

 Opened connection at 2014-05-12 17:37:10 -04:00 SELECT [Project1].[MainId] AS [MainId], [Project1].[C1] AS [C1], [Project1].[LookupId] AS [LookupId] FROM ( SELECT [Limit1].[MainId] AS [MainId], [Extent2].[LookupId] AS [LookupId], CASE WHEN ([Extent2].[MainId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM (SELECT TOP (1) [Extent1].[MainId] AS [MainId] FROM [dbo].[MainTable] AS [Extent1] WHERE 10 = [Extent1].[MainId] ) AS [Limit1] LEFT OUTER JOIN [dbo].[MainTable_LookupTable] AS [Extent2] ON [Limit1].[MainId] = [Extent2].[MainId] ) AS [Project1] ORDER BY [Project1].[MainId] ASC, [Project1].[C1] ASC -- Executing at 2014-05-12 17:37:11 -04:00 -- Completed in 11 ms with result: SqlDataReader Closed connection at 2014-05-12 17:37:11 -04:00 

If I have more than one, the request goes crazy, like hell with UNION ALL ...

Is this expected behavior? if so, is it possible to preserve the behavior of one of them, while using more than one simple left join for all of them?

Model:

Query:

 using (var db = new Context()) { var data = db.MainTables.Include(x => x.LookupTables) .Include(x => x.MainTable_MoreData) .Include(x => x.MaintTable_DataLookup) .Include(x => x.MainTable_EvenMoreData) .Where(d => d.MainId == 10) .FirstOrDefault() } 

Generated by SQL:

 Opened connection at 2014-05-12 18:00:56 -04:00 SELECT [UnionAll3].[C2] AS [C1], [UnionAll3].[C3] AS [C2], [UnionAll3].[C4] AS [C3], [UnionAll3].[C5] AS [C4], [UnionAll3].[C6] AS [C5], [UnionAll3].[C7] AS [C6], [UnionAll3].[C8] AS [C7], [UnionAll3].[C9] AS [C8], [UnionAll3].[C10] AS [C9], [UnionAll3].[C11] AS [C10], [UnionAll3].[C12] AS [C11], [UnionAll3].[C13] AS [C12], [UnionAll3].[C1] AS [C13], [UnionAll3].[C14] AS [C14], [UnionAll3].[C15] AS [C15], [UnionAll3].[C16] AS [C16], [UnionAll3].[C17] AS [C17], [UnionAll3].[C18] AS [C18], [UnionAll3].[C19] AS [C19], [UnionAll3].[C20] AS [C20], [UnionAll3].[C21] AS [C21], [UnionAll3].[C22] AS [C22], [UnionAll3].[C23] AS [C23], [UnionAll3].[C24] AS [C24], [UnionAll3].[C25] AS [C25], [UnionAll3].[C26] AS [C26], [UnionAll3].[C27] AS [C27], [UnionAll3].[C28] AS [C28], [UnionAll3].[C29] AS [C29], [UnionAll3].[C30] AS [C30], [UnionAll3].[C31] AS [C31], [UnionAll3].[C32] AS [C32], [UnionAll3].[C33] AS [C33], [UnionAll3].[C34] AS [C34], [UnionAll3].[C35] AS [C35], [UnionAll3].[C36] AS [C36], [UnionAll3].[C37] AS [C37], [UnionAll3].[C38] AS [C38], [UnionAll3].[C39] AS [C39], [UnionAll3].[C40] AS [C40], [UnionAll3].[C41] AS [C41], [UnionAll3].[C42] AS [C42], [UnionAll3].[C43] AS [C43], [UnionAll3].[C44] AS [C44], [UnionAll3].[C45] AS [C45], [UnionAll3].[C46] AS [C46], [UnionAll3].[C47] AS [C47], [UnionAll3].[C48] AS [C48], [UnionAll3].[C49] AS [C49], [UnionAll3].[C50] AS [C50], [UnionAll3].[C51] AS [C51] FROM (SELECT [UnionAll2].[C1] AS [C1], [UnionAll2].[C2] AS [C2], [UnionAll2].[C3] AS [C3], [UnionAll2].[C4] AS [C4], [UnionAll2].[C5] AS [C5], [UnionAll2].[C6] AS [C6], [UnionAll2].[C7] AS [C7], [UnionAll2].[C8] AS [C8], [UnionAll2].[C9] AS [C9], [UnionAll2].[C10] AS [C10], [UnionAll2].[C11] AS [C11], [UnionAll2].[C12] AS [C12], [UnionAll2].[C13] AS [C13], [UnionAll2].[C14] AS [C14], [UnionAll2].[C15] AS [C15], [UnionAll2].[C16] AS [C16], [UnionAll2].[C17] AS [C17], [UnionAll2].[C18] AS [C18], [UnionAll2].[C19] AS [C19], [UnionAll2].[C20] AS [C20], [UnionAll2].[C21] AS [C21], [UnionAll2].[C22] AS [C22], [UnionAll2].[C23] AS [C23], [UnionAll2].[C24] AS [C24], [UnionAll2].[C25] AS [C25], [UnionAll2].[C26] AS [C26], [UnionAll2].[C27] AS [C27], [UnionAll2].[C28] AS [C28], [UnionAll2].[C29] AS [C29], [UnionAll2].[C30] AS [C30], [UnionAll2].[C31] AS [C31], [UnionAll2].[C32] AS [C32], [UnionAll2].[C33] AS [C33], [UnionAll2].[C34] AS [C34], [UnionAll2].[C35] AS [C35], [UnionAll2].[C36] AS [C36], [UnionAll2].[C37] AS [C37], [UnionAll2].[C38] AS [C38], [UnionAll2].[C39] AS [C39], [UnionAll2].[C40] AS [C40], [UnionAll2].[C41] AS [C41], [UnionAll2].[C42] AS [C42], [UnionAll2].[C43] AS [C43], [UnionAll2].[C44] AS [C44], [UnionAll2].[C45] AS [C45], [UnionAll2].[C46] AS [C46], [UnionAll2].[C47] AS [C47], [UnionAll2].[C48] AS [C48], [UnionAll2].[C49] AS [C49], [UnionAll2].[C50] AS [C50], [UnionAll2].[C51] AS [C51] FROM (SELECT [UnionAll1].[C1] AS [C1], [UnionAll1].[MainId] AS [C2], [UnionAll1].[MainId1] AS [C3], [UnionAll1].[Field1] AS [C4], [UnionAll1].[Field11] AS [C5], [UnionAll1].[Field12] AS [C6], [UnionAll1].[Field13] AS [C7], [UnionAll1].[Field14] AS [C8], [UnionAll1].[Field15] AS [C9], [UnionAll1].[Field16] AS [C10], [UnionAll1].[Field17] AS [C11], [UnionAll1].[Field18] AS [C12], [UnionAll1].[Field19] AS [C13], [UnionAll1].[LookupId] AS [C14], [UnionAll1].[Field161] AS [C15], [UnionAll1].[Field151] AS [C16], [UnionAll1].[Field141] AS [C17], [UnionAll1].[Field131] AS [C18], [UnionAll1].[Field121] AS [C19], [UnionAll1].[Field111] AS [C20], [UnionAll1].[Field110] AS [C21], [UnionAll1].[C2] AS [C22], [UnionAll1].[C3] AS [C23], [UnionAll1].[C4] AS [C24], [UnionAll1].[C5] AS [C25], [UnionAll1].[C6] AS [C26], [UnionAll1].[C7] AS [C27], [UnionAll1].[C8] AS [C28], [UnionAll1].[C9] AS [C29], [UnionAll1].[C10] AS [C30], [UnionAll1].[C11] AS [C31], [UnionAll1].[C12] AS [C32], [UnionAll1].[C13] AS [C33], [UnionAll1].[C14] AS [C34], [UnionAll1].[C15] AS [C35], [UnionAll1].[C16] AS [C36], [UnionAll1].[C17] AS [C37], [UnionAll1].[C18] AS [C38], [UnionAll1].[C19] AS [C39], [UnionAll1].[C20] AS [C40], [UnionAll1].[C21] AS [C41], [UnionAll1].[C22] AS [C42], [UnionAll1].[C23] AS [C43], [UnionAll1].[C24] AS [C44], [UnionAll1].[C25] AS [C45], [UnionAll1].[C26] AS [C46], [UnionAll1].[C27] AS [C47], [UnionAll1].[C28] AS [C48], [UnionAll1].[C29] AS [C49], [UnionAll1].[C30] AS [C50], [UnionAll1].[C31] AS [C51] FROM (SELECT CASE WHEN ([Join1].[MainId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], [Limit1].[MainId] AS [MainId], [Limit1].[MainId] AS [MainId1], [Limit1].[Field1] AS [Field1], [Limit1].[Field11] AS [Field11], [Limit1].[Field12] AS [Field12], [Limit1].[Field13] AS [Field13], [Limit1].[Field14] AS [Field14], [Limit1].[Field15] AS [Field15], [Limit1].[Field16] AS [Field16], [Limit1].[Field17] AS [Field17], [Limit1].[Field18] AS [Field18], [Limit1].[Field19] AS [Field19], [Join1].[LookupId1] AS [LookupId], [Join1].[Field16] AS [Field161], [Join1].[Field15] AS [Field151], [Join1].[Field14] AS [Field141], [Join1].[Field13] AS [Field131], [Join1].[Field12] AS [Field121], [Join1].[Field11] AS [Field111], [Join1].[Field1] AS [Field110], CAST(NULL AS int) AS [C2], CAST(NULL AS int) AS [C3], CAST(NULL AS varchar(1)) AS [C4], CAST(NULL AS varchar(1)) AS [C5], CAST(NULL AS varchar(1)) AS [C6], CAST(NULL AS varchar(1)) AS [C7], CAST(NULL AS varchar(1)) AS [C8], CAST(NULL AS varchar(1)) AS [C9], CAST(NULL AS varchar(1)) AS [C10], CAST(NULL AS int) AS [C11], CAST(NULL AS varchar(1)) AS [C12], CAST(NULL AS varchar(1)) AS [C13], CAST(NULL AS varchar(1)) AS [C14], CAST(NULL AS varchar(1)) AS [C15], CAST(NULL AS varchar(1)) AS [C16], CAST(NULL AS varchar(1)) AS [C17], CAST(NULL AS varchar(1)) AS [C18], CAST(NULL AS int) AS [C19], CAST(NULL AS int) AS [C20], CAST(NULL AS int) AS [C21], CAST(NULL AS varchar(1)) AS [C22], CAST(NULL AS varchar(1)) AS [C23], CAST(NULL AS varchar(1)) AS [C24], CAST(NULL AS varchar(1)) AS [C25], CAST(NULL AS varchar(1)) AS [C26], CAST(NULL AS varchar(1)) AS [C27], CAST(NULL AS varchar(1)) AS [C28], CAST(NULL AS varchar(1)) AS [C29], CAST(NULL AS varchar(1)) AS [C30], CAST(NULL AS int) AS [C31] FROM (SELECT TOP (1) [Extent1].[MainId] AS [MainId], [Extent1].[Field1] AS [Field1], [Extent1].[Field11] AS [Field11], [Extent1].[Field12] AS [Field12], [Extent1].[Field13] AS [Field13], [Extent1].[Field14] AS [Field14], [Extent1].[Field15] AS [Field15], [Extent1].[Field16] AS [Field16], [Extent1].[Field17] AS [Field17], [Extent1].[Field18] AS [Field18], [Extent1].[Field19] AS [Field19] FROM [dbo].[MainTable] AS [Extent1] WHERE 10 = [Extent1].[MainId] ) AS [Limit1] LEFT OUTER JOIN (SELECT [Extent2].[MainId] AS [MainId], [Extent3].[LookupId] AS [LookupId1], [Extent3].[Field16] AS [Field16], [Extent3].[Field15] AS [Field15], [Extent3].[Field14] AS [Field14], [Extent3].[Field13] AS [Field13], [Extent3].[Field12] AS [Field12], [Extent3].[Field11] AS [Field11], [Extent3].[Field1] AS [Field1] FROM [dbo].[MainTable_LookupTable] AS [Extent2] INNER JOIN [dbo].[LookupTable] AS [Extent3] ON [Extent3].[LookupId] = [Extent2].[LookupId] ) AS [Join1] ON [Limit1].[MainId] = [Join1].[MainId] UNION ALL SELECT 2 AS [C1], [Limit2].[MainId] AS [MainId], [Limit2].[MainId] AS [MainId1], [Limit2].[Field1] AS [Field1], [Limit2].[Field11] AS [Field11], [Limit2].[Field12] AS [Field12], [Limit2].[Field13] AS [Field13], [Limit2].[Field14] AS [Field14], [Limit2].[Field15] AS [Field15], [Limit2].[Field16] AS [Field16], [Limit2].[Field17] AS [Field17], [Limit2].[Field18] AS [Field18], [Limit2].[Field19] AS [Field19], CAST(NULL AS int) AS [C2], CAST(NULL AS varchar(1)) AS [C3], CAST(NULL AS varchar(1)) AS [C4], CAST(NULL AS varchar(1)) AS [C5], CAST(NULL AS varchar(1)) AS [C6], CAST(NULL AS varchar(1)) AS [C7], CAST(NULL AS varchar(1)) AS [C8], CAST(NULL AS varchar(1)) AS [C9], [Extent5].[DataId] AS [DataId], [Extent5].[MainId] AS [MainId2], [Extent5].[SomeData] AS [SomeData], [Extent5].[Field1] AS [Field110], [Extent5].[Field11] AS [Field111], [Extent5].[Field12] AS [Field121], [Extent5].[Field13] AS [Field131], [Extent5].[Field14] AS [Field141], [Extent5].[Field15] AS [Field151], CAST(NULL AS int) AS [C10], CAST(NULL AS varchar(1)) AS [C11], CAST(NULL AS varchar(1)) AS [C12], CAST(NULL AS varchar(1)) AS [C13], CAST(NULL AS varchar(1)) AS [C14], CAST(NULL AS varchar(1)) AS [C15], CAST(NULL AS varchar(1)) AS [C16], CAST(NULL AS varchar(1)) AS [C17], CAST(NULL AS int) AS [C18], CAST(NULL AS int) AS [C19], CAST(NULL AS int) AS [C20], CAST(NULL AS varchar(1)) AS [C21], CAST(NULL AS varchar(1)) AS [C22], CAST(NULL AS varchar(1)) AS [C23], CAST(NULL AS varchar(1)) AS [C24], CAST(NULL AS varchar(1)) AS [C25], CAST(NULL AS varchar(1)) AS [C26], CAST(NULL AS varchar(1)) AS [C27], CAST(NULL AS varchar(1)) AS [C28], CAST(NULL AS varchar(1)) AS [C29], CAST(NULL AS int) AS [C30] FROM (SELECT TOP (1) [Extent4].[MainId] AS [MainId], [Extent4].[Field1] AS [Field1], [Extent4].[Field11] AS [Field11], [Extent4].[Field12] AS [Field12], [Extent4].[Field13] AS [Field13], [Extent4].[Field14] AS [Field14], [Extent4].[Field15] AS [Field15], [Extent4].[Field16] AS [Field16], [Extent4].[Field17] AS [Field17], [Extent4].[Field18] AS [Field18], [Extent4].[Field19] AS [Field19] FROM [dbo].[MainTable] AS [Extent4] WHERE 10 = [Extent4].[MainId] ) AS [Limit2] INNER JOIN [dbo].[MainTable_MoreData] AS [Extent5] ON [Limit2].[MainId] = [Extent5].[MainId]) AS [UnionAll1] UNION ALL SELECT 3 AS [C1], [Limit3].[MainId] AS [MainId], [Limit3].[MainId] AS [MainId1], [Limit3].[Field1] AS [Field1], [Limit3].[Field11] AS [Field11], [Limit3].[Field12] AS [Field12], [Limit3].[Field13] AS [Field13], [Limit3].[Field14] AS [Field14], [Limit3].[Field15] AS [Field15], [Limit3].[Field16] AS [Field16], [Limit3].[Field17] AS [Field17], [Limit3].[Field18] AS [Field18], [Limit3].[Field19] AS [Field19], CAST(NULL AS int) AS [C2], CAST(NULL AS varchar(1)) AS [C3], CAST(NULL AS varchar(1)) AS [C4], CAST(NULL AS varchar(1)) AS [C5], CAST(NULL AS varchar(1)) AS [C6], CAST(NULL AS varchar(1)) AS [C7], CAST(NULL AS varchar(1)) AS [C8], CAST(NULL AS varchar(1)) AS [C9], CAST(NULL AS int) AS [C10], CAST(NULL AS int) AS [C11], CAST(NULL AS varchar(1)) AS [C12], CAST(NULL AS varchar(1)) AS [C13], CAST(NULL AS varchar(1)) AS [C14], CAST(NULL AS varchar(1)) AS [C15], CAST(NULL AS varchar(1)) AS [C16], CAST(NULL AS varchar(1)) AS [C17], CAST(NULL AS varchar(1)) AS [C18], [Extent7].[MainId] AS [MainId2], [Extent7].[Data] AS [Data], [Extent7].[Field1] AS [Field110], [Extent7].[Field11] AS [Field111], [Extent7].[Field12] AS [Field121], [Extent7].[Field14] AS [Field141], [Extent7].[Field15] AS [Field151], [Extent7].[Field13] AS [Field131], [Extent7].[MainId] AS [MainId3], CAST(NULL AS int) AS [C19], CAST(NULL AS int) AS [C20], CAST(NULL AS varchar(1)) AS [C21], CAST(NULL AS varchar(1)) AS [C22], CAST(NULL AS varchar(1)) AS [C23], CAST(NULL AS varchar(1)) AS [C24], CAST(NULL AS varchar(1)) AS [C25], CAST(NULL AS varchar(1)) AS [C26], CAST(NULL AS varchar(1)) AS [C27], CAST(NULL AS varchar(1)) AS [C28], CAST(NULL AS varchar(1)) AS [C29], CAST(NULL AS int) AS [C30] FROM (SELECT TOP (1) [Extent6].[MainId] AS [MainId], [Extent6].[Field1] AS [Field1], [Extent6].[Field11] AS [Field11], [Extent6].[Field12] AS [Field12], [Extent6].[Field13] AS [Field13], [Extent6].[Field14] AS [Field14], [Extent6].[Field15] AS [Field15], [Extent6].[Field16] AS [Field16], [Extent6].[Field17] AS [Field17], [Extent6].[Field18] AS [Field18], [Extent6].[Field19] AS [Field19] FROM [dbo].[MainTable] AS [Extent6] WHERE 10 = [Extent6].[MainId] ) AS [Limit3] INNER JOIN [dbo].[MaintTable_DataLookup] AS [Extent7] ON [Limit3].[MainId] = [Extent7].[MainId]) AS [UnionAll2] UNION ALL SELECT 4 AS [C1], [Limit4].[MainId] AS [MainId], [Limit4].[MainId] AS [MainId1], [Limit4].[Field1] AS [Field1], [Limit4].[Field11] AS [Field11], [Limit4].[Field12] AS [Field12], [Limit4].[Field13] AS [Field13], [Limit4].[Field14] AS [Field14], [Limit4].[Field15] AS [Field15], [Limit4].[Field16] AS [Field16], [Limit4].[Field17] AS [Field17], [Limit4].[Field18] AS [Field18], [Limit4].[Field19] AS [Field19], CAST(NULL AS int) AS [C2], CAST(NULL AS varchar(1)) AS [C3], CAST(NULL AS varchar(1)) AS [C4], CAST(NULL AS varchar(1)) AS [C5], CAST(NULL AS varchar(1)) AS [C6], CAST(NULL AS varchar(1)) AS [C7], CAST(NULL AS varchar(1)) AS [C8], CAST(NULL AS varchar(1)) AS [C9], CAST(NULL AS int) AS [C10], CAST(NULL AS int) AS [C11], CAST(NULL AS varchar(1)) AS [C12], CAST(NULL AS varchar(1)) AS [C13], CAST(NULL AS varchar(1)) AS [C14], CAST(NULL AS varchar(1)) AS [C15], CAST(NULL AS varchar(1)) AS [C16], CAST(NULL AS varchar(1)) AS [C17], CAST(NULL AS varchar(1)) AS [C18], CAST(NULL AS int) AS [C19], CAST(NULL AS varchar(1)) AS [C20], CAST(NULL AS varchar(1)) AS [C21], CAST(NULL AS varchar(1)) AS [C22], CAST(NULL AS varchar(1)) AS [C23], CAST(NULL AS varchar(1)) AS [C24], CAST(NULL AS varchar(1)) AS [C25], CAST(NULL AS varchar(1)) AS [C26], CAST(NULL AS int) AS [C27], [Extent9].[LookupId] AS [LookupId], [Extent9].[MainId] AS [MainId2], [Extent9].[MoreMoreData] AS [MoreMoreData], [Extent9].[Field17] AS [Field171], [Extent9].[Field16] AS [Field161], [Extent9].[Field15] AS [Field151], [Extent9].[Field14] AS [Field141], [Extent9].[Field13] AS [Field131], [Extent9].[Field12] AS [Field121], [Extent9].[Field11] AS [Field111], [Extent9].[Field1] AS [Field110], [Extent9].[MainId] AS [MainId3] FROM (SELECT TOP (1) [Extent8].[MainId] AS [MainId], [Extent8].[Field1] AS [Field1], [Extent8].[Field11] AS [Field11], [Extent8].[Field12] AS [Field12], [Extent8].[Field13] AS [Field13], [Extent8].[Field14] AS [Field14], [Extent8].[Field15] AS [Field15], [Extent8].[Field16] AS [Field16], [Extent8].[Field17] AS [Field17], [Extent8].[Field18] AS [Field18], [Extent8].[Field19] AS [Field19] FROM [dbo].[MainTable] AS [Extent8] WHERE 10 = [Extent8].[MainId] ) AS [Limit4] INNER JOIN [dbo].[MainTable_EvenMoreData] AS [Extent9] ON [Limit4].[MainId] = [Extent9].[MainId]) AS [UnionAll3] ORDER BY [UnionAll3].[C3] ASC, [UnionAll3].[C1] ASC -- Executing at 2014-05-12 18:00:57 -04:00 -- Completed in 39 ms with result: SqlDataReader 

Sql server result

why 8 lines when 2 is enough?

this db example is not optimized with index and such, but here is the execution plan

plan

Ideally I would like to create a query that will look like, keeping change tracking entity structure because I would use the object to update the database

 SELECT dbo.MainTable.MainId, dbo.MainTable.Field1, dbo.MainTable.Field11, dbo.MainTable.Field12, dbo.MainTable.Field13, dbo.MainTable.Field14, dbo.MainTable.Field15, dbo.MainTable.Field16, dbo.MainTable.Field17, dbo.MainTable.Field18, dbo.MainTable.Field19, dbo.MainTable_EvenMoreData.LookupId, dbo.MainTable_EvenMoreData.MainId AS Expr1, dbo.MainTable_EvenMoreData.MoreMoreData, dbo.MainTable_EvenMoreData.Field17 AS Expr2, dbo.MainTable_EvenMoreData.Field16 AS Expr3, dbo.MainTable_EvenMoreData.Field15 AS Expr4, dbo.MainTable_EvenMoreData.Field14 AS Expr5, dbo.MainTable_EvenMoreData.Field13 AS Expr6, dbo.MainTable_EvenMoreData.Field12 AS Expr7, dbo.MainTable_EvenMoreData.Field11 AS Expr8, dbo.MainTable_EvenMoreData.Field1 AS Expr9, dbo.MainTable_LookupTable.MainId AS Expr10, dbo.MainTable_LookupTable.LookupId AS Expr11, dbo.MainTable_MoreData.DataId, dbo.MainTable_MoreData.MainId AS Expr12, dbo.MainTable_MoreData.SomeData, dbo.MainTable_MoreData.Field1 AS Expr13, dbo.MainTable_MoreData.Field11 AS Expr14, dbo.MainTable_MoreData.Field12 AS Expr15, dbo.MainTable_MoreData.Field13 AS Expr16, dbo.MainTable_MoreData.Field14 AS Expr17, dbo.MainTable_MoreData.Field15 AS Expr18, dbo.MaintTable_DataLookup.MainId AS Expr19, dbo.MaintTable_DataLookup.Data, dbo.MaintTable_DataLookup.Field1 AS Expr20, dbo.MaintTable_DataLookup.Field11 AS Expr21, dbo.MaintTable_DataLookup.Field12 AS Expr22, dbo.MaintTable_DataLookup.Field14 AS Expr23, dbo.MaintTable_DataLookup.Field15 AS Expr24, dbo.MaintTable_DataLookup.Field13 AS Expr25 FROM dbo.MainTable LEFT OUTER JOIN dbo.MainTable_EvenMoreData ON dbo.MainTable.MainId = dbo.MainTable_EvenMoreData.MainId LEFT OUTER JOIN dbo.MainTable_LookupTable ON dbo.MainTable.MainId = dbo.MainTable_LookupTable.MainId LEFT OUTER JOIN dbo.MainTable_MoreData ON dbo.MainTable.MainId = dbo.MainTable_MoreData.MainId LEFT OUTER JOIN dbo.MaintTable_DataLookup ON dbo.MainTable.MainId = dbo.MaintTable_DataLookup.MainId WHERE dbo.MainTable.MainId = 10 

Answer 1

therefore, after several hours of playing, I made some balancing / execution plan / execution statistics plan on the sql server with a simple left connection and a huge query that the entity infrastructure performs.

it seems that the entity structure wins by a wide margin.

I am going to implement a solution .include ().

Answer 2

Yes, it is normal. The Entity Framework takes your (usually hierarchical) relational model and tries to create a query that will generate all the necessary data in one big flat table.

Is it possible to preserve the behavior of one of them, while using more than one simple left join for all of them?

It depends on what you mean by "behavior." If you mean creating a similar SQL query, then no, you cannot. But you need to ask why you care: these queries can be optimized for SQL Server, but they will not be optimized for readability.

If you mean that you want to increase productivity, there are strategies that you can use other than loading anything in one large database round-trip. One of the strategies that seemed to work well is to use a new round for each connection, for example:

 var dataQuery = db.MainTables.Where(d => d.MainId == 10); var data = dataQuery.FirstOrDefault(); dataQuery.Select(x => x.MainTable_MoreData).ToList(); // etc. 

Here is a simple utility class that you can use to make it less tedious, especially when loading data from several relationships:

 /// <summary> /// This class aids in loading a lot of related data in Entity Framework. /// <para> /// Typically Entity Framework either lets you load entities Eagerly or Lazily, /// but neither case handles things very well once you are adding many chained /// relationships. A more ideal approach in these cases is to load all of the /// entities you are going to need for a given relationship in a single round-trip, /// and do this once for every relationship you're interested in. /// That what this class helps with. /// </para> /// <para> /// To use: simply create an EntityRelationshipLoader with the initial /// Entity-Framework-backed queryable that will be the basis of all the data /// you're going to be loading. Then for each entity you want to load in relationship /// to that original data type, call either <see cref="Include{TProp}"/> or /// <see cref="IncludeMany{TProp}"/>. The return value from calling these methods may /// be retained and used to include other property relationships based on the /// property that you just defined. Each call to any of these methods will produce a /// single round-trip. /// </para> /// <remarks> /// Remember that all actions on the loader, including its original /// construction, must be performed while the query Entity Framework context /// is active. /// </remarks> /// </summary> /// <typeparam name="T"></typeparam> public class EntityRelationshipLoader<T> : IRelationshipPropertyBuilder<T> { private readonly IQueryable<T> _src; public EntityRelationshipLoader(IQueryable<T> src) : this(src, true) { } private EntityRelationshipLoader(IQueryable<T> src, bool evaluateSource) { _src = src; if (evaluateSource) { LoadEntities(src); } } public IRelationshipPropertyBuilder<TProp> IncludeMany<TProp>(Expression<Func<T, IEnumerable<TProp>>> navProp) { LoadEntities(_src.Select(navProp)); return new EntityRelationshipLoader<TProp>(_src.SelectMany(navProp), false); } public IRelationshipPropertyBuilder<TProp> Include<TProp>(Expression<Func<T, TProp>> navProp) { return new EntityRelationshipLoader<TProp>(_src.Select(navProp), true); } /// <summary> /// Simple helper method to cause the given query to be executed, /// thereby loading all the entities the query represents. /// </summary> /// <param name="query"></param> private void LoadEntities<T1>(IQueryable<T1> query) { #pragma warning disable 168 foreach (var item in query) { } #pragma warning restore 168 } 

This will let you say:

 var dataQuery = db.MainTables.Where(d => d.MainId == 10); var dataLoader = new EntityRelationshipLoader<MainTable>(dataQuery); dataLoader.Include(x => x.LookupTables); dataLoader.IncludeMany(x => x.MainTable_MoreData) // Do you need to load MoreData .LookupTables properties? .Include(x => x.LookupTables); dataLoader.Include(x => x.MaintTable_DataLookup) dataLoader.Include(x => x.MainTable_EvenMoreData); var data = dataQuery.Single(); // Or ToList() if you need multiple of them.