EFCore 2.2 GroupBy Sum and DateDiff

Question

我正在尝试将以下SQL转换为EF Core查询，并且收到警告，指出GroupBy 和 Sum 将在本地进行评估。目前有没有写这个它将完全转换为 SQL？

SELECT UserId, ST.StatusId, SUM(DATEDIFF(MINUTE, StartDate, ISNULL(EndDate,GETDATE()))) AS Time
FROM StatusTransaction ST
WHERE
    TeamManagerId = 1
    AND StartDate >= N'01-01-2019'
    AND ISNULL(EndDate,GETDATE()) <= N'01-02-2019'
GROUP BY UserId, ST.StatusId
ORDER BY UserId

这些是我使用的 EF 查询：

var efFunction = await context
    .Where(st => st.TeamManagerId == tmId && st.StartDate >= dateFrom && (st.EndDate ?? DateTime.Now) <= dateTo)
    .GroupBy(st => new { st.UserId, st.StatusId })
    .Select(g => new
        {
            g.Key.UserId,
            g.Key.StatusId,
            Time = g.Sum(st => Microsoft.EntityFrameworkCore.EF.Functions.DateDiffMinute(st.StartDate, st.EndDate)) // null check not done on end date - (st.EndDate ?? DateTime.Now) causes an error here
        }).ToListAsync(cancellationToken).ConfigureAwait(false);
var simpleDateSubtraction = await context
    .Where(st => st.TeamManagerId == tmId && st.StartDate >= dateFrom && (st.EndDate ?? DateTime.Now) <= dateTo)
    .GroupBy(st => new { st.UserId, st.StatusId })
    .Select(g => new
    {
        g.Key.UserId,
        g.Key.StatusId,
        Time = g.Sum(st => st.EndDate.Value.Subtract(st.StartDate).Minutes)// null check not done on end date - (st.EndDate ?? DateTime.Now) causes an error here
    }).ToListAsync(cancellationToken).ConfigureAwait(false);
var groupBySimpleSum = await context
    .Where(st => st.TeamManagerId == tmId)
    .GroupBy(st => new { st.TeamManagerId, st.OperationsManagerId })
    .Select(g => new
    {
        g.Key.OperationsManagerId,
        g.Key.TeamManagerId,
        Foo = g.Sum(st => st.UserId) // nonsense but a simple column to sum, this translates fully to SQL
    }).ToListAsync(cancellationToken).ConfigureAwait(false);

Answer 1

首先，EF Core 仍然不支持转换TimeSpan操作，并且DateTime差异会产生TimeSpan，因此EF.Functions.DateDiff方法是正确的方法。

其次，它仍然只能在简单的成员访问器表达式上转换GroupBy聚合。因此，您必须预先Select GroupBy表达式：

var query = context
    .Where(st => st.TeamManagerId == tmId
        && st.StartDate >= dateFrom
        && (st.EndDate ?? DateTime.Now) <= dateTo
    )
    .Select(st => new
    {
        st.UserId,
        st.StatusId,
        Time = EF.Functions.DateDiffMinute(st.StartDate, st.EndDate ?? DateTime.Now)
    })
    .GroupBy(st => new { st.UserId, st.StatusId })
    .Select(g => new
    {
        g.Key.UserId,
        g.Key.StatusId,
        Time = g.Sum(st => st.Time)
    });

或者使用允许预先选择聚合源的GroupBy重载：

var query = context
    .Where(st => st.TeamManagerId == tmId
        && st.StartDate >= dateFrom
        && (st.EndDate ?? DateTime.Now) <= dateTo
    )
    .GroupBy(st => new { st.UserId, st.StatusId }, st => new
    {
        Time = EF.Functions.DateDiffMinute(st.StartDate, st.EndDate ?? DateTime.Now)
    })
    .Select(g => new
    {
        g.Key.UserId,
        g.Key.StatusId,
        Time = g.Sum(st => st.Time)
    });