ef-core nested table aggregate functions give "NavigationExpandingExpressionVisitor failed"

EF Core still can't translate nested aggregates on GroupBy result (grouping).

You have to pre calculate the nested aggregates in advance by utilizing the element selector of GroupBy (or element in query syntax group element by key):

from scan in Scans
group new { scan.ScanDate, Qty = scan.Items.Sum(i => i.Qty) } // <--
by new { scan.ScanUser.Name, scan.ScanUser.Id } into g
select new
{
    UserId = g.Key.Id,
    Name = g.Key.Name,
    LastSyncTime = g.Max(x => x.ScanDate),
    ScanItems = g.Sum(x => x.Qty) // <--
}

**Update: ** for SqlServer the above LINQ query translates to SQL query like this:

 SELECT [s1].[Id] AS [UserId], [s1].[Name], MAX([s0].[ScanDate]) AS [LastSyncTime], SUM((
      SELECT SUM([s].[Qty])
      FROM [ScanItem] AS [s]
      WHERE [s0].[Id] = [s].[InventoryScanId])) AS [ScanItems]
  FROM [Scan] AS [s0]
  INNER JOIN [ScanUser] AS [s1] ON [s0].[ScanUserId] = [s1].[Id]
  GROUP BY [s1].[Name], [s1].[Id]

which as mentioned in the comment generates SQL execution exception "Cannot perform an aggregate function on an expression containing an aggregate or a subquery.".

So you really need another approach - use left join to flatten the result set before grouping, and then perform the grouping / aggregates on that set:

from scan in Scans
from item in scan.Items.DefaultIfEmpty() // <-- left outer join
group new { scan, item } by new { scan.ScanUser.Name, scan.ScanUser.Id } into g
select new
{
    UserId = g.Key.Id,
    Name = g.Key.Name,
    LastSyncTime = g.Max(x => x.scan.ScanDate),
    ScanItems = g.Sum(x => (double?)x.item.Qty) ?? 0
};

which now translates to hopefully valid SqlServer SQL query:

  SELECT [s1].[Id] AS [UserId], [s1].[Name], MAX([s].[ScanDate]) AS [LastSyncTime], COALESCE(SUM([s0].[Qty]), 0.0E0) AS [ScanItems]
  FROM [Scan] AS [s]
  LEFT JOIN [ScanItem] AS [s0] ON [s].[Id] = [s0].[InventoryScanId]
  INNER JOIN [ScanUser] AS [s1] ON [s].[ScanUserId] = [s1].[Id]
  GROUP BY [s1].[Name], [s1].[Id]