Query: Aggregate function over subquery produces incorrect SQL

[GunterGladDC]

When I run this select:

var list6 = context.Customers.Select(x => new
         {
            Name = x.Name,
            InvoiceTotal = x.Invoices.Sum(p => p.InvoiceDetails.Sum(d => d.TotalAmount)),
            PaymentTotal = x.Payments.Sum(p => p.PaymentAmount)
         }
         ).ToList();

I get this error:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

My DB context:

public class AppDBContext: DbContext, { 
public DbSet<Customer> Customers { get; set; }
public DbSet<Invoice> Invoices { get; set; }
public DbSet<InvoiceDetail>  InvoiceDetails { get; set; }
}

public class Customer 
{
public string Name { get; set; }
public virtual ICollection<Invoice> Invoices { get; set; }
}

public class Invoice
{
#region Customer
public int CustomerID { get; set; }
public virtual Customer Customer { get; set; }
#endregion Customer

public virtual ICollection<InvoiceDetail> InvoiceDetails { get; set; }
}

public class InvoiceDetail 
   {
      public int InvoiceID { get; set; }
      public decimal TotalAmount { get; set; }

      public virtual Invoice Invoice { get; set; }
   }

[natemcmaster]

See also https://github.com/aspnet/MusicStore/blob/dev/src/MusicStore/Components/GenreMenuComponent.cs#L31 (Using EF 1.0.0-rc2-20051)

[mikary]

Still appears to repro on current dev.

Cleaned up version of repro:

    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new MyContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var list6 = context.Customers.Select(x => new
                {
                    Name = x.Name,
                    InvoiceTotal = x.Invoices.Sum(p => p.InvoiceDetails.Sum(d => d.TotalAmount)),
                    PaymentTotal = x.Payments.Sum(p => p.PaymentAmount)
                }
                    ).ToList();
            }
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Customer> Customers { get; set; }
        public DbSet<Invoice> Invoices { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(
                "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=NestedSum;Integrated Security=True");
        }
    }

    public class Customer
    {
        public int CustomerID { get; set; }

        public string Name { get; set; }
        public virtual ICollection<Invoice> Invoices { get; set; }

        public virtual ICollection<Payment> Payments { get; set; }
    }

    public class Invoice
    {
        public int InvoiceID { get; set; }

        public int CustomerID { get; set; }
        public virtual Customer Customer { get; set; }

        public virtual ICollection<InvoiceDetail> InvoiceDetails { get; set; }
    }

    public class InvoiceDetail
    {
        public int InvoiceDetailID { get; set; }

        public decimal TotalAmount { get; set; }

        public int InvoiceID { get; set; }
        public virtual Invoice Invoice { get; set; }
    }

    public class Payment
    {
        public int PaymentID { get; set; }
        public virtual decimal PaymentAmount { get; set; }

        public int CustomerID { get; set; }
        public virtual Customer Customer { get; set; }
    }

[tuespetre]

Related: #800