迁移到.net core 3.1之后，EF OrderBy出现问题 [英] Problem with EF OrderBy after migration to .net core 3.1

问题描述

考虑以下代码:

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

通过这种方式，无论用户是谁发送的消息，我都按照用户ID对用户的所有对话进行分组.然后，我按组中SentAt日期的顺序对消息进行排序，然后从每个对话框中选择最后一条消息. 关键是该代码有效，并且更多地将其全部转换为纯T-Sql(我使用SQL Server Profiler进行检查).但是后来我决定将我的项目从Core 2.1迁移到3.1，现在我明白了:

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:

LINQ表达式'(GroupByShaperExpression:KeySelector:

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)

无法翻译.可以使用以下形式重写查询: 进行翻译，或通过插入来明确切换到客户评估 调用AsEnumerable()，AsAsyncEnumerable()，ToList()或 ToListAsync().请参阅 https://go.microsoft.com/fwlink/?linkid=2101038 为了 更多信息.

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.我知道我可以深入研究T-SQL并为其编写存储过程，但是我仍在寻找一种使用Linq to Entity来实现它的方法.

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.

推荐答案

不幸的是，当前EF Core 3.0/3.1仅支持GroupBy的服务器转换，并带有键/聚集的投影(类似于SQL).

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

这是不能接受的，因为尽管EF6也没有客户端评估，但它能够成功翻译此类查询.

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

在解决GroupBy转换问题之前，解决方法是将GroupBy替换为2个相关的子查询-第一个仅包含分组键，第二个包含group元素.

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

这篇关于迁移到.net core 3.1之后，EF OrderBy出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！