Как создавать сложные динамические запросы в EF Core 3.1 после критических изменений?

У меня есть эта функция, которая возвращает IQueryable:

private IQueryable<string> GetActiveCellPhoneNumbersUpToDate(long serviceToken, DateTime date, bool? isPrepaid = null)
{
    var to = date.AddDays(1).Date;
    var query = ViewRepository
        .All
        .Where(i => i.ServiceToken == serviceToken)
        .Where(i => i.Date < to);
    if (isPrepaid.HasValue)
    {
        query = query.Where(i => i.IsPrepaid == isPrepaid);
    }
    query = query.OrderByDescending(i => i.Date);
    var result = query
        .GroupBy(i => i.CellPhoneNumber)
        .Where(i => i.First().ActionId == (int)SubscriptionAction.Subscription)
        .SelectMany(i => i.ToList())
        .Select(i => i.CellPhoneNumber)
        .Distinct();
    return result;
}

и эта функция будет называться другой функцией только для подсчета:

var prepaidsCount = GetActiveCellPhoneNumbersUpToDate(serviceToken, DateTime.Date, true);
var postPaidsCount = GetActiveCellPhoneNumbersUpToDate(serviceToken, DateTime.Date, false);

И когда я его выполняю, я вижу критическое изменение EF 3.0, в котором говорится:

Обработка выражения LINQ 'i => i .ToList()' с помощью 'NavigationExpandingExpressionVisitor' не удалась. Это может указывать либо на ошибку, либо на ограничение в EF Core. См. https://go.microsoft.com/fwlink/?linkid=2101433. для получения более подробной информации.

Как указано в примечаниях о критических изменениях, мне нужно использовать AsEnumerable или ToList перед сложными предложениями Where, чтобы выполнить эту часть LINQ и перенести данные в ОЗУ, а затем продолжить мой запрос.

Но для больших объемов данных с необходимостью динамических запросов это абсолютно безумно и невообразимо неэффективно.

Чем заменить это? Как мы можем создавать динамические сложные запросы, которые будут транслироваться во время выполнения и возвращать только единственное скалярное значение?

Обновление. Реальные требования — это не приветственные примеры. Им нужна сложная фильтрация, сортировка, группировка и другие функции, смешанные вместе, для извлечения данных из реляционной структуры. Раньше для этих целей мы использовали хранимые процедуры. Передача пары параметров в базу данных и написание уродливого, сложно тестируемого, далеко не поддерживаемого, еженедельно набираемого, устойчивого к рефакторингу SQL-кода для выборки данных.

Теперь единственный вариант, который приходит мне на ум, — вернуться к этим уродливым хранимым процедурам. Является ли этот кошмар реальностью в EF 3.1?

Обновление 2: это мой сценарий. У меня есть таблица, в которой я храню подписку/отмену номера сотового телефона в определенных службах. Упрощенная версия этой таблицы будет выглядеть так:

create table Subscriptions
(
    Id,
    CellPhoneNumber,
    ServiceId,
    Date,
    ActionId
)

И это могут быть записи:

John,+1-541-754-3010,15,2019-10-13 12:10:06.153,1
John,+1-541-754-3010,15,2019-10-18 12:10:06.153,2

Здесь мы видим, что Джон подписался на услугу 15 и оставался в ней 5 дней, а затем отменил. Если мы хотим сообщить, сколько подписчиков у нас было на 2019-10-14, будет учитываться Джон. Потому что в то время его последним действием было зачисление. Но если мы хотим сообщить, сколько у нас было подписчиков на 2910-11-03, то последним действием Джона был выход из сервиса, и его не следует учитывать.


