используя .include в структуре сущностей, создать огромный запрос?

Я не ожидал такого сгенерированного запроса...

вернемся назад, если я оставлю одно включение, запрос выглядит хорошо, он выполняет простое левое соединение

Запрос:

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

Сгенерированный 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

если у меня их больше одного, запрос становится чертовски сумасшедшим с UNION ALL...

это ожидаемое поведение? если да, возможно ли сохранить поведение одного включения при использовании более одного простого левого соединения для всех из них?

Модель:

dbModel2

Запрос:

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:

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

dbresult2

зачем 8 рядов, если достаточно 2?

этот пример базы данных не оптимизирован с помощью индекса и т. д., но вот план выполнения

план

в идеале я хотел бы создать запрос, который выглядел бы так, сохраняя изменения структуры сущностей, поскольку я буду использовать объект для обновления базы данных

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

c# entity-framework tsql entity-framework-6.1
person Fredou    schedule 12.05.2014    source источник
comment
ТАК, где запрос Хью ;)?   -  person TomTom    schedule 13.05.2014
comment
Да, это нормально. Это тебя волнует? Это сгенерированный код, на который вы очень редко хотите даже смотреть   -  person Jeroen Vannevel    schedule 13.05.2014
comment
@JeroenVannevel, когда я вижу, что каждый союз снова и снова выполняет объединение в основной таблице с одним и тем же предложением where ... я не эксперт по sql, но это кажется неэффективным. это пробный тест. в реальной модели запрос еще больше и возвращает тысячу строк, тогда как если бы я использовал обычный запрос левого соединения, он был бы меньше 50 строк.   -  person Fredou    schedule 13.05.2014
comment
@TomTom, добавил несколько полей в мою тестовую таблицу :-)   -  person Fredou    schedule 13.05.2014
comment
Испытываете ли вы проблемы с производительностью из-за размера и сложности запроса?   -  person NWard    schedule 13.05.2014
comment
@NWard, пока я не могу сказать, пока сам не проведу нагрузочное тестирование. но это простой пример. моя реальная модель намного сложнее и может иметь больше включений/строк и иметь некоторые varchar(256)/+ в основных таблицах.   -  person Fredou    schedule 13.05.2014
comment
Кстати, попробуйте FirstOrDefault(d => d.MainId == 10) вместо Where(...).FirstOrDefault().   -  person abatishchev    schedule 13.05.2014
comment
@abatishchev, тот же результат   -  person Fredou    schedule 13.05.2014
comment
Когда вы используете простые левые соединения, вы получаете умножение строк в соединениях, поэтому вы можете просто получить для 10 строк корня иерархии 1 миллион строк из соединений. Union возвращает только уникальные строки без умножения соединения. В результате запросы работают быстрее и возвращают меньше строк без потери данных.   -  person Kirill Bestemyanov    schedule 27.11.2015
comment
Для меня это влияет на производительность, и я не знаю, как я могу настроить его в EF.   -  person aj go    schedule 18.05.2021

Ответы (2)


arrow_upward
6
arrow_downward

поэтому, поиграв несколько часов, я сделал балансировку нагрузки/план выполнения/статистику на сервере sql с простым левым соединением и огромным запросом, который выполняет структура сущности.

кажется, что Entity Framework выигрывает с большим отрывом.

Я собираюсь реализовать решение .include().

person Fredou    schedule 13.05.2014
comment
Кстати, как вам удалось принять ваш ответ на ваш вопрос? - person adminSoftDK; 14.01.2016

arrow_upward
2
arrow_downward

Да, это нормально. Entity Framework берет вашу (обычно иерархическую) реляционную модель и пытается создать запрос, который выведет все необходимые данные в одну большую плоскую таблицу.

возможно ли сохранить поведение одного включения при использовании более одного простого левого соединения для всех из них?

Это зависит от того, что вы подразумеваете под «поведением». Если вы имеете в виду создание аналогичного SQL-запроса, то нет, вы не можете. Но следует спросить, почему вас это волнует: эти запросы могут быть оптимизированы для SQL Server, но они не будут оптимизированы для удобочитаемости человеком.

