Query: GroupJoin generates LEFT-JOIN and doesn't return all results

[Hajisharifi]

Steps to reproduce (SQL Server Database)

CREATE TABLE Table_1
(
ID1 int NOT NULL PRIMARY KEY,
ID2 int NULL
);
GO
CREATE TABLE Table_2
(
ID2 int NOT NULL PRIMARY KEY,
MyData int NOT NULL,
MyOtherDate nvarchar(50) NULL
/* I have multi column... */
);
GO
INSERT Table_1 (ID1, ID2) VALUES (11, 21);
INSERT Table_1 (ID1, ID2) VALUES (12, 21);
INSERT Table_1 (ID1, ID2) VALUES (13, 22);
INSERT Table_2 (ID2, MyData, MyOtherDate) VALUES (21, 11111, NULL);
INSERT Table_2 (ID2, MyData, MyOtherDate) VALUES (22, 22222, NULL);
GO

Steps to reproduce (C# Code1)

[Table("Table_1")]
public class Table_1
{
[Key] public int ID1 { get; set; }
public int? ID2 { get; set; }
}
[Table("Table_2")]
public class Table_2
{
[Key] public int ID2 { get; set; }
public int? MyData { get; set; }
public string MyOtherDate { get; set; }
}

//EFCore1-Return only two rows(?!!!)
//SELECT b.[ID2], b.[MyData], b.[MyOtherDate], a.[ID1]
//FROM [Table_1] AS a LEFT JOIN [Table_2] AS b ON a.[ID2] = b.[ID2] ORDER BY a.[ID2]
var x = db.Table_1
.GroupJoin(db.Table_2,
a => a.ID2,
b => b.ID2, (a, b) => new
{
a.ID1,
MyData = b.Select(c => c.MyData).FirstOrDefault()
})
.ToList();

Steps to reproduce (Alternative Code2)

//EFCore1-ERROR
//System.Data.SqlClient.SqlException
//The column prefix 't20' does not match with a table name or alias name used in the query.
//No column name was specified for column 1 of 't3'.
//Invalid column name 'ID2'.
//Invalid column name 'MyData'.
var y = db.Table_1
.Join(db.Table_2.DefaultIfEmpty(),
a => a.ID2,
b => b.ID2, (a, b) => new
{
a.ID1,
b.MyData
})
.ToList();

Steps to reproduce (Detect another Error)

//EFCore1-ERROR
//System.InvalidOperationException
//Operation is not valid due to the current state of the object.
var z = db.Table_1
.Join(db.Table_2.Select(r => new { r.ID2, r.MyData }),
a => a.ID2,
b => b.ID2, (a, b) => new
{
a.ID1,
b.MyData
})
.ToList();

The issue

GroupJoin in Code1 don't return all rows
Row[0]=11,1111
Row[1]=13,2222
Row[2]=12,1111 (don't return)

auto TSQL generate by EFCore1 (return three row):

Join+DefaultIfEmpty in Code2 throw an exception.
Exception type: System.Data.SqlClient.SqlException
Exception message:
The column prefix 't20' does not match with a table name or alias name used in the query.
No column name was specified for column 1 of 't3'.
Invalid column name 'ID2'.
Invalid column name 'MyData'.

Further technical details

"Microsoft.EntityFrameworkCore": "1.0.0"

Sorry for my bad English.
Thanks.

[smitpatel]

Same root cause as #6318
The projection includes columns from second join table before first hence the logic of outer key changed failed.

[smitpatel]

Issue here is:
For 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:

var query = context.Orders
    .GroupJoin(context.Customers,
        o => o.CustomerID,
        c => c.CustomerID,
        (o, cs) => new
        {
            o.OrderID,
            Name = cs.Select(c => c.ContactName).FirstOrDefault()
        }).ToList();

In 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.
Therefore in this issue, we see 2 groups instead of 3.
In #6318 & #6232 , it causes to return same outer element multiple times, even though the element is changed.

[Eilon]

Approved for 1.0.1.

[smitpatel]

fixed via #6387