{"action":"created","issue":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6360","repository_url":"https://api.github.com/repos/aspnet/EntityFramework","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6360/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6360/comments","events_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6360/events","html_url":"https://github.com/aspnet/EntityFramework/issues/6360","id":172018036,"number":6360,"title":"The GroupJoin generate LEFT-JOIN and don't return all result !","user":{"login":"Hajisharifi","id":21112489,"avatar_url":"https://avatars.githubusercontent.com/u/21112489?v=3","gravatar_id":"","url":"https://api.github.com/users/Hajisharifi","html_url":"https://github.com/Hajisharifi","followers_url":"https://api.github.com/users/Hajisharifi/followers","following_url":"https://api.github.com/users/Hajisharifi/following{/other_user}","gists_url":"https://api.github.com/users/Hajisharifi/gists{/gist_id}","starred_url":"https://api.github.com/users/Hajisharifi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Hajisharifi/subscriptions","organizations_url":"https://api.github.com/users/Hajisharifi/orgs","repos_url":"https://api.github.com/users/Hajisharifi/repos","events_url":"https://api.github.com/users/Hajisharifi/events{/privacy}","received_events_url":"https://api.github.com/users/Hajisharifi/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":"open","locked":false,"assignee":{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false},"assignees":[{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/20","html_url":"https://github.com/aspnet/EntityFramework/milestones/1.0.1","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/20/labels","id":1829807,"number":20,"title":"1.0.1","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":1,"closed_issues":23,"state":"open","created_at":"2016-06-15T18:13:10Z","updated_at":"2016-08-19T18:35:45Z","due_on":null,"closed_at":null},"comments":2,"created_at":"2016-08-18T22:43:31Z","updated_at":"2016-08-19T23:23:36Z","closed_at":null,"body":"### Steps to reproduce (SQL Server Database)\r\nCREATE TABLE Table_1\r\n(\r\n\tID1 int NOT NULL PRIMARY KEY,\r\n\tID2 int NULL\r\n);\r\nGO\r\nCREATE TABLE Table_2\r\n(\r\n\tID2 int NOT NULL PRIMARY KEY,\r\n\tMyData int NOT NULL,\r\n\tMyOtherDate nvarchar(50) NULL\r\n\t/* I have multi column... */\r\n);\r\nGO\r\nINSERT Table_1 (ID1, ID2) VALUES (11, 21);\r\nINSERT Table_1 (ID1, ID2) VALUES (12, 21);\r\nINSERT Table_1 (ID1, ID2) VALUES (13, 22);\r\nINSERT Table_2 (ID2, MyData, MyOtherDate) VALUES (21, 11111, NULL);\r\nINSERT Table_2 (ID2, MyData, MyOtherDate) VALUES (22, 22222, NULL);\r\nGO\r\n\r\n### Steps to reproduce (C# Code1)\r\n[Table(\"Table_1\")]\r\npublic class Table_1\r\n{\r\n    [Key] public int ID1 { get; set; }\r\n    public int? ID2 { get; set; }\r\n}\r\n[Table(\"Table_2\")]\r\npublic class Table_2\r\n{\r\n    [Key] public int ID2 { get; set; }\r\n    public int? MyData { get; set; }\r\n    public string MyOtherDate { get; set; }\r\n}\r\n\r\n//EFCore1-Return only two rows(?!!!)\r\n//SELECT b.[ID2], b.[MyData], b.[MyOtherDate], a.[ID1]\r\n//FROM [Table_1] AS a LEFT JOIN [Table_2] AS b ON a.[ID2] = b.[ID2] ORDER BY a.[ID2]\r\nvar x = db.Table_1\r\n.GroupJoin(db.Table_2,\r\na => a.ID2,\r\nb => b.ID2, (a, b) => new\r\n{\r\n    a.ID1,\r\n    MyData = b.Select(c => c.MyData).FirstOrDefault()\r\n})\r\n.ToList();\r\n\r\n### Steps to reproduce (Alternative Code2)\r\n//EFCore1-ERROR\r\n//System.Data.SqlClient.SqlException\r\n//The column prefix 't20' does not match with a table name or alias name used in the query.\r\n//No column name was specified for column 1 of 't3'.\r\n//Invalid column name 'ID2'.\r\n//Invalid column name 'MyData'.\r\nvar y = db.Table_1\r\n.Join(db.Table_2.DefaultIfEmpty(),\r\na => a.ID2,\r\nb => b.ID2, (a, b) => new\r\n{\r\n    a.ID1,\r\n    b.MyData\r\n})\r\n.ToList();\r\n\r\n\r\n### Steps to reproduce (Detect another Error)\r\n//EFCore1-ERROR\r\n//System.InvalidOperationException\r\n//Operation is not valid due to the current state of the object.\r\nvar z = db.Table_1\r\n.Join(db.Table_2.Select(r => new { r.ID2, r.MyData }),\r\na => a.ID2,\r\nb => b.ID2, (a, b) => new\r\n{\r\n    a.ID1,\r\n    b.MyData\r\n})\r\n.ToList();\r\n\r\n\r\n### The issue\r\nGroupJoin in Code1 don't return all rows\r\nRow[0]=11,1111\r\nRow[1]=13,2222\r\n_Row[2]=12,1111 (don't return)_\r\n\r\nauto TSQL generate by EFCore1 (return three row):\r\n![ssms-result](https://cloud.githubusercontent.com/assets/21112489/17792612/150b0bc0-65b7-11e6-81a8-348a6455a009.PNG)\r\n\r\nJoin+DefaultIfEmpty in Code2 throw an exception.\r\nException type: System.Data.SqlClient.SqlException\r\nException message:\r\nThe column prefix 't20' does not match with a table name or alias name used in the query.\r\nNo column name was specified for column 1 of 't3'.\r\nInvalid column name 'ID2'.\r\nInvalid column name 'MyData'.\r\n\r\n### Further technical details\r\n\r\n\"Microsoft.EntityFrameworkCore\": \"1.0.0\"\r\n\r\nSorry for my bad English.\r\nThanks."},"comment":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/comments/241158930","html_url":"https://github.com/aspnet/EntityFramework/issues/6360#issuecomment-241158930","issue_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/6360","id":241158930,"user":{"login":"smitpatel","id":1528107,"avatar_url":"https://avatars.githubusercontent.com/u/1528107?v=3","gravatar_id":"","url":"https://api.github.com/users/smitpatel","html_url":"https://github.com/smitpatel","followers_url":"https://api.github.com/users/smitpatel/followers","following_url":"https://api.github.com/users/smitpatel/following{/other_user}","gists_url":"https://api.github.com/users/smitpatel/gists{/gist_id}","starred_url":"https://api.github.com/users/smitpatel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/smitpatel/subscriptions","organizations_url":"https://api.github.com/users/smitpatel/orgs","repos_url":"https://api.github.com/users/smitpatel/repos","events_url":"https://api.github.com/users/smitpatel/events{/privacy}","received_events_url":"https://api.github.com/users/smitpatel/received_events","type":"User","site_admin":false},"created_at":"2016-08-19T23:23:36Z","updated_at":"2016-08-19T23:23:36Z","body":"Issue here is:\r\nFor the case of streaming group join, we query data sorted by outer key selector. If outer element has projection from something other than outer key selector then we check for change in outer element while inner key remains the same. For the rest of cases, we assume that change in outer element will be recorded by change in outer key selector. Though that optimization fails for following query:\r\n```c#\r\nvar query = context.Orders\r\n    .GroupJoin(context.Customers,\r\n        o => o.CustomerID,\r\n        c => c.CustomerID,\r\n        (o, cs) => new\r\n        {\r\n            o.OrderID,\r\n            Name = cs.Select(c => c.ContactName).FirstOrDefault()\r\n        }).ToList();\r\n```\r\nIn above query, customer has one-to-many fk to orders so multiple orders can have same customer. While doing group join with orders being outer, change in outer element (different orders) can have same key which due to our optimization we assume to be same element. Therefore number of groups generated are not same as number of unique outer elements, rather it is equal to number of unique keys.\r\nTherefore in this issue, we see 2 groups instead of 3.\r\nIn #6318 & #6232 , it causes to return same outer element multiple times, even though the element is changed.\r\n"}}
{"actor":{"display_login":"smitpatel"}}