Если вы имеете в виду, что хотите иметь более высокую производительность, есть стратегии, которые вы можете использовать, кроме нетерпеливой загрузки всего в одну большую базу данных туда и обратно. Одна из стратегий, которая, по моему мнению, хорошо работает, заключается в использовании нового кругового пути для каждого отношения, например:

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

Вот простой служебный класс, который вы можете использовать, чтобы сделать это менее утомительным, особенно при загрузке данных из нескольких глубоких отношений:

/// <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
    }

Это позволит вам сказать:

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.
person StriplingWarrior    schedule 12.05.2014
comment
чего я не понимаю, так это то, что плоская таблица может быть огромной в зависимости от того, сколько строк присутствует в дочерних таблицах, посмотрите на мое последнее редактирование (последнее изображение), если в основной таблице есть 30 полей с varchar (500) или больше, это будет передавать тонны данные без причины также - person Fredou; 13.05.2014
comment
@Fredou: это потому, что вы, вероятно, используете нетерпеливую загрузку, которая с первого раза получит все, что связано с вашим запросом. Вы также можете использовать ленивую загрузку, которая будет запрашивать (приблизительно) только то, что вы укажете. msdn.microsoft.com/nl-be/data/jj574232#lazy - person Jeroen Vannevel; 13.05.2014
comment
прямо сейчас я делаю что-то вроде dbcontext.Entry(result).Collection(x => x.MainTable_MoreData).Load();, который делает именно то, что вы предложили, но если mainTable станет коллекцией (ToList() вместо FirstOrDefault()), а затем я делаю это в цикле, это может сгенерировать 4 запроса на строки основной таблицы. что, в конце концов, все равно вызывает у меня странное чувство. - person Fredou; 13.05.2014
comment
@JeroenVannevel, context.ContextOptions.LazyLoadingEnabled = false; ведет себя так же - person Fredou; 13.05.2014
comment
@Fredou: поставь true - person Jeroen Vannevel; 13.05.2014
comment
@Fredou: нетерпеливая загрузка данных из нескольких таблиц за один цикл может привести к взрыву одним из нескольких способов. Если бы они просто использовали кучу левых внешних соединений, все бы взорвалось еще хуже. Если вы хотите предложить лучший запрос, который они могли бы использовать, который по-прежнему включал бы все данные, которые им понадобятся для регидратации этого, я, вероятно, могу предположить причину, по которой они не выбрали этот конкретный подход, но трудно объяснить, почему они выбрали этот путь, за исключением того, что сказали, что это, вероятно, лучшее, что они могли сделать. - person StriplingWarrior; 13.05.2014
comment
@JeroenVannevel: ленивая загрузка будет хорошо работать в этом случае, когда есть только один исходный объект, а нетерпеливые загрузки идут только на один уровень в глубину, но вы должны быть очень осторожны, чтобы не получить N, N² или более циклов. поездки. - person StriplingWarrior; 13.05.2014
comment
@JeroenVannevel, true или false дают один и тот же запрос/поведение - person Fredou; 13.05.2014
comment
в вашем последнем редактировании отсутствует интерфейс IRelationshipPropertyBuilder и содержимое цикла, но я думаю, что мое понимание того, что он делает, не изменит конечный результат? - person Fredou; 13.05.2014
comment
@Fredou: интерфейс просто определяет методы. Вы можете удалить его, если хотите. Цикл существует исключительно для перебора результатов: он намеренно оставлен пустым. - person StriplingWarrior; 13.05.2014
comment
Здравствуйте, спасибо за полезность Класс! Но этот класс реализует интерфейс IRelationshipPropertyBuilder. Можете ли вы предоставить это, пожалуйста? - person Ravior; 25.10.2016
comment
@Ravior: Как я уже сказал в своем предыдущем комментарии, этот интерфейс просто определяет два общедоступных метода, которые вы видите в этом классе. Если вы видите в этом ценность, просто создайте интерфейс с этими двумя методами; если нет, удалите интерфейс из своего кода. - person StriplingWarrior; 25.10.2016