EF Core — невозможно выполнить агрегатную функцию для выражения, содержащего агрегат или подзапрос

Проблема в том, что EF преобразовал ваше выражение LINQ в синтаксически (и даже семантически) правильный запрос, но столкнулся с ограничением SQL Server. Хитрость заключается в том, чтобы попытаться найти эквивалентную форму, которая генерирует другой, но поддерживаемый SQL-запрос.

Если бы мне пришлось закодировать это на SQL Server, я бы использовал CROSS APPLY (SELECT TOP 1 ...) для поиска предыдущей (начальной) записи аудита. Что-то вроде:

SELECT AVG(CAST(DATEDIFF(second, prior.AuditDate, a.AuditDate) AS float))
FROM Audit_Opportunity AS a
CROSS APPLY (
    SELECT TOP 1 a0.*
    FROM Audit_Opportunity a0
    WHERE a0.OpportunityId = a.OpportunityId
    AND a0.Id < a.Id
    AND a0.PreviousStatus = 1
    ORDER BY a0.Id DESC
) prior
WHERE a.Status = 3
AND a.PreviousStatus = 2

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

var result = dbContext.Audit_Opportunity
    .Where(a => a.Status == 3 && a.PreviousStatus == 2)
    .Select(a => new
    {
        Duration = dbContext.Audit_Opportunity
            .Where(a0 => a0.OpportunityId == a.OpportunityId
                         && a0.Id < a.Id
                         && a0.PreviousStatus == 1)
            .OrderByDescending(a0 => a0.Id)
            .Select(a0 => EF.Functions.DateDiffSecond(a0.AuditDate, a.AuditDate))
            .FirstOrDefault()
    })
    .Select(t => (float?)t.Duration) // Convert duration to float
    .Average(); // Calculate the average

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

Я нашел следующее «Как мне написать этот перекрестный запрос в LINQ-to-SQL?» вопрос, на который был многообещающий ответ. Адаптируя этот ответ к вашему случаю, я придумал следующие запросы LINQ (один с использованием синтаксиса запросов LINQ, а другой с использованием синтаксиса метода LINQ).

var result = 
    (from a in dbContext.Audit_Opportunity
     where a.Status == 3 && a.PreviousStatus == 2
     from a0 in (
         from a0 in dbContext.Audit_Opportunity
         where a0.OpportunityId == a.OpportunityId
           && a0.Id < a.Id
           && a0.PreviousStatus == 1
         orderby a0.Id descending
         select a0
         )
         .Take(1)
     select (float?)EF.Functions.DateDiffSecond(a0.AuditDate, a.AuditDate)
    ).Average();

var result = dbContext.Audit_Opportunity
    .Where(a => a.Status == 3 && a.PreviousStatus == 2)
    .SelectMany(a => dbContext.Audit_Opportunity
        .Where(a0 => a0.OpportunityId == a.OpportunityId
                    && a0.Id < a.Id
                    && a0.PreviousStatus == 1)
        .OrderByDescending(a0 => a0.Id)
        .Take(1)
        .Select(a0 => (float?)EF.Functions.DateDiffSecond(a0.AuditDate, a.AuditDate))
    )
    .Average();

Они не проверены, но, надеюсь, их можно будет перевести в работоспособную форму SQL.

Другая идея (которую я не конкретизировал) может заключаться в том, чтобы добавить к подзапросу второе значение выбора (например, a0.Id), чтобы его больше нельзя было транслировать как скалярный подзапрос. Чтобы избежать оптимизации, нам пришлось бы ссылаться на это значение каким-нибудь безобидным способом во внешнем запросе, например where inner.Id > 0. Это некрасиво, но может потребовать другого перевода.

Если ничего не помогает, запасным вариантом является кодирование и выполнение необработанного SQL-запроса.

(Для справки, вот скрипт , содержащий данные, исходные опубликованные запросы и запрос перекрестного применения сверху.)