Query :: we could be smarter about removing redundant joins when expanding multiple navigations

[maumar]

Repro:

    public class Contact
    {
        public int Id { get; set; }
        public int CustomerID { get; set; }
        public virtual Customer Customer { get; set; }
    }

    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Contact> Contacts { get; set; }
        public virtual ICollection<Payment> Payments { get; set; }
    }

    public class Payment
    {
        public int Id { get; set; }
        public int CustomerID { get; set; }
        public virtual Customer Customer { get; set; }

        public decimal PaymentAmount { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Contact> Contacts { get; set; }
        public DbSet<Customer> Customers { get; set; }
        public DbSet<Payment> Payments { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Customer>().HasMany(e => e.Contacts).WithOne(e => e.Customer).HasForeignKey(e => e.CustomerID);
            modelBuilder.Entity<Customer>().HasMany(e => e.Payments).WithOne(e => e.Customer).HasForeignKey(e => e.CustomerID);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(new SqlConnectionStringBuilder { DataSource = ".", InitialCatalog = "Repro3791", MultipleActiveResultSets = true, IntegratedSecurity = true }.ToString());
        }

    }

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

            using (var ctx = new MyContext())
            {
                var list5 = ctx.Contacts.Select(x => new
                {
                    Customer = x.Customer.Name,
                    PaymentTotal = x.Customer.Payments.Sum(p => p.PaymentAmount)
                }).ToList();
            }
        }
    }

Generated SQL:

SELECT (
    SELECT SUM([p].[PaymentAmount])
    FROM [Payment] AS [p]
    WHERE [x.Customer0].[Id] = [p].[CustomerID]
), [x.Customer].[Name]
FROM [Contact] AS [x]
INNER JOIN [Customer] AS [x.Customer] ON [x].[CustomerID] = [x.Customer].[Id]
INNER JOIN [Customer] AS [x.Customer0] ON [x].[CustomerID] = [x.Customer0].[Id]

[x.Customer] and [x.Customer0] are the same thing - ideally we could detect this and prune second one (and re-wire all the references to point to the first)

[anpete]

@maumar Can we close this now?

[maumar]

@anpete yes, this is now fixed. SQL that we generate now is as follows:

SELECT [x.Customer].[Name], (
    SELECT SUM([p0].[PaymentAmount])
    FROM [Payments] AS [p0]
    WHERE [x.Customer].[Id] = [p0].[CustomerID]
)
FROM [Contacts] AS [x]
INNER JOIN [Customers] AS [x.Customer] ON [x].[CustomerID] = [x.Customer].[Id]

[maumar]

most likely fixed in 9897064