Get all Shots (table) related to a SessionId (table) where a third table named Results connects them through Result Id. using Linq

Question

I want to use the sessionId to get all the Shots related to that specific Session. In the database the Shots table has a foreign key to a table named Results. Results has a foreign key to the table Session and it's primary key is the sessionId. The code below returns 0 and I have tried multiple Include etc but nothing works.

Anyone know how to solve this and return a list of Shots related to a specific Session?

public List<Shots> GetShotListFromSession(string sessionId)
        {
            List<Shots> shotslist = new List<Shots>();
            shotslist = _db.Shots.Where(x => x.Results.Session.Id == sessionId).ToList();
            
            return shotslist;
            
        }
    
    ```
[Picture of the relations in database[1]


  [1]: https://i.stack.imgur.com/LfQcW.png

Answer 1

You can first select the desired Results and then use .SelectMany() to retrieve and combine all associated Shots.

List<Shots> shotslist = _db.Results
    .Where(r => r.Session.Id == sessionId)
    .SelectMany(r => r.Shots)
    .ToList();