EF 3.1：克服LINQ GroupBy SQL转换问题 [英] EF 3.1: Overcome LINQ GroupBy SQL translation problem

问题描述

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

In MS SQL Server I have a table that contains a history of calls to contacts (that is another table). Accessed by EF, The Entities are the following:

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; }
}

和

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个针对个人电话号码的通话的列表

I need to get a list of the last 5 calls to individual numbers order by date descending.

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

I came up with the following query that could not be translated to SQL, unfortunately:

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.

似乎问题出在我将导航属性分组的事实。

It seems that the problem lies in the fact that I'm grouping on a navigation property.

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

Can I rewrite this query to make it translatable to SQL?

我不知道何时切换到对对象的限制与该查询一样，因为我已经调用了 ToListAsync 。在查询中 Select 之后，我尝试将其移动，但它无法编译

I have no clue when to switch to Linq to objects with this query As I already have a call to ToListAsync. I've tried to move it after Select in the query but it does not compile

推荐答案

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

Calling ToListAsync earlier in your query will result all other linq statements to not compiling because to ToListAsync will return a Task so essentially your will need to await to result first or call .Result (which will be a blocking for the current thread). My suggestion here would be to split up the query in:

1. 获取数据


2. 投影数据

例如

    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);

请记住，通过呼叫组可以得到分组< T，电视>，因此在呼叫选择时，您具有按键属性（电话号码）和和值属性。我建议您通过使用调用方DbSet来反转查询，并包括其调用方历史记录，然后从那里进行分组，并使用group by上的一些重载来选择将值更正为TV。

Remember that by calling group by you get a Grouping< T, TV >, so when call Select you have a Key property (the phone number) and and value property. I would suggest reversing you get query by using the caller DbSet and include its caller history, then grouping from there and use some of the overloads on group by to select to correct values into TV.

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

这篇关于EF 3.1：克服LINQ GroupBy SQL转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！