efcore: Query: Join after GroupByAggregate throws when join key uses grouping key or aggregate function

All the queries described in this issue were already working. They may have started working in 3.1 or in earlier previews of 5.0.

@MolallaComm We plan to sync with the OData team to get a better understanding of some of the queries that are generated.

Just wondering if this issue is going to be addressed and when. At the moment, it is blocking us from upgrading from dotnet core 2.2 to 3.1 and I looking at the thread, I suspect others are in the same boat.

Imho this issue should be handled with pressure and not being open for several years. Let’s have an example. You have 1.000.000 customers. Every customer has some orders with a date. And now you need the 50 customers (paging) with the newest orders. In SQL thats something like (without TOP 50):

SELECT C.*, O.DATE
FROM CUSTOMER C
JOIN (
  SELECT CUSTOMER_ID, MAX(DATE) DATE
  FROM ORDERS
  GROUP BY CUSTOMER_ID) O ON C.CUSTOMER_ID = O.CUSTOMER_ID
ORDER BY O.DATE DESC

This I currently impossible with EF Core. Reading all 1.000.000 customers and select 50 of them is not a solution. The only solution I found is to write raw SQL.

    public class Foo
    {
        public int Id { get; set; }

        public ICollection<FooBar> Bars { get; } = new List<FooBar>();
    }

    public class FooBar
    {
        public virtual Foo Foo { get; set; }

        public virtual int FooId { get; set; }

        public virtual Bar Bar { get; set; }

        public virtual int BarId { get; set; }
    }

    public class Bar
    {
        public virtual int Id { get; set; }

        public virtual ICollection<FooBar> Foos { get; } = new List<FooBar>();

        public virtual ICollection<BarBaz> Bazs { get; } = new List<BarBaz>();
    }

    public class BarBaz
    {
        public virtual Bar Bar { get; set; }

        public virtual int BarId { get; set; }

        public virtual Baz Baz { get; set; }

        public virtual int BazId { get; set; }
    }

    public class Baz
    {
        public virtual int Id { get; set; }

        public virtual ICollection<BarBaz> Bars { get; } = new List<BarBaz>();
    }

    public class BazResult
    {
        public Baz Baz { get; set; }

        public int BazBarsCount { get; set; }
    }

    public class EfTestDbContext : DbContext
    {
        public virtual DbSet<Foo> Foos { get; private set; }

        public virtual DbSet<Bar> Bars { get; private set; }

        public virtual DbSet<Baz> Bazs { get; private set; }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseSqlServer("Data Source=localhost; Initial Catalog=EfTest; Integrated Security=true; MultipleActiveResultSets=True;");
        }

        protected override void OnModelCreating(ModelBuilder model)
        {
            model.Entity<FooBar>().HasKey(e => new {e.FooId, e.BarId});
            model.Entity<BarBaz>().HasKey(e => new {e.BarId, e.BazId});
        }
    }

    public class Program
    {
        public static async Task Main(string[] args)
        {
            await using var context = new EfTestDbContext();

            const int fooId = 0;
                
            var barBazsByFoo =
                from foo in context.Foos
                where foo.Id == fooId
                from fooBar in foo.Bars
                let bar = fooBar.Bar
                from barBaz in bar.Bazs
                select barBaz;

            var barBazs =
                from barBaz in barBazsByFoo
                group barBaz by barBaz.BazId
                into barBazGroups
                join barBaz in barBazsByFoo on barBazGroups.Key equals barBaz.BazId
                select new BazResult
                {
                    Baz = barBaz.Baz,
                    BazBarsCount = barBazGroups.Count()
                };

            await barBazs.ToListAsync();
        }
    }

Processing of the LINQ expression 'GroupByShaperExpression: KeySelector: b.BazId, ElementSelector:EntityShaperExpression: EntityType: BarBaz ValueBufferExpression: ProjectionBindingExpression: Outer IsNullable: False ’ by ‘RelationalProjectionBindingExpressionVisitor’ failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

This is with EF Core SqlServer 3.0.0.

@mattheiler - While this issue is not marked as fixed, many of this scenarios could already be working. We have done work to improve implementation to make such queries translate though we did not have time to go through various queries to find edge case bugs. You can try running your query on 3.0 (or even better 3.1 preview) and it may be working already. If it is not then there is always easy work-around as mentioned here https://github.com/aspnet/EntityFrameworkCore/issues/10012#issuecomment-372830495 which is to apply key/aggregate operation in custom projection before composing join. And that already works. We would love to know what queries are still not working in 3.x release so that we can fix them in future release.

@reservoir-dogs We hope to fix this issue in the 3.0 release, which will be later this year.