Problem with EF OrderBy after migration to .net core 3.1

Question

Consider this code:

_dbContext.Messages
    .GroupBy(m => new
        {
            MinId = m.SenderId <= m.RecipientId ? m.SenderId : m.RecipientId,
            MaxId = m.SenderId > m.RecipientId ? m.SenderId : m.RecipientId
        })
        .Select(gm => gm.OrderByDescending(m => m.SentAt).FirstOrDefault());

By this I group all dialogues of users by their Id's no matter who sent the message. Then I order messages by SentAt date inside the groups and select one last message out of each dialogue. The thing is that this code worked and more over it translated all of it into pure T-Sql (I user SQL Server Profiler to check that). But then I decided to move my Projects from Core 2.1 to 3.1 and now I get this:

The LINQ expression '(GroupByShaperExpression: KeySelector:

new { 
    MinId = (CASE
        WHEN ((m.SenderId) <= (m.RecipientId)) THEN (m.SenderId)
        ELSE (m.RecipientId)
    END), 
    MaxId = (CASE
        WHEN ((m.SenderId) > (m.RecipientId)) THEN (m.SenderId)
        ELSE (m.RecipientId)
    END)
 }, 
ElementSelector:(EntityShaperExpression: 
    EntityType: Message
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
).OrderByDescending(m => m.SentAt)

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.

Any idea to fix this up would be appreciated.

P.S. I know that I can dive into T-SQL and write the stored procedure for it but I am still looking a way to implement it with Linq to Entity.

If you can trust that the most recent message also has the highest `MessageID` you could get `Max(MessageID)` per `MinId,MaxId` pair and retrieve the belonging messages by a `Contains` query (all in 1 expression). That produces somewhat more decent SQL than the otherwise inevitable work-around as show by @Ivan. However, this comes uncomfortably close to adding business meaning to surrogate keys. — Gert Arnold, Dec 15 '19 at 19:17
@GertArnold thanks. But the problem is - my MessageId is of type Guid. — Alex, Dec 15 '19 at 19:23

score 27 · Accepted Answer · answered Dec 15 '19 at 18:54

27

Unfortunately currently EF Core 3.0 / 3.1 only supports server translation of GroupBy with projection of key / aggregates (similar to SQL).

This is unacceptable since although EF6 also has no client evaluation, it was able to successfully translate such queries.

Until the GroupBy translation issues get resolved, the workaround is to replace the GroupBy with 2 correlated subqueries - first containing just the grouping key and second containing the group elements.

In your case it would be something like this:

var source = _dbContext.Messages
    .Select(m => new
    {
        Key = new
        {
            MinId = m.SenderId <= m.RecipientId ? m.SenderId : m.RecipientId,
            MaxId = m.SenderId > m.RecipientId ? m.SenderId : m.RecipientId
        },
        Message = m
    });

var query = source.Select(e => e.Key).Distinct()
    .SelectMany(key => source
        .Where(e => e.Key.MinId == key.MinId && e.Key.MaxId == key.MaxId)
        .Select(e => e.Message)
        .OrderByDescending(m => m.SentAt)
        .Take(1));

answered Dec 15 '19 at 18:54

Ivan Stoev

195,425
15
312
343

I have been redirected here from one of my [question](https://stackoverflow.com/questions/59892940/ef-3-1-overcome-linq-groupby-sql-translation-problem).Would'nt the first query retreive all the datatable on the client? This might be hurtfull. – Jan 24 '20 at 13:54
2

@OlivierMATROT Nope, that's the whole idea - the above technique creates server translatable query. Note that `source` is `IQueryable` (hence is not executed) and contains the common query part (with filters if any) before `GroupBy`. – Ivan Stoev Jan 24 '20 at 15:29
Ok I see now. Still trying to wrap my head around your solution to my problem. – Jan 24 '20 at 15:34
@OlivierMATROT Applying it to your scenario would be like this: `var sourceQuery = context.CallbackHistoryDbSet .Where(e => e.CompanyId == companyId) .Select(e => new { Key = new { e.Caller.PhoneNumber }, Entry = e }); var query = sourceQuery .Select(e => e.Key).Distinct() .SelectMany(key => sourceQuery .Where(e => e.Key.PhoneNumber == key.PhoneNumber) .Select(e => e.Entry) .OrderByDescending(e => e.LastCallTimeStamp).Take(1)) .Include(e => e.Caller) .Take(5) .AsNoTracking();` – Ivan Stoev Jan 24 '20 at 15:39
Nice. Tanks for that. – Jan 24 '20 at 15:47

Problem with EF OrderBy after migration to .net core 3.1

1 Answers1

Linked

Related