Агрегат агрегата с EF Core Linq2Sql

У меня есть проект ASP.NET Core 2.2 с EF Core 2.2 Code-First DB. У меня есть следующие сущности:

  • Здание, которое в основном представляет собой адрес с некоторыми другими важными данными.
  • Этаж, содержащий номер этажа. Здание может быть многоэтажным. На этаже должно быть ровно одно здание, в котором он находится.
  • Комната, в которой есть номер. На этаже может быть несколько комнат. В комнате должен быть ровно один этаж.
  • Рабочая группа, которая содержит информацию о количестве сотрудников в группе, о том, активна ли группа и когда группа начала работать (что может произойти в будущем).
  • RoomOccupancy, который представляет собой соединительную таблицу между рабочей группой и комнатой и показывает, в какой комнате находится / была / будет рабочая группа.

Мне нужен список зданий с названием здания, сколько в нем этажей, сколько комнат в здании (не этаж) и сколько людей в настоящее время работает в здании.

В настоящее время я могу получить все данные, но переведенный SQL не является оптимальным и требует много обращений к БД. Мне удалось вручную написать одну инструкцию SQL select (с внутренним выбором) для решения этой проблемы, поэтому я знаю, что это должно быть возможно с помощью одного запроса.

dbContext.Buildings.Select(x=> new BuildingDatableElementDTO(){
            BuildingId = b.Id,
            Name = b.Name,
            FloorCount = b.Floors.Count(),
            //this is the part where problems start,
            //this translates to multiple SQL statements
            RoomCount = b.Floors.Sum(f=>f.Rooms.Count()),
            // I replaced the next line with
            // CurrentWorkerCount = 10, but a solution would be nice
            CurrentWorkerCount = b.Floors.Sum(f=>f.Rooms
              .Sum(r=>r.RoomOccupancies
                 .Where(o=>!o.WorkGroup.IsFinished && o.WorkGroup.StartDate < Datetime.Now).
                 .Sum(w => w.NumberOfEmployees)
                 ))),
    }).ToList();

В целях тестирования я заменил лямбда CurrentWorkerCount на CurrentWorkerCount = 10, потому что я могу понять, трудно ли его перевести в SQL, но все равно не удается создать один оператор SQL с помощью RoomCount.

Ведение журнала с информационным уровнем показывает следующее: «Выражение LINQ '« Sum () »' не может быть переведено и будет оцениваться локально» для каждого здания, которое имеет хотя бы один этаж. Затем у меня есть одна большая команда DbCommand (слишком длинная для копирования), затем одна команда DbCommand для каждого здания, которая считает количество комнат.

Я читал, что есть проблемы с агрегатами в EF Core 2.1, но я думаю, что для ORM не должно быть сложной задачей преобразовать эту проекцию в один запрос.

Я что-то делаю не так или это возможности LINQ и EF Core? Думаю, раньше я мог легко сделать это с помощью не-Core EF. Я читал о некоторых обходных путях для GroupBy и агрегатов, но в моем случае это не помогло.

ОБНОВЛЕНИЕ

