Outer join with linq query in EF Core 3 not working

Question

I am developing a football-themed ASP.Net Core MVC web application. I'm trying to get my head around EF Core as everything I've done previously has been straight ADO.Net.

In my database, I have a table for Players, a table for MatchEvents, and a join table to create a many-to-many relationship, which is called PlayerMatchEvents.

I am trying to fetch ALL the Players together with their role in a MatchEvent for a particular MatchEvent, including the Players that have no involvement in that match. In other words, an Outer Join.

All my research leads to the same solution of using a group join into a temp variable and using DefaultIfEmpty(), but I just can't get it to work!

The code I am working with right now is:

from p in Players
join pme in PlayerMatchEvents
on p.ID equals pme.PlayerID
into temp
from t in temp.DefaultIfEmpty() where t.MatchEventID ==2
select new
{
    PlayerFirstName = p.FirstName,
    PlayerLastName = p.LastName,
    Attendance = t == null ? null : t.PlayerMatchAttendanceDetail.Description

}

The above code gives the same results as an inner join - i.e. only the Players who are already linked to MatchEvent 2 are returned. This is because it is creating the following SQL with a WHERE clause that filters out rows I want to keep...

SELECT [p].[FirstName] AS [PlayerFirstName], [p].[LastName] AS [PlayerLastName], [p1].[Description] AS [Attendance]
FROM [Players] AS [p]
LEFT JOIN [PlayerMatchEvents] AS [p0] ON [p].[ID] = [p0].[PlayerID]
LEFT JOIN [PlayerMatchAttendanceDetail] AS [p1] ON [p0].[PlayerMatchAttendanceDetailID] = [p1].[PlayerMatchAttendanceDetailID]
WHERE [p0].[MatchEventID] = 2
GO

What I am trying to achieve is the EF equivalent of this:

SELECT [p].[FirstName] AS [PlayerFirstName], [p].[LastName] AS [PlayerLastName], [p1].[Description] AS [Attendance]
FROM [Players] AS [p]
LEFT JOIN [PlayerMatchEvents] AS [p0] ON [p].[ID] = [p0].[PlayerID] AND [p0].[MatchEventID] = 2
LEFT JOIN [PlayerMatchAttendanceDetail] AS [p1] ON [p0].[PlayerMatchAttendanceDetailID] = [p1].[PlayerMatchAttendanceDetailID]
GO

Putting the MatchEventID criteria on the join instead of in a WHERE clause brings back ALL players, including those not associated with the MatchEvent, which is what I want. I can do it in SQL but I just can't work out how to do it in EF Core with Linq queries.

Update:

Thanks to Ivan Stoev, the corrected (and tested) version is:

from p in Players
join pme in PlayerMatchEvents
on new { PlayerId = p.ID, MatchEventID = 2 } equals new { PlayerId = pme.PlayerID, MatchEventID = pme.MatchEventID }
into temp
from t in temp.DefaultIfEmpty() 
select new
{
    PlayerFirstName = p.FirstName,
    PlayerLastName = p.LastName,
    Attendance = t == null ? null : t.PlayerMatchAttendanceDetail.Description
}

user_5202563 · Answer

The standard LINQ way of putting the MatchEventID criteria on the join is to Join by using composite keys, e.g.

on new { PlayerID = p.ID, MatchEventID = 2 } equals new { pme.PlayerID, pme.MatchEventID }

In EF Core we usually use navigation properties, and also we can push the outer predicate into right side subquery before performing the join, but the above is the closest LINQ manual join equivalent of the SQL join.

Outer join with linq query in EF Core 3 not working

1 Answers