{"action":"created","issue":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3816","repository_url":"https://api.github.com/repos/aspnet/EntityFramework","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3816/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3816/comments","events_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3816/events","html_url":"https://github.com/aspnet/EntityFramework/issues/3816","id":117951505,"number":3816,"title":"Query :: we could be smarter about removing redundant joins when expanding multiple navigations","user":{"login":"maumar","id":1591930,"avatar_url":"https://avatars.githubusercontent.com/u/1591930?v=3","gravatar_id":"","url":"https://api.github.com/users/maumar","html_url":"https://github.com/maumar","followers_url":"https://api.github.com/users/maumar/followers","following_url":"https://api.github.com/users/maumar/following{/other_user}","gists_url":"https://api.github.com/users/maumar/gists{/gist_id}","starred_url":"https://api.github.com/users/maumar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maumar/subscriptions","organizations_url":"https://api.github.com/users/maumar/orgs","repos_url":"https://api.github.com/users/maumar/repos","events_url":"https://api.github.com/users/maumar/events{/privacy}","received_events_url":"https://api.github.com/users/maumar/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/aspnet/EntityFramework/labels/type-bug","name":"type-bug","color":"f7c6c7"}],"state":"closed","locked":false,"assignee":{"login":"anpete","id":1210944,"avatar_url":"https://avatars.githubusercontent.com/u/1210944?v=3","gravatar_id":"","url":"https://api.github.com/users/anpete","html_url":"https://github.com/anpete","followers_url":"https://api.github.com/users/anpete/followers","following_url":"https://api.github.com/users/anpete/following{/other_user}","gists_url":"https://api.github.com/users/anpete/gists{/gist_id}","starred_url":"https://api.github.com/users/anpete/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/anpete/subscriptions","organizations_url":"https://api.github.com/users/anpete/orgs","repos_url":"https://api.github.com/users/anpete/repos","events_url":"https://api.github.com/users/anpete/events{/privacy}","received_events_url":"https://api.github.com/users/anpete/received_events","type":"User","site_admin":false},"assignees":[{"login":"anpete","id":1210944,"avatar_url":"https://avatars.githubusercontent.com/u/1210944?v=3","gravatar_id":"","url":"https://api.github.com/users/anpete","html_url":"https://github.com/anpete","followers_url":"https://api.github.com/users/anpete/followers","following_url":"https://api.github.com/users/anpete/following{/other_user}","gists_url":"https://api.github.com/users/anpete/gists{/gist_id}","starred_url":"https://api.github.com/users/anpete/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/anpete/subscriptions","organizations_url":"https://api.github.com/users/anpete/orgs","repos_url":"https://api.github.com/users/anpete/repos","events_url":"https://api.github.com/users/anpete/events{/privacy}","received_events_url":"https://api.github.com/users/anpete/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/19","html_url":"https://github.com/aspnet/EntityFramework/milestones/1.1.0-preview1","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/19/labels","id":1748298,"number":19,"title":"1.1.0-preview1","description":"","creator":{"login":"rowanmiller","id":4741851,"avatar_url":"https://avatars.githubusercontent.com/u/4741851?v=3","gravatar_id":"","url":"https://api.github.com/users/rowanmiller","html_url":"https://github.com/rowanmiller","followers_url":"https://api.github.com/users/rowanmiller/followers","following_url":"https://api.github.com/users/rowanmiller/following{/other_user}","gists_url":"https://api.github.com/users/rowanmiller/gists{/gist_id}","starred_url":"https://api.github.com/users/rowanmiller/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rowanmiller/subscriptions","organizations_url":"https://api.github.com/users/rowanmiller/orgs","repos_url":"https://api.github.com/users/rowanmiller/repos","events_url":"https://api.github.com/users/rowanmiller/events{/privacy}","received_events_url":"https://api.github.com/users/rowanmiller/received_events","type":"User","site_admin":false},"open_issues":108,"closed_issues":210,"state":"open","created_at":"2016-05-04T18:23:47Z","updated_at":"2016-10-03T20:11:29Z","due_on":null,"closed_at":null},"comments":1,"created_at":"2015-11-20T02:17:26Z","updated_at":"2016-10-03T20:11:29Z","closed_at":"2016-10-03T20:11:29Z","body":"Repro:\r\n\r\n```\r\n    public class Contact\r\n    {\r\n        public int Id { get; set; }\r\n        public int CustomerID { get; set; }\r\n        public virtual Customer Customer { get; set; }\r\n    }\r\n\r\n    public class Customer\r\n    {\r\n        public int Id { get; set; }\r\n        public string Name { get; set; }\r\n        public virtual ICollection<Contact> Contacts { get; set; }\r\n        public virtual ICollection<Payment> Payments { get; set; }\r\n    }\r\n\r\n    public class Payment\r\n    {\r\n        public int Id { get; set; }\r\n        public int CustomerID { get; set; }\r\n        public virtual Customer Customer { get; set; }\r\n\r\n        public decimal PaymentAmount { get; set; }\r\n    }\r\n\r\n    public class MyContext : DbContext\r\n    {\r\n        public DbSet<Contact> Contacts { get; set; }\r\n        public DbSet<Customer> Customers { get; set; }\r\n        public DbSet<Payment> Payments { get; set; }\r\n\r\n        protected override void OnModelCreating(ModelBuilder modelBuilder)\r\n        {\r\n            modelBuilder.Entity<Customer>().HasMany(e => e.Contacts).WithOne(e => e.Customer).HasForeignKey(e => e.CustomerID);\r\n            modelBuilder.Entity<Customer>().HasMany(e => e.Payments).WithOne(e => e.Customer).HasForeignKey(e => e.CustomerID);\r\n        }\r\n\r\n        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)\r\n        {\r\n            optionsBuilder.UseSqlServer(new SqlConnectionStringBuilder { DataSource = \".\", InitialCatalog = \"Repro3791\", MultipleActiveResultSets = true, IntegratedSecurity = true }.ToString());\r\n        }\r\n\r\n    }\r\n\r\n    class Program\r\n    {\r\n        static void Main(string[] args)\r\n        {\r\n            using (var ctx = new MyContext())\r\n            {\r\n                ctx.Database.EnsureDeleted();\r\n                ctx.Database.EnsureCreated();\r\n            }\r\n\r\n            using (var ctx = new MyContext())\r\n            {\r\n                var list5 = ctx.Contacts.Select(x => new\r\n                {\r\n                    Customer = x.Customer.Name,\r\n                    PaymentTotal = x.Customer.Payments.Sum(p => p.PaymentAmount)\r\n                }).ToList();\r\n            }\r\n        }\r\n    }\r\n```\r\n\r\nGenerated SQL:\r\n\r\n```\r\nSELECT (\r\n    SELECT SUM([p].[PaymentAmount])\r\n    FROM [Payment] AS [p]\r\n    WHERE [x.Customer0].[Id] = [p].[CustomerID]\r\n), [x.Customer].[Name]\r\nFROM [Contact] AS [x]\r\nINNER JOIN [Customer] AS [x.Customer] ON [x].[CustomerID] = [x.Customer].[Id]\r\nINNER JOIN [Customer] AS [x.Customer0] ON [x].[CustomerID] = [x.Customer0].[Id]\r\n```\r\n\r\n[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)"},"comment":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/comments/251212746","html_url":"https://github.com/aspnet/EntityFramework/issues/3816#issuecomment-251212746","issue_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3816","id":251212746,"user":{"login":"maumar","id":1591930,"avatar_url":"https://avatars.githubusercontent.com/u/1591930?v=3","gravatar_id":"","url":"https://api.github.com/users/maumar","html_url":"https://github.com/maumar","followers_url":"https://api.github.com/users/maumar/followers","following_url":"https://api.github.com/users/maumar/following{/other_user}","gists_url":"https://api.github.com/users/maumar/gists{/gist_id}","starred_url":"https://api.github.com/users/maumar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/maumar/subscriptions","organizations_url":"https://api.github.com/users/maumar/orgs","repos_url":"https://api.github.com/users/maumar/repos","events_url":"https://api.github.com/users/maumar/events{/privacy}","received_events_url":"https://api.github.com/users/maumar/received_events","type":"User","site_admin":false},"created_at":"2016-10-03T20:11:29Z","updated_at":"2016-10-03T20:11:29Z","body":"@anpete yes, this is now fixed. SQL that we generate now is as follows:\r\n\r\n```\r\nSELECT [x.Customer].[Name], (\r\n    SELECT SUM([p0].[PaymentAmount])\r\n    FROM [Payments] AS [p0]\r\n    WHERE [x.Customer].[Id] = [p0].[CustomerID]\r\n)\r\nFROM [Contacts] AS [x]\r\nINNER JOIN [Customers] AS [x.Customer] ON [x].[CustomerID] = [x.Customer].[Id]\r\n```"}}
{"actor":{"display_login":"maumar"}}