Вот сгенерированный журнал (только интересные части). Я использую специальное решение для фильтрации, сортировки и разбиения по страницам, которое отлично справляется с простыми проблемами. В этом примере нет фильтрации, сортировка по названию зданий и базовая выборка (пропустить 0, взять 15). В базе данных только минимальное количество тестовых данных (15 зданий: одно по 1 этажу, другое - 2, из них в одном - 1 комната, в котором 1 рабочая группа со 100 сотрудниками). У меня также есть мягкое удаление с глобальным фильтром, настроенным для флага IsDeleted. Я не думаю, что эти вещи влияют на результаты, но вот они, может быть, они влияют.

  • Выражение LINQ Sum () не может быть переведено и будет вычислено локально.
  • Выражение LINQ Sum () не может быть переведено и будет вычислено локально.
  • Выражение LINQ Sum () не может быть переведено и будет вычислено локально.
  • Выражение LINQ Sum () не может быть переведено и будет вычислено локально.
  • Выражение LINQ Sum () не может быть переведено и будет вычислено локально.
  • Выражение LINQ Sum () не может быть переведено и будет вычислено локально.
  • Выражение LINQ Sum () не может быть переведено и будет вычислено локально.
  • Выражение LINQ Sum () не может быть переведено и будет вычислено локально.
  • Выполненная команда DbCommand ("2" мс) [Parameters = ["@__ p_0 = '?' (DbType = Int32), @__ p_1 = '?' (DbType = Int32) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT CONVERT(VARCHAR(36), [x].[Id]) AS [BuildingId], [x].[Name], (
    SELECT COUNT(*)
    FROM [Floors] AS [x0]
    WHERE ([x0].[IsDeleted] = 0) AND ([x].[Id] = [x0].[BuildingId])
) AS [FloorCount], [x].[Id]
FROM [Buildings] AS [x]
WHERE [x].[IsDeleted] = 0
ORDER BY [x].[Name]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("0" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@__ Now_2 = '?' (DbType = DateTime2), @ _outer_Id3 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT SUM([x14].[NumberOfEmployees])
    FROM [RoomOccupancys] AS [x14]
    LEFT JOIN [WorkGroups] AS [k.WorkGroup2] ON [x14].[WorkGroupId] = [k.WorkGroup2].[Id]
    WHERE (([x14].[IsDeleted] = 0) AND (([k.WorkGroup2].[IsFinished] = 0) AND ([k.WorkGroup2].[StartDate] < @__Now_2))) AND ([x13].[Id] = [x14].[RoomId])
)
FROM [Rooms] AS [x13]
WHERE ([x13].[IsDeleted] = 0) AND (@_outer_Id3 = [x13].[FloorId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("0" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("0" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@__ Now_2 = '?' (DbType = DateTime2), @ _outer_Id3 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT SUM([x14].[RemainingAmount])
    FROM [RoomOccupancys] AS [x14]
    LEFT JOIN [WorkGroups] AS [k.WorkGroup2] ON [x14].[WorkGroupId] = [k.WorkGroup2].[Id]
    WHERE (([x14].[IsDeleted] = 0) AND (([k.WorkGroup2].[IsFinished] = 0) AND ([k.WorkGroup2].[StartDate] < @__Now_2))) AND ([x13].[Id] = [x14].[RoomId])
)
FROM [Rooms] AS [x13]
WHERE ([x13].[IsDeleted] = 0) AND (@_outer_Id3 = [x13].[FloorId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("0" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("0" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("0" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])
  • Выполненная команда DbCommand ("1" мс) [Parameters = ["@_ outer_Id = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT (
    SELECT COUNT(*)
    FROM [Rooms] AS [x4]
    WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId])
)
FROM [Floors] AS [x3]
WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId])
  • Выполненная команда DbCommand ("0" мс) [Parameters = ["@_ outer_Id2 = '?' (DbType = Guid) "], CommandType = 'Text', CommandTimeout = '30 ']"
SELECT [x10].[Id]
FROM [Floors] AS [x10]
WHERE ([x10].[IsDeleted] = 0) AND (@_outer_Id2 = [x10].[BuildingId])

c# asp.net-core entity-framework-core ef-core-2.2
person gyomihaly    schedule 25.06.2019    source источник
comment
Не всегда возможно сгенерировать эффективный SQL с помощью трансляции LINQ. Вы можете показать SQL-запрос? В вашем коде смешиваются x и b? Вы говорите, что используете LINQ to SQL, но похоже, что используете Linq to EF Core 2.1?   -  person NetMage    schedule 25.06.2019
comment
Обратите внимание, что несколько запросов SQL не всегда менее эффективны, чем один, в зависимости от распределения данных.   -  person NetMage    schedule 25.06.2019
comment
Создается ли один оператор SQL для Floors или Building для Sum?   -  person NetMage    schedule 25.06.2019
comment
@NetMage Обновлено сгенерированным журналом. Я не думаю, что это неэффективное решение, поскольку в базе данных почти не было данных. Да, и это журнал с реальным CurentWorkerCount, а не = 10.   -  person gyomihaly    schedule 25.06.2019
comment
@NetMage только что проверил CurrentWorkerCount = 10, он вызывает тот, у которого есть вложенный выбор, 15 раз (это количество зданий в базе данных). Моя проблема в том, что у меня может быть гораздо большее количество записей в производстве. Даже если каждый вызов занимает всего 1 мс, 500 из них означают полсекунды ожидания данных, что неплохо, но не ужасно :)   -  person gyomihaly    schedule 25.06.2019
comment
Просто забудь это. Возможности формирования сгенерированного SQL очень ограничены, особенно. с EF-ядром. Вы никогда не помешаете ему генерировать несколько запросов, и я почти уверен, что большая часть запросов даже оценивается на стороне клиента, поэтому он извлекает в память гораздо больше данных, чем это строго необходимо. Кстати, повторяющиеся Sums можно заменить на SelectMany и один Sum, что, вероятно, сгенерирует немного менее неэффективный SQL.   -  person Gert Arnold    schedule 25.06.2019
comment
@GertArnold, спасибо, у меня было ощущение, что это так. Я попробовал SelectMany, вероятно, он немного лучше (я еще не добавил достаточно тестовых данных), но я надеялся, что у этого есть решение. Насколько я помню, EF неплохо справлялась с подобными вещами, но я не пользовался им полтора года. Думаю, я напишу хранимые процедуры или, возможно, настрою cqrs с помощью elastisearch или что-то в этом роде.   -  person gyomihaly    schedule 25.06.2019
comment
Вы, наверное, привыкли к EF6. EF6 по крайней мере генерирует достойный SQL, а это лучшее, что может сделать ORM. Ядру EF предстоит еще долгий путь.   -  person Gert Arnold    schedule 25.06.2019
comment
Хотя в целом я согласен с @GertArnold (и сказал, что EF Core не готов к производству), я думаю, что EF Core 2.1 можно использовать, а EF Core 3.x должен быть намного лучше.   -  person NetMage    schedule 25.06.2019
comment
Похоже, у вас тоже проблема с FloorCount, похоже, для этого выполняется отдельный запрос для каждого здания. Я действительно видел, где Count() может быть проблемой для EF Core в некоторых случаях. Полагаю, у вас есть критерий "не удален" в Floors виртуальной коллекции? Помогает ли добавление Include(b => b.Floors).ThenInclude(f => f.Rooms) перед Select?   -  person NetMage    schedule 25.06.2019
comment
@NetMage Я проверю, но я так не думаю. Насколько я знаю, при создании экземпляра в select нет необходимости во включениях, и, если я правильно помню, они даже оптимизируются EF. Ага, у меня настроено мягкое удаление для объектов Floor.   -  person gyomihaly    schedule 26.06.2019
comment
@NetMage Да, это в журналах: операция Include для навигации «[s] .Floors.Rooms» не нужна и была проигнорирована, потому что навигация недоступна в окончательных результатах запроса. См. go.microsoft.com/fwlink/?linkid=850303 для получения дополнительной информации.   -  person gyomihaly    schedule 26.06.2019
comment
@NetMage, когда я использовал CurrentWorker = 10 вместо long linq, проверки мягкого удаления Floor исчезли. У меня было ровно столько зданий, сколько SELECT ( SELECT COUNT(*) FROM [Rooms] AS [x4] WHERE ([x4].[IsDeleted] = 0) AND ([x3].[Id] = [x4].[FloorId]) ) FROM [Floors] AS [x3] WHERE ([x3].[IsDeleted] = 0) AND (@_outer_Id = [x3].[BuildingId]) я принес для стола.   -  person gyomihaly    schedule 26.06.2019

Ответы (2)


arrow_upward
3
arrow_downward

Я читал, что есть проблемы с агрегатами в EF Core 2.1, но я думаю, что для ORM не должно быть сложной задачей преобразовать эту проекцию в один запрос.

Вы правы, что у EF Core были (и все еще есть - последняя на тот момент версия 2.2) проблемы с переводом GroupBy и агрегатов (и не только). Но не для «не должно быть сложной задачей» - попробуйте самостоятельно преобразовать произвольное дерево выражений в псевдо-SQL, и вы быстро обнаружите, что это довольно сложная задача.

В любом случае, преобразование запросов EF Core со временем улучшается, но, как уже упоминалось, далеко от совершенства. В этом случае показателем являются вложенные агрегаты - сумма суммы / количества и т. Д. Решение состоит в том, чтобы сгладить целевой набор и применить единый агрегат. Например, переписав запрос LINQ следующим образом:

dbContext.Buildings.Select(b => new //BuildingDatableElementDTO()
{
    BuildingId = b.Id,
    Name = b.Name,
    FloorCount = b.Floors.Count(),
    // (1)
    RoomCount = b.Floors.SelectMany(f => f.Rooms).Count(),
    // (2)
    CurrentWorkerCount = b.Floors
        .SelectMany(f => f.Rooms)
        .SelectMany(r => r.RoomOccupancies)
        .Select(o => o.WorkGroup)
        .Where(w => !w.IsFinished && w.StartDate < DateTime.Now)
        .Sum(w => w.NumberOfEmployees),
})
.ToList();

переводится в один SQL (как и ожидалось):

  SELECT [e].[Id] AS [BuildingId], [e].[Name], (
      SELECT COUNT(*)
      FROM [Floors] AS [e0]
      WHERE ([e0].[IsDeleted] = 0) AND ([e].[Id] = [e0].[BuildingId])
  ) AS [FloorCount], (
      SELECT COUNT(*)
      FROM [Floors] AS [e1]
      INNER JOIN (
          SELECT [e2].[Id], [e2].[FloorId], [e2].[IsDeleted], [e2].[Name]
          FROM [Rooms] AS [e2]
          WHERE [e2].[IsDeleted] = 0
      ) AS [t] ON [e1].[Id] = [t].[FloorId]
      WHERE ([e1].[IsDeleted] = 0) AND ([e].[Id] = [e1].[BuildingId])
  ) AS [RoomCount], (
      SELECT SUM([f.Rooms.RoomOccupancies.WorkGroup].[NumberOfEmployees])
      FROM [Floors] AS [e3]
      INNER JOIN (
          SELECT [e4].*
          FROM [Rooms] AS [e4]
          WHERE [e4].[IsDeleted] = 0
      ) AS [t0] ON [e3].[Id] = [t0].[FloorId]
      INNER JOIN (
          SELECT [e5].*
          FROM [RoomOccupancies] AS [e5]
          WHERE [e5].[IsDeleted] = 0
      ) AS [t1] ON [t0].[Id] = [t1].[RoomId]
      INNER JOIN [WorkGroups] AS [f.Rooms.RoomOccupancies.WorkGroup] ON [t1].[WorkgroupId] = [f.Rooms.RoomOccupancies.WorkGroup].[Id]
      WHERE (([e3].[IsDeleted] = 0) AND (([f.Rooms.RoomOccupancies.WorkGroup].[IsFinished] = 0) AND ([f.Rooms.RoomOccupancies.WorkGroup].[StartDate] < GETDATE()))) AND ([e].[Id] = [e3].[BuildingId])
  ) AS [CurrentWorkerCount]
  FROM [Building] AS [e]
  WHERE [e].[IsDeleted] = 0
person Ivan Stoev    schedule 26.06.2019
comment
Вау, я никогда не ожидал, что ядро ​​EF сможет справиться с этим, и сдался, даже не попробовав. Увидев SQL-запрос, кажется, что все это серверная часть. Это не так плохо, как я думал. Хорошие новости. (Теперь дождитесь GroupBy) - person Gert Arnold; 26.06.2019
comment
Нет лучших слов: черт возьми. Ты обалденный! Красиво работает. Я дополню свой вопрос вашим ответом через несколько часов (нужно запустить прямо сейчас, но мне пришлось это проверить). Как сказал @GertArnold, я наполовину отказался от EF Core (я обычно не использую его в производстве, но это меньший проект, думал, что этого будет достаточно), но, думаю, мне придется углубиться в LINQ и EF Core. - person gyomihaly; 26.06.2019

arrow_upward
0
arrow_downward

Что делать, если вы не использовали свойства EF Navigations, но использовали ручное объединение с LINQ to EF?

var ans2 = (from b in dbContext.Buildings
            join f in dbContext.Floors on b.Id equals f.BuildingId into fj
            from f in fj.DefaultIfEmpty()
            join r in dbContext.Rooms on f.Id equals r.FloorId into rj
            from r in rj.DefaultIfEmpty()
            join ro in dbContext.RoomOccupancies on r.Id equals ro.RoomId
            join w in dbContext.WorkGroups on ro.WorkGroupId equals w.Id into wj
            from w in wj.DefaultIfEmpty()
            where !w.IsFinished && w.StartDate < DateTime.Now
            select new BuildingDatableElementDTO() {
                BuildingId = b.Id,
                Name = b.Name,
                FloorCount = fj.Count(),
                RoomCount = rj.Count(),
                CurrentWorkerCount = wj.Sum(w => w.NumberOfEmployees)
           })
           .ToList();
person NetMage    schedule 25.06.2019