Convert this SQL to lambda for EF 4 Code first

Question

I have this Sql statement

SELECT * FROM Game 
        INNER JOIN Series ON Series.Id = Game.SeriesId 
        INNER JOIN SeriesTeams ON SeriesTeams.SeriesId = Series.Id 
        INNER JOIN Team ON Team.Id = SeriesTeams.TeamId 
        INNER JOIN TeamPlayers ON TeamPlayers.TeamId = Team.Id 
        INNER JOIN Player ON Player.Id = TeamPlayers.PlayerId 
    WHERE AND Game.StartTime >= GETDATE() 
        AND Player.Id = 1

That I want to be converted into a lambda expression.

This is how it works.

A game can only be joined to 1 series, but a serie can of course have many games. A serie can have many teams and a team can join many series. A player can play in many teams and a team has many players.

SeriesTeams and TeamPlayers are only the many-to-many tables created by EF to hold the references between series/teams and Teams/Players

Thanks in advance...

Edit: I use the EF 4 CTP5 and would like to have the answer as lambda functions, or in linq if that is easier...

Answer 1

Ok, first of all, if you want to make sure that everything is eager-loaded when you run your query, you should add an explicit Include:

context.
Games.
Include(g => g.Series.Teams.Select(t => t.Players)).
Where(g => 
         g.StartTime >= DateTime.Now && 
         g.Series.Teams.Any(t => t.Players.Any(p => p.Id == 1))).
ToList();

However, as I mentioned in my comment, this won't produce the same results as your SQL query, since you don't filter out the players from the child collection.

EF 4.1 has some nifty Applying filters when explicitly loading related entities features, but I couldn't get it to work for sub-sub-collections, so I think the closest you can get to your original query would be by projecting the results onto an anonymous object (or you can create a class for that if you need to pass this object around later on):

var query = context.
            Games.
            Where(g =>
                     g.StartTime >= DateTime.Now && 
                     g.Series.Teams.Any(t => t.Players.Any(p => p.Id == 1))).
            Select(g => new
                        {
                            Game = g,
                            Players = g.
                                      Series.
                                      Teams.
                                      SelectMany(t => t.
                                                      Players.
                                                      Where(p => p.Id == user.Id))
                        });

Then you can enumerate and inspect the results:

var gamesAndPlayersList = query.ToList();

Answer 2

I did found the solution.

IList<Domain.Model.Games> commingGames = this.Games
 .Where(a => a.StartTime >= DateTime.Now && a.Series.Teams.Any(t => t.Players.Any(p => p.Id == user.Id))).ToList();

If somebody has a better solution then I am all ears..