迁移到 .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，现在我明白了:

<块引用>

LINQ 表达式 '(GroupByShaperExpression: KeySelector:

new {MinId = (CASEWHEN ((m.SenderId) <= (m.RecipientId)) THEN (m.SenderId)ELSE (m.RecipientId)结尾)，MaxId = (CASEWHEN ((m.SenderId) > (m.RecipientId)) THEN (m.SenderId)ELSE (m.RecipientId)结尾)},ElementSelector:(EntityShaperExpression:实体类型:消息值缓冲区表达式:(投影绑定表达式:EmptyProjectionMember)IsNullable: 假)).OrderByDescending(m => m.SentAt)

<块引用>

无法翻译.要么以可以的形式重写查询被翻译，或通过插入显式切换到客户评估调用 AsEnumerable()、AsAsyncEnumerable()、ToList() 或ToListAsync().请参阅 https://go.microsoft.com/fwlink/?linkid=2101038 了解更多信息.

任何解决此问题的想法将不胜感激.

附言我知道我可以深入研究 T-SQL 并为其编写存储过程，但我仍在寻找使用 Linq to Entity 实现它的方法.

解决方案

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

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

在 GroupBy 翻译问题得到解决之前，解决方法是用 2 个相关子查询替换 GroupBy - 第一个只包含分组键，第二个包含组元素.

在你的情况下，它会是这样的:

var source = _dbContext.Messages.Select(m => new{钥匙 = 新{MinId = m.SenderId <= m.RecipientId ?m.SenderId : m.RecipientId,MaxId = m.SenderId >m.RecipientId ?m.SenderId : m.RecipientId},消息 = 米});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));

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.

解决方案

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

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