为什么“选择”、“位置”和“分组依据”的这种组合会导致异常
本文关键字:组合 异常 分组依据 位置 选择 为什么 | 更新日期: 2023-09-27 18:32:00
我有一个简单的服务表结构,每个服务都有许多设施。在数据库中,这是一个Service
表和一个Facility
表,其中Facility
表引用了服务表中的行。
在我们的应用程序中,我们有以下 LINQ 工作:
Services
.Where(s => s.Facilities.Any(f => f.Name == "Sample"))
.GroupBy(s => s.Type)
.Select(g => new { Type = g.Key, Count = g.Count() })
但是由于我无法控制的原因,源集在Where
调用之前被投影到非实体对象,如下所示:
Services
.Select(s => new { Id = s.Id, Type = s.Type, Facilities = s.Facilities })
.Where(s => s.Facilities.Any(f => f.Name == "Sample"))
.GroupBy(s => s.Type)
.Select(g => new { Type = g.Key, Count = g.Count() })
但这引发了以下异常,没有内部例外:
EntityCommandCompilationException: The nested query is not supported. Operation1='GroupBy' Operation2='MultiStreamNest'
但是,删除Where
使其正常工作,这使我相信它仅在这种特定的方法调用组合中:
Services
.Select(s => new { Id = s.Id, Type = s.Type, Facilities = s.Facilities })
//.Where(s => s.Facilities.Any(f => f.Name == "Sample"))
.GroupBy(s => s.Type)
.Select(g => new { Type = g.Key, Count = g.Count() })
有没有办法使上述工作:选择非实体对象,然后对生成的可查询对象使用 Where
和 GroupBy
?在Select
工作后添加ToList
,但大型源代码集使这不可行(它将在数据库上执行查询,然后在 C# 中执行分组逻辑)。
此异常源自 EF 源中的这段代码...
// <summary>
// Not Supported common processing
// For all those cases where we don't intend to support
// a nest operation as a child, we have this routine to
// do the work.
// </summary>
private Node NestingNotSupported(Op op, Node n)
{
// First, visit my children
VisitChildren(n);
m_varRemapper.RemapNode(n);
// Make sure we don't have a child that is a nest op.
foreach (var chi in n.Children)
{
if (IsNestOpNode(chi))
{
throw new NotSupportedException(Strings.ADP_NestingNotSupported(op.OpType.ToString(), chi.Op.OpType.ToString()));
}
}
return n;
}
我不得不承认:这里发生了什么并不明显,也没有技术设计文档披露 EF 的所有查询构建策略。但是这段代码...
// We can only pull the nest over a Join/Apply if it has keys, so
// we can order things; if it doesn't have keys, we throw a NotSupported
// exception.
foreach (var chi in n.Children)
{
if (op.OpType != OpType.MultiStreamNest
&& chi.Op.IsRelOp)
{
var keys = Command.PullupKeys(chi);
if (null == keys
|| keys.NoKeys)
{
throw new NotSupportedException(Strings.ADP_KeysRequiredForJoinOverNest(op.OpType.ToString()));
}
}
}
在窗帘后面偷看一眼。我只是在我自己的案例中尝试了一个完全复制您的OrderBy
,它奏效了。所以我很确定,如果你这样做...
Services
.Select(s => new { Id = s.Id, Type = s.Type, Facilities = s.Facilities })
.OrderBy(x => x.Id)
.Where(s => s.Facilities.Any(f => f.Name == "Sample"))
.GroupBy(s => s.Type)
.Select(g => new { Type = g.Key, Count = g.Count() })
异常将消失。