Could someone kindly provide some assistance.

I am trying to write a query that gives me the average time between status changes of an database entity. On each update an entry is created in an audit table, this table contains a Status and PreviousStatus field to allow me to easily query the changes. I am able to get the output I want via the following query but the issue is that this is being executed in memory and not in the database which is not the best solution.

//this is the best one so far but it does the average function in memory
private async Task<double> GetAvgBetweenStatus()
{
    return (await _context.Set<Audit_Opportunity>().Where(x => x.Status == OpportunityStatus.Solutioned && x.PreviousStatus == OpportunityStatus.Qualified).Select(x => new
    {
        Date1 = x.AuditDate,
        Date2 = _context.Set<Audit_Opportunity>().Where(o => o.OpportunityId == x.OpportunityId && o.Id < x.Id && o.PreviousStatus == OpportunityStatus.Identified).OrderByDescending(o => o.Id).First().AuditDate
    })
    .Select(x =>
        new
        {
            Duration = EF.Functions.DateDiffSecond(x.Date2, x.Date1)
        })
    .ToListAsync() // <--- here it is doing the query  ) 
    .Average(x => x.Duration); // <--- this bit is done in memory / not good!
}

If I try and remove the ToListAsync() method and use AverageAsync() for example I get a “Cannot perform an aggregate function on an expression containing an aggregate or a subquery” exception.

It produces the following invalid SQL statement

SELECT AVG(CAST(DATEDIFF(second, (
SELECT TOP(1) [a0].[AuditDate]
FROM [Audit_Opportunity] AS [a0]
WHERE [a0].[OpportunityId] = [a].[OpportunityId] AND [a0].[Id] < [a].[Id] AND [a0].[PreviousStatus] = 1), [a].[AuditDate]) AS float))

FROM [Audit_Opportunity] AS [a]
WHERE [a].[Status] = 3 AND [a].[PreviousStatus] = 2

What I think I need is to add a SELECT AVG(….) FROM T like this:

** I am using datediff seconds just for testing. ***

SELECT AVG(t.Duration) FROM(
SELECT DATEDIFF(SECOND, (
    SELECT TOP(1) [a0].[AuditDate]
    FROM [Audit_Opportunity] AS [a0]
    WHERE [a0].[OpportunityId] = [a].[OpportunityId] AND [a0].[Id] < [a].[Id] AND [a0].[PreviousStatus] = 1 order by [a0].Id DESC), [a].[AuditDate]) AS [Duration]
FROM [Audit_Opportunity] AS [a]
WHERE [a].[Status] = 3 AND [a].[PreviousStatus] = 2) AS T

No matter what I try it either causes the exception or it works but produces a mess of a query which is most likely very inefficient.

Here are the main columns which are key to this query.

Any help will be greatly appreciated, thank you.