Why does this combination of Select, Where and GroupBy cause an exception?

Question

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.

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: The nested query is not supported. Operation1='GroupBy' Operation2='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() })

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#).

Answer 1

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

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

the exception will be gone.