c# entity-framework entity-framework-core .net-core-3.1 ef-core-3.0
person mohammad rostami siahgeli    schedule 13.12.2019    source источник
comment
То, на что вы ссылаетесь, не является критическим изменением. Он обнаружил ошибки, которые уже были в вашем коде. EF 6.2 также вызывал исключение вместо использования оценки на стороне клиента. Это ошибка, а не функция. Его единственное реальное использование заключалось в том, чтобы скрыть недостатки в EF Core 1.x и 2.x (например, без GROUP BY). До сих пор ваш код не мог создать один запрос SQL для исходного запроса LINQ и получить большую часть данных на клиенте и отфильтровать их там. Излишне говорить, что это очень медленно и неэффективно.   -  person Panagiotis Kanavos    schedule 13.12.2019
comment
Почему это ошибка? Этот код работает нормально, без ошибок. По сути, у меня есть таблица подписок и отмен для множества сервисов, и все, что я хочу знать, это узнать, сколько подписчиков у сервиса было до даты. Это единственное скалярное значение, и я не хочу извлекать все данные в ОЗУ только для того, чтобы их подсчитать. Единственный способ, который я знаю сейчас, — это вернуться к хранимым процедурам, которые, я думаю, откатываются назад с точки зрения технологии.   -  person mohammad rostami siahgeli    schedule 13.12.2019
comment
Вы имеете в виду, что мой код уже переносит все данные в память?   -  person mohammad rostami siahgeli    schedule 13.12.2019
comment
В яблочко. Если вы использовали SQL Server Profiler или расширенные события, вы бы увидели, что запрос SQL был немного неожиданным.   -  person Panagiotis Kanavos    schedule 13.12.2019
comment
Например .SelectMany(i => i.ToList()) ? Как это можно перевести в SQL? Что это вообще значит? Результаты SQL не вложены, им не нужен SelectMany   -  person Panagiotis Kanavos    schedule 13.12.2019
comment
Затем, как я уже упоминал в заголовке вопроса, как мы можем создавать сложные динамические запросы в EF Core? Я имею в виду, что реальные требования требуют группировки данных, фильтрации и сортировки, а иногда и нескольких из них, смешанных вместе. Таким образом, мы всегда должны избегать EF для этих сценариев. Я прав, или я что-то пропустил здесь?   -  person mohammad rostami siahgeli    schedule 13.12.2019
comment
.Where(i => i.First().ActionId == (int)SubscriptionAction.Subscription) в группе? Похоже, он пытается разгруппировать группу и выбрать случайный ActionId. В SQL вы должны использовать GROUP BY и либо MIN(ActionID)`, либо MAX(ActionID) для получения первого или последнего идентификатора, или оконную функцию, такую ​​как First_Value или Last_Value   -  person Panagiotis Kanavos    schedule 13.12.2019
comment
@PanagiotisKanavos, до этого я отсортировал данные. Этот код работает. Это не случайное ActionId, это первое ActionId с этого момента или, другими словами, последнее действие, предпринятое подписчиком. Если последним его действием была подписка, то он в сервисе, тогда мы его засчитываем.   -  person mohammad rostami siahgeli    schedule 13.12.2019
comment
Вопрос неправильный. У вас не было сложных запросов с самого начала, это были плохие запросы, которые не имеют смысла в самом SQL. LINQ не может делать то, чего не может сам SQL. Подумайте еще раз, что вы хотите сделать, подумайте, как бы вы сделали это в SQL, и напишите этот запрос LINQ. Если запрос выглядит слишком сложным, НЕ ДЕЛАЙТЕ ЭТОГО с ORM. ORM предназначены для сопоставления объектов с реляционными таблицами. Не для запросов отчетов, которые возвращают только данные. LINQ ослабляет это ограничение, но только до определенного момента. Запросы отчетов обычно относятся к базе данных, например, в виде представлений.   -  person Panagiotis Kanavos    schedule 13.12.2019
comment
Вы не отсортировали данные. Вы не можете сортировать данные после GROUP BY просто потому, что данные исчезли — это либо ключи, либо агрегаты. Единственная причина, по которой ваш запрос работал раньше, заключается в том, что данные не были сгруппированы в базе данных.   -  person Panagiotis Kanavos    schedule 13.12.2019
comment
Вы можете легко создавать представления, которые упрощают создание отчетов, и сопоставлять их с объектами с помощью EF Core, особенно с использованием сущностей без ключа.   -  person Panagiotis Kanavos    schedule 13.12.2019
comment
Говоря о реальных требованиях, вот почему вы не можете использовать ORM и LINQ для отчетов о запросах, независимо от того, что вы видите в демонстрациях. Хотя хорошие учебники и демонстрации никогда не используют ORM для отчетов о запросах или общие репозитории. Отметьте Нет необходимости в репозиториях и единицах работы с Entity Framework Core потому что я подозреваю, что за этим ViewRepository тоже скрываются некоторые сюрпризы   -  person Panagiotis Kanavos    schedule 13.12.2019
comment
Да, я знаю, что EF Core уже является одновременно и репозиторием, и единицей работы. Этот ViewRepository не может действовать как репозиторий. Он предназначен для расширения EF Core. Такие методы, как GetIfExists, GetByGuid или GetList(List<long> ids), или другие удобные методы отсутствуют в интерфейсе EF Core. Таким образом, мы должны обернуть его.   -  person mohammad rostami siahgeli    schedule 13.12.2019
comment
Что вы пытаетесь создать с помощью этого запроса? Легче найти решение, если вы объясните свое намерение, а не то, как вы ожидали, что запрос будет выглядеть. Найти сотовые номера, чей последний звонок был с определенной подписки? Или просто найти подписки за последние X дней?   -  person Panagiotis Kanavos    schedule 13.12.2019
comment
напишите sql, а я напишу linq, все, что сказал Панайотис, верно, иногда на самом деле проще написать sql для сложных запросов, а затем преобразовать его в linq, как это делается в sql, чтобы вы поняли, как должны быть структурированы данные способами, поддерживаемыми языком запроса   -  person Seabizkit    schedule 13.12.2019

Ответы (1)


arrow_upward
2
arrow_downward

Запросы, зависящие от интервалов или текущего состояния записи, могут быть сложными. Обычно нам приходится искать подписки, которые имеют один статус в указанный период, но не имеют другого. Для этого потребуется как минимум один подзапрос или CTE. Это может быть дорого даже при индексировании, поскольку требует двух операций поиска или сканирования целевой таблицы.

Любые приемы, которые избегают этого, приветствуются. В этом конкретном случае, когда идентификаторы действий равны 1 и 2, простым способом получить активных подписчиков будет получение тех, чей MAX(ActionID) не равен 2 или меньше 2, например:

SELECT COUNT(Distinct cellnumber)
FROM Subscriptions 
WHERE Date <=@reportDate ....
GROUP by CellNumber
HAVING MAX(ActionID)<2

Эквивалент в LINQ будет

var actives= ctx.Subscriptions
                .Where(sub=>sub.Date <= reportDate )
                .GroupBy(sub=>sub.CellNumber)
                .Where(grp=>grp.Max(sub=>sub.ActionId)<2)  // Results in a HAVING clause
                .Distinct()
                .Count();

Добавляем остальные критерии:

var query = ctx.Subscriptions
                .Where(sub=>sub.Date <= reportDate && sub.ServiceToken == serviceToken);
if(isPrepaid.HasValue)
{
    query = query.Where(sub => sub.IsPrePaid==isPrepaid);
}

var actives= query.GroupBy(sub=>sub.CellNumber)
                  .Where(grp=>grp.Max(sub=>sub.ActionId)<2)
                  .Distinct()
                  .Count();

Временные таблицы SQL Server 2016

Если нам посчастливится использовать SQL Server 2016 или более позднюю версию, мы можем преобразовать Subscriptions во временную таблицу и просто подсчитать подписки с определенным состоянием в определенный момент времени. Мы могли бы просто использовать:

SELECT COUNT(DISTINCT CellPhoneNumber)
FROM Subscriptions  FOR SYSTEM_TIME AS OF @someTime
WHERE ActionID<2

EF Core не поддерживает временные таблицы напрямую, поэтому нам нужно использовать FromSqlRaw для этой части запроса:

var query = ctx.Subscriptions
                .FromSqlRaw("select * from Subscriptions FOR SYSTEM_TIME AS OF {0}",
                            reportDate)
                .Where(sub=>sub.Date <= reportDate && sub.ServiceToken == serviceToken);
if(isPrepaid.HasValue)
{
    query = query.Where(sub => sub.IsPrePaid==isPrepaid);
}

var actives= query.Distinct()
                  .Count();

В этом запросе нет группировки. Это не зависит от фактического количества или порядка значений Action, а также не путается с несколькими записями на подписку.

person Panagiotis Kanavos    schedule 13.12.2019
comment
Я не понимаю раздел темпоральных таблиц. Мы запрашиваем таблицу в определенный момент времени. В это определенное время, скажем, Джон подписался 3 раза и дважды отменил подписку. Последнее состояние Джона — подписка. Таким образом, он должен считаться подписанным в этот момент времени. Как несколько записей на подписку не создают путаницы в темпоральной таблице? - person mohammad rostami siahgeli; 13.12.2019