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

Question

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

In our application, we have the following LINQ operation:

 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 onto an object without an entity before calling Where as follows:

 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 without an internal exception:

EntityCommandCompilationException: The nested query is not supported. Operation1='GroupBy' Operation2='MultiStreamNest'

Removing Where , however, makes it work, which only makes me believe in this particular 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 do the above work: select a non-entity object, and then use Where and GroupBy as a result of the request? Adding ToList after Select works, but a large set of sources makes this impossible (it will query the database and then group the logic in C #).

Answer 1

This exception comes from this piece of code in the source 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; } 

I must admit: it is not clear what is happening here and there is no technical design document that reveals all the strategies for building EF queries. 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 small look behind the curtains. I just tried OrderBy in my own, which is exactly reproduced, and it worked. Therefore, I am sure that if you ...

 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 disappear.