using .include in entity framework create huge query?

Question

I wasn't expecting a generated query like this...

let go back, if I keep one include, the query look good, it does a simple left join

Query:

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

SQL generated:

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 query get crazy like hell with UNION ALL...

is that an expected behavior? if yes, is it possible to keep the behavior of one include 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()

}

SQL generated:

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 rows when 2 is enough?

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

the plan

ideally i would like to produce a query that would look like this while keeping entity framework tracking change since i would be using 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

Jaymejaymee

SO, where is the hugh query ;)? – Selmaselman

Yes, this is normal. Does it bother you? This is generated code, something you very rarely want to even look at – Fritter

@JeroenVannevel, when i see that each union does a join on the main table with the same where clause again and again... i'm not a sql expert but this seem inefficient. this is a sample test. on the real model the query is even bigger and return thousand of rows while if i was using normal left join query it would be less than 50 rows – Jaymejaymee

@TomTom, added a few fields in my test table :-) – Jaymejaymee

Are you experiencing performance problems because of the query size and complexity? – Mainland

@NWard, so far I cannot tell unless i do some load testing myself. but this is a simple example. my real model is way more complex and can have more includes/rows and do have some varchar(256) / + in the main tables. – Jaymejaymee

btw try FirstOrDefault(d => d.MainId == 10) instead of Where(...).FirstOrDefault(). – Weinshienk

@abatishchev, same result – Jaymejaymee

When you use simple left joins you get multiplication of rows in joins, so you can simply get for 10 rows of hierarchy root get 1 million rows from joins. Union returns the only unique rows without join multiplication. In result - queries work faster and returns lesser rows without data loss – Fluoridate

For me it does hit performance and I dont know how I could tweak it in EF – Boast

Answer 1

so after a few hours playing around, i did some load balancing / execution plan / statistic on sql server with a simple left join and the huge query that entity framework does.

it seem that entity framework win by a big margin.

I'm going to implement the .include() solution.

Jaymejaymee

btw, how did you manage to accept your answer to your question? – Firedrake

Answer 2

Yes, this is normal. Entity Framework is taking your (usually hierarchical) relational model and trying to produce a query that will produce all the necessary data in one big, flat table.

is it possible to keep the behavior of one include while using more than one, simple left join for all of them?

It depends on what you mean by "behavior." If you mean producing a similar SQL query, then no, you cannot. But one must ask why you care: these queries might be optimized for SQL Server, but they are not going to be optimized for human readability.

If you mean that you want to have better performance, there are strategies you can use other than eager-loading everything all in one big database round-trip. One strategy that I've found to work well is using a new round-trip per relationship, like so:

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

Here's a simple utility class you can use to make this less tedious, especially when loading data from several relationships deep:

/// <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's 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's 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 allow you to 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's .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.

Jeanene

what I dont understand is the flat table can be huge depending on how many rows are present in child tables, look at my last edit (last image) if the main table have 30 fields with varchar(500) or more this will transfer tons of data for no reason also – Jaymejaymee

@Fredou: that's because you're probably using eager loading which will get everything related to your query the first time. You can also use lazy loading which will only query (roughly) the things you specify. msdn.microsoft.com/nl-be/data/jj574232#lazy – Fritter

right now what I'm doing is something like dbcontext.Entry(result).Collection(x => x.MainTable_MoreData).Load(); which does exactly what you suggested, but if mainTable become a collection(ToList() instead of FirstOrDefault()) and then I'm doing this is a loop, this could generate 4 query per maintable rows. which in the end give me a weird feeling anyway. – Jaymejaymee

@JeroenVannevel, context.ContextOptions.LazyLoadingEnabled = false; give the same behavior – Jaymejaymee

@Fredou: set it to true – Fritter

@Fredou: Eager-loading data from multiple tables in a single round-trip is liable to blow up in one of a few different ways. If they just use a bunch of left outer joins, it would blow up even worse. If you'd like to propose a better query they could use that would still include all the data they'd need to rehydrate this, I can probably guess a reason why they didn't choose that particular approach, but it's hard to explain why they did choose this way, apart from saying it's probably about the best they could do. – Jeanene

@JeroenVannevel: Lazy loading would work well in this case, where there's only a single originating object, and the eager loads only go one layer deep, but you have to be awfully careful to avoid ending up with N, N², or more round-trips. – Jeanene

@JeroenVannevel, true or false give the same query / behavior – Jaymejaymee

your last edit is missing IRelationshipPropertyBuilder interface and the content of the loop, but my understanding of what it does would not change the end result, i think? – Jaymejaymee

@Fredou: The interface just defines the methods. You could remove it if you want. The loop exists solely to iterate over the results: it is intentionally left empty. – Jeanene

Hello, thanks for the utility Class! But this class is implementing the interface IRelationshipPropertyBuilder. Can you provide that please? – Kiersten

@Ravior: As I said in my previous comment, that interface just defines the two public-facing methods you see in this class. If you see value in it, just create an interface with those two methods on it; if not, remove the interface from your code. – Jeanene