LINQ и Entity Framework - предотвращение подзапросов

Мне очень сложно настроить один из моих Entity Framework сгенерированных запросов в моем приложении. Это очень простой запрос, но по какой-то причине EF использует несколько внутренних подзапросов, которые, кажется, ужасно работают в DB вместо использования объединений.

Здесь мой код LINQ:

Projects.Select(proj => new ProjectViewModel()
                {
                    Name = proj.Name,
                    Id = proj.Id,
                    Total = proj.Subvalue.Where(subv =>
                        subv.Created >= startDate
                        && subv.Created <= endDate
                        &&
                        (subv.StatusId == 1 ||
                         subv.StatusId == 2))
                        .Select(c => c.SubValueSum)
                        .DefaultIfEmpty()
                        .Sum()
                })
                .OrderByDescending(c => c.Total)
                .Take(10);

EF генерирует действительно сложный запрос с несколькими подзапросами, который имеет ужасную производительность запросов, например:

SELECT TOP (10) 
[Project3].[Id] AS [Id], 
[Project3].[Name] AS [Name], 
[Project3].[C1] AS [C1]
FROM ( SELECT 
    [Project2].[Id] AS [Id], 
    [Project2].[Name] AS [Name], 
    [Project2].[C1] AS [C1]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        (SELECT 
            SUM([Join1].[A1]) AS [A1]
            FROM ( SELECT 
                CASE WHEN ([Project1].[C1] IS NULL) THEN cast(0 as decimal(18)) ELSE [Project1].[SubValueSum] END AS [A1]
                FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
                LEFT OUTER JOIN  (SELECT 
                    [Extent2].[SubValueSum] AS [SubValueSum], 
                    cast(1 as tinyint) AS [C1]
                    FROM [dbo].[Subvalue] AS [Extent2]
                    WHERE ([Extent1].[Id] = [Extent2].[Id]) AND ([Extent2].[Created] >= '2015-08-01') AND ([Extent2].[Created] <= '2015-10-01') AND ([Extent2].[StatusId] IN (1,2)) ) AS [Project1] ON 1 = 1
            )  AS [Join1]) AS [C1]
        FROM [dbo].[Project] AS [Extent1]
        WHERE ([Extent1].[ProjectCountryId] = 77) AND ([Extent1].[Active] = 1)
    )  AS [Project2]
)  AS [Project3]
ORDER BY [Project3].[C1] DESC;

Время выполнения запроса, генерируемого EF, составляет ~10 seconds. Но когда я пишу запрос вручную следующим образом:

select 
    TOP (10)
    Proj.Id,
    Proj.Name,
    SUM(Subv.SubValueSum) AS Total
from 
    SubValue as Subv
left join
    Project as Proj on Proj.Id = Subv.ProjectId
where
    Subv.Created > '2015-08-01' AND Subv.Created <= '2015-10-01' AND Subv.StatusId IN (1,2)
group by
    Proj.Id,
    Proj.Name
order by 
    Total DESC

Время выполнения почти мгновенно; ниже 30ms.

Проблема заключается в моей способности писать хорошие EF запросы с LINQ, но независимо от того, что я пытаюсь сделать (используя Linqpad для тестирования), я просто не могу написать аналогичный запрос на выполнение с LINQ\EF, поскольку я может писать вручную. Я запрашиваю таблицу SubValue и таблицу Project, но конечный результат в основном такой же: несколько неэффективных вложенных подзапросов, а не одно соединение, выполняющее работу.

Как я могу написать запрос, который имитирует написанную рукой SQL, показанную выше? Как я могу управлять фактическим запросом, созданным EF? И самое главное: как я могу получить Linq2SQL и Entity Framework для использования Joins, когда я хочу вместо вложенных подзапросов.

Ответ 1

EF генерирует SQL из выражения LINQ, которое вы предоставляете, и вы не можете ожидать, что это преобразование полностью раскроет структуру того, что вы положили в выражение, чтобы его оптимизировать. В вашем случае вы создали дерево выражений, которое для каждого проекта будет использовать свойство навигации для суммирования некоторых значений, связанных с проектом. Это приводит к вложенным подзапросам, которые вы обнаружили.

Чтобы улучшить сгенерированный SQL, вам нужно избегать перехода от проекта к субвалюту, прежде чем выполнять все операции над субвалютом, и вы можете сделать это, создав соединение (которое также вы делаете в ручном SQL):

var query = from proj in context.Project
            join s in context.SubValue.Where(s => s.Created >= startDate && s.Created <= endDate && (s.StatusId == 1 || s.StatusId == 2)) on proj.Id equals s.ProjectId into s2
            from subv in s2.DefaultIfEmpty()
            select new { proj, subv } into x
            group x by new { x.proj.Id, x.proj.Name } into g
            select new {
              g.Key.Id,
              g.Key.Name,
              Total = g.Select(y => y.subv.SubValueSum).Sum()
            } into y
            orderby y.Total descending
            select y;
var result = query.Take(10);

Основная идея состоит в объединении проектов на подвыборы, ограниченные предложением where. Для выполнения левого соединения вам понадобится DefaultIfEmpty(), но вы уже знаете это.

Объединенные значения (x) затем сгруппированы и суммирование SubValueSum выполняется в каждой группе.

Наконец, выполняется упорядочение и TOP(10).

Сгенерированный SQL по-прежнему содержит подзапросы, но я ожидал бы его более эффективного по сравнению с SQL, сгенерированным вашим запросом:

SELECT TOP (10)
    [Project1].[Id] AS [Id],
    [Project1].[Name] AS [Name],
    [Project1].[C1] AS [C1]
    FROM ( SELECT
        [GroupBy1].[A1] AS [C1],
        [GroupBy1].[K1] AS [Id],
        [GroupBy1].[K2] AS [Name]
        FROM ( SELECT
            [Extent1].[Id] AS [K1],
            [Extent1].[Name] AS [K2],
            SUM([Extent2].[SubValueSum]) AS [A1]
            FROM  [dbo].[Project] AS [Extent1]
            LEFT OUTER JOIN [dbo].[SubValue] AS [Extent2] ON ([Extent2].[Created] >= @p__linq__0) AND ([Extent2].[Created] <= @p__linq__1) AND ([Extent2].[StatusId] IN (1,2)) AND ([Extent1].[Id] = [Extent2].[ProjectId])
            GROUP BY [Extent1].[Id], [Extent1].[Name]
        )  AS [GroupBy1]
    )  AS [Project1]
    ORDER BY [Project1].[C1] DESC