为什么选择在哪里和GROUPBY的组合会导致异常？ [英] Why does this combination of Select, Where and GroupBy cause an exception?

问题描述

我有每个多项设施服务的简单的表结构。在数据库中，这是一个服务表和基金表，其中基金表必须在服务表中的行引用

I have a simple table structure of services with each a number of facilities. In the database, this is a Service table and a Facility table, where the Facility table has a reference to a row in the Service table.

在我们的应用程序，我们有以下的LINQ工作：

In our application, we have the following LINQ working:

Services
    .Where(s => s.Facilities.Any(f => f.Name == "Sample"))
    .GroupBy(s => s.Type)
    .Select(g => new { Type = g.Key, Count = g.Count() })

但我无法控制的原因，源集的凡在投影到非实体对象通话，是这样的：

But for reasons beyond my control, the source set is projected to a non-entity object before the Where call, in this way:

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() })

不过，这引起了以下异常，没有内部异常：

But this raises the following exception, with no inner exception:

EntityCommandCompilationException：不支持嵌套查询。 Operation1 ='GROUPBY'操作2 ='MultiStreamNest'

删除其中，，但是，使得它的工作，这使我相信这只是在方法的这一特定组合调用：

Removing the Where, however, makes it work, which makes me believe it's only in this specific combination of method calls:

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() })

有没有一种方法，使上述工作：选择一个非实体对象，然后用其中，和 GROUPBY 上所产生的可查询？添加了ToList 的选择工作之后，但在大源集使得这不可行（它将实现对数据库的查询然后做分组在C＃逻辑）。

Is there a way to make the above work: select to an non-entity object, and then use Where and GroupBy on the resulting queryable? Adding ToList after the Select works, but the large source set makes this unfeasible (it would execute the query on the database and then do grouping logic in C#).

推荐答案

这个例外，从这段代码中的EF源发源于...

This exception originates from this piece of code in the EF source...

// <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的查询建设战略。但是，这段代码...

I have to admit: it's not obvious what happens here and there's no technical design document disclosing all of EF's query building strategies. But this piece of code...

// 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()));
        }
    }
}

给出后面的一个小偷看窗帘。我只是在我自己的精确复制你的情况下试过排序依据，和它的工作。所以我敢肯定，如果你做...

Gives a little peek behind the curtains. I just tried an OrderBy in a case of my own that exactly reproduced yours, and it worked. So I'm pretty sure that if you do...

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() })

异常会走了。

这篇关于为什么选择在哪里和GROUPBY的组合会导致异常？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！