EF 3.1: Overcome LINQ GroupBy SQL translation prob

问题:

This question already has an answer here:

Problem with EF OrderBy after migration to .net core 3.1 (1 answer)

Closed 6 months ago.

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

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

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

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

Here is the error I get:

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.

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

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

回答1:

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. Getting the data
2. Projecting the data

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

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