Weakness regarding groupings over left joins

[jtheisen]

Consider this query:

from e in db.MyEntities

join o in db.MyOtherEntities
on e.Foo equals o.Foo into others
from o in others.DefaultIfEmpty()

// where e.Value1 == e.Value1 (see below)

select new
{
  Value1 = e.Value1,
  Value2 = o.Value2
}

It's one of the ways to write a left join, the one that also works in linq to objects.

When you call this query query and do a group by over it like this:

from e in query group e by new { } into g select g.Count()

you get a NotSupportedException:

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

If, however, the commented-out where clause above is put in, it works.

It also works if the group-by groups on something constant from the source itself:

from e in query group e by e.SomePropertyThatsActuallyConstant into g select g.Count()

And finally it works when the original query is rewritten as a subquery:

from e in db.MyEntities
from o in db.MyOtherEntities.Where(o => o.Foo == e.Foo).DefaultIfEmpty()

Why is this bizarre query important you ask?

I came across this while using DevExpress's DevExtreme.AspNet.Data library that is used to connect an in-browser data grid to a linq-based data source.

query is what I put in, the grouping is what the data library put on top.

I believe the way it does the somewhat bizarre grouping over a new { } is actually a natural way to implement total summaries in those cases where you also want to implement group summaries. The problem then manifests if you have total summaries, but no group summaries (which would otherwise appear in the now trivial new { }

While libraries like DevExtreme.AspNet.Data could work around it, and users like me can work around it, it's a headache especially for those not aware of what's going on.

So in conclusion, I think think it's best if this is solved on the entity framework level. Apparently entity framework has no fundamental problem doing the query, otherwise the workarounds wouldn't be so simple.

For the sake of completeness, here's the pull request I originally put on DevExtreme.AspNet.Data and there's a ticket on the DevExpress ticket system lamenting the same thing.

I put up a StackOverflow Q/A to educate about the workarounds for users.

[divega]

@jtheisen I am looking at this now and I am unable to repro based on the code you provided.

Here is the attempt I made. Could make any necessary changes?

using System.Data.Entity;
using System.Linq;

namespace ReproEF6_34
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new MyContext())
            {
                var query = from e in db.MyEntities
                            join o in db.MyOtherEntities
                            on e.Foo equals o.Foo into others // "on new" seems to be a typo 
                            from o in others.DefaultIfEmpty()

                                // where e.Value1 == e.Value1 (see below)

                            select new
                            {
                                Value1 = e.Value1,
                                Value2 = o.Value2
                            };
                var count = from e in query group e by new { } into g select g.Count();
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<MyEntity> MyEntities { get; set; }
        public DbSet<MyOtherEntity> MyOtherEntities { get; set; }
    }

    public class MyOtherEntity
    {
        public int Id { get; set; }
        public string Foo { get; set; }
        public string Value2 { get; set; }
    }

    public class MyEntity
    {
        public int Id { get; set; }
        public string Foo { get; set; }
        public string Value1 { get; set; }
    }
}

[jtheisen]

You need to add this after defining count:

count.ToArray();

:-)

(Perhaps I wasn't clear enough, the exception gets thrown on executing the query. And the "new" was indeed a typo.)