EF Core joining on the wrong field when using GroupBy

[olivierr91]

Here is probably a big bug introduced in EF Core 2.0. The database is PostgreSQL.

I have this code, which returns a result in EF Core 1.2, and gives no results in EF Core 2.0:

IEnumerable<IGrouping<Sport, Team>> groups = dbContext.Teams
                .Include(t => t.Players)
                .Where(t => t.ID == 3)
                .GroupBy(t => t.Sport)
                .ToList();

I have investigated the produced SQL query. The first SQL query that is produced to join Team on Sport is correct:

SELECT "t"."id", "t"."name", "t"."sport_id"
	FROM "team" AS "t"
	INNER JOIN "sport" AS "t.Sport" ON "t"."sport_id" = "t.Sport"."id"
	WHERE "t"."id" = 3
	ORDER BY "t.Sport"."id", "t"."id"

The second query produced to join Team on Players has an incorrect join:

SELECT "t.Players"."id", "t.Players"."team_id"
	FROM "team" AS "t.Players"
	INNER JOIN (
	    SELECT DISTINCT "t.Sport0"."id", "t0"."id" AS "id0"
	    FROM "team" AS "t0"
	    INNER JOIN "sport" AS "t.Sport0" ON "t0"."sport_id" = "t.Sport0"."id"
	    WHERE "t0"."id" = 3
	) AS "t1" ON "t.Players"."team_id" = "t1"."id"
	ORDER BY "t0"."id"

EF Core makes its outermost join on Player and Sport (t1.id), which does not make any sense and obviously gives no result. Instead, it should join on Player and Team (id0). Running the query manually with the fixed SQL returns the expected record.

Also removing the GroupBy() clause fixes the problem. The following query produces the expected SQL query and returns the expected results:

IEnumerable<Team> groups = dbContext.Teams
                .Include(t => t.Players)
                .Include(t => t.Sport)
                .Where(t => t.ID == 3)
                .ToList();

I can then GroupBy() just fine using LINQ-to-objects,

[ajcvickers]

Hi @orobert91. We are gathering information on the use of EF Core pre-release builds. You reported this issue shortly after the release of 2.0.0 RTM. It would be really helpful if you could let us know:

• Did you consider testing your code against the pre-release builds?
• Was there anything blocking you from using pre-release builds?
• What do you think could make it easier for you to use pre-release builds in the future?

Thanks in advance for any feedback. Hopefully this will help us to increase the value of pre-release builds going forward.

[olivierr91]

Hi @ajcvickers

• I did not consider, but I will consider. I understand that testing our code against pre-release builds is helpful to you guys and prevent such issues from appearing in the final release. Since we have made .NET Core and EF Core our primary development technology, it would make sense to do it.
• No.
• Getting the pre-release builds through NuGet couldn't be easier, really. Maybe if there was a way to get notified when a milestone is reached and you would like us to test the code, that would be great

[ajcvickers]

@orobert91 Thanks for the feedback; much appreciated!

[smitpatel]

Blocked on #10974

[smitpatel]

Assigning to @maumar to verify this is fixed while fixing #10974
If there is still invalid sql, feel free to pass me back.

[maumar]

fixed in fdb8ece