EF 3.1: Overcome LINQ GroupBy SQL translation problem

Question

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

The *query* has a problem to begin with. LINQ can't produce something that can't be represented in SQL. What would the *SQL* query you want look like? What is `Select(s => s.OrderByDescending(historyEntry => historyEntry.LastCallTimeStamp).FirstOrDefault())` supposed to return? You can't put the `ORDER BY` clause inside the `SELECT` clause. Are you trying to emulate T-SQL's `FIRST() OVER(ORDER BY...)`? That's a reporting query, and ORMs are *not* built to handle those. — Panagiotis Kanavos, Jan 24 '20 at 09:40
Another issue - `Select(s => s.OrderByDescending(historyEntry => historyEntry.LastCallTimeStamp).FirstOrDefault())` returns an entire row, so you wouldn't be able to use `FIRST()` either. Are you trying to retrieve the latest record per company and customer? In T-SQL you'd do this with a CTE that calculates ranking eg using `ROWNUMBER()` and an outer query that returns only rows with RN=1. You can't express that in LINQ. It's not done with GROUP BY either — Panagiotis Kanavos, Jan 24 '20 at 09:44
Sometimes it is easier to use the "other" LINQ syntax. Example (Northwind database): `var categoryId = 2; var productQuery = (from item in context.Categories let s = item.Products.OrderByDescending(h => h.ProductName).FirstOrDefault() where item.CategoryID == categoryId group item.Products by s.ProductName into g select g);` which is a similar query, it is using a `let` statement allowing to pick the first item from a descended sorted list.Try to reformulate your query in a similar way. — Matt, Jan 24 '20 at 10:00
@PanagiotisKanavos Select is here to return the whole record. See [here](https://stackoverflow.com/a/470511/15186). I'm trying to retreive the latest 5 history records by company and distinct phone number. I'm using GroupBy instead of distinct as it may be more appropriate. — , Jan 24 '20 at 10:48
@OlivierMATROT and that's something not possible in *SQL*. To get the last 5 records you need ROWCOUNT in a CTE or nested query, not GROUP BY or DISTINCT. Both GROUP BY and DISTINCT *eliminate* records according to some fields, when you want to order and rank them according to those fields — Panagiotis Kanavos, Jan 24 '20 at 10:57
@OlivierMATROT *without* ROWCOUNT, which, btw is available in all supported SQL Server versions, you'd need to JOIN to a TOP 5 subquery that selected the top 5 records for every row in the outer query. I think you can do that with LINQ but the SQL query itself is far less efficient than using ROWCOUNT — Panagiotis Kanavos, Jan 24 '20 at 10:59
@PanagiotisKanavos I'm ok to run a SQL text query with a CTE for that. Can you provide a pseudo SQL query for me to start with ? Add an answer if you want. — , Jan 24 '20 at 11:03

score 0 · Answer 1 · answered Jan 24 '20 at 09:42

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:

Getting the data
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);

EF 3.1: Overcome LINQ GroupBy SQL translation problem

1 Answers1

Linked