EF 3.1：克服 LINQ GroupBy SQL 转换问题 [重复]

在 MS SQL Server 中，我有一个表，其中包含联系人呼叫历史记录（这是另一个表）。 由 EF 访问的实体如下：

public partial class CallbackHistory
{
    public int HistoryId { get; set; }
    public int CompanyId { get; set; }
    public int CallerId { get; set; }
    public DateTime LastCallTimeStamp { get; set; }

    public virtual CompanyDiary Caller { get; set; }
    public virtual Company Company { get; set; }
}

and

public partial class CompanyDiary
{
    public CompanyDiary()
    {
        DatiCallbackHistory = new HashSet<DatiCallbackHistory>();
    }
    public int CallerId { get; set; }
    public string NickName { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public int CompanyId { get; set; }

    public virtual Company Company { get; set; }
    public virtual ICollection<CallbackHistory> CallbackHistory { get; set; }
}

我需要获取按日期降序排列的最后 5 次对个人号码的呼叫的列表。

不幸的是，我提出了以下无法转换为 SQL 的查询：

var historyOfCalls = await
                    context.CallbackHistoryDbSet
                    .Include(historyEntry => historyEntry.Caller)
                    .Where(historyEntry => historyEntry.CompanyId == companyId)
                    .GroupBy(s => s.Caller.PhoneNumber)
                    .Select(s => s.OrderByDescending(historyEntry => historyEntry.LastCallTimeStamp).FirstOrDefault())
                    .Take(5)
                    .AsNoTracking()
                    .ToListAsync(cancellationToken).ConfigureAwait(false);

这是我得到的错误：

System.AggregateException
  HResult=0x80131500
  Message=One or more errors occurred. (The LINQ expression '(GroupByShaperExpression:
KeySelector: (c.PhoneNumber), 
ElementSelector:(EntityShaperExpression: 
    EntityType: CallbackHistory
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
)
    .OrderByDescending(historyEntry => historyEntry.LastCallTimeStamp)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.)
  Source=System.Private.CoreLib

Inner Exception 1:
InvalidOperationException: The LINQ expression '(GroupByShaperExpression:
KeySelector: (c.PhoneNumber), 
ElementSelector:(EntityShaperExpression: 
    EntityType: CallbackHistory
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
)
    .OrderByDescending(historyEntry => historyEntry.LastCallTimeStamp)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

看来问题在于我正在对导航属性进行分组。

我可以重写此查询以使其可转换为 SQL 吗？

我不知道什么时候切换到Linq 到对象 https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/#restricted-client-evaluation有了这个查询，因为我已经打电话给ToListAsync。之后我尝试移动它Select在查询中但它不编译

在查询中较早调用 ToListAsync 将导致所有其他 linq 语句无法编译，因为 ToListAsync 将返回一个 Task，因此本质上您需要首先等待结果或调用 .Result （这将阻塞当前线程）。我的建议是将查询拆分为：

1. 获取数据
2. 投影数据

e.g.

    var historyOfCalls = await context.CallbackHistoryDbSet
        .Include(historyEntry => historyEntry.Caller)
        .Where(historyEntry => historyEntry.CompanyId == companyId)
        .AsNoTracking()
        .ToListAsync(cancellationToken).ConfigureAwait(false);

    var projection = historyOfCalls 
        .GroupBy(s => s.Caller.PhoneNumber);

请记住，通过调用 group by 您将获得一个 Grouping，因此当调用 Select 时您将拥有一个 Key 属性（电话号码）和 value 属性。我建议通过使用调用者 DbSet 来反转您的查询并包含其调用者历史记录，然后从那里进行分组并使用 group by 上的一些重载来选择将值更正到 TV 中。

    var callers = await context.CompanyDiaryDbSet
        .Include(c => c.CallbackHistory)
        .Where(c=> c.CompanyId == companyId)
        .AsNoTracking()
        .ToListAsync(cancellationToken).ConfigureAwait(false);