Linq query to implement where clause on child entities - EF Core

Question

What I'm trying to do should be really easy, but I just can't work it out!

I have a parent entity called Players and a child entity called PlayerTrainingEvents. (PlayerTrainingEvents is actually a join table, but I'm not involving more than these two tables in this example).

I want to bring back all the Players (not just the ones with related PlayerTrainingEvents) AND their related PlayerTrainingEvents for a given TrainingEventID.

These are the (simplified) entities and their relationship:

Here is some example data:

Using the above example data, if I want to show all the Players with their PlayerTrainingEvents for TrainingEventID 3, I should get the following results:

In SQL, it's really easy:

SELECT *
  FROM Players p
  left join PlayerTrainingEvents pte on p.ID = pte.PlayerID and pte.TrainingEventID = 3

In Linq, the nearest I can get is:

var results = await _context.Players
            .Include(p => p.PlayerTrainingEvents.Where(pte => pte.TrainingEventID == id)).ToListAsync();

...but the above code results in an error: "InvalidOperationException: Lambda expression used inside Include is not valid".

As you can probably tell, I'm still quite new to EF. Having been able to anything I want easily in SQL, I'm struggling with how EF is supposed to make things easier!

Any help would be gratefully appreciated.

Answer 1

Thanks for the answers/comments I received. I have managed to get the below query to work. This queries the Players first and then performs an outer join with the PlayerTrainingEvents.

from p in Players
join pte in PlayerTrainingEvents on new { PlayerId = p.ID, TrainingEventID = 3 } 
                                 equals new { PlayerId = pte.PlayerID, TrainingEventID = pte.TrainingEventID } into ptes
from pte in ptes.DefaultIfEmpty()
select new  { p.ID, pte.TrainingEventID, pte.AttendanceCode }

That just seems a lot of code for what is a very simple SQL query however. I still think SQL is easier but perhaps I just need to do some studying! If anyone has a better solution, I'd be really interested to see it.