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 

What I want to convert to a lambda expression.

Here's how it works.

A game can only be attached to series 1, but the series can, of course, have many games. A series 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 just many-to-many tables created by EF to store links between series / teams and teams / players.

Thanks in advance...

Edit: I am using EF 4 CTP5 and would like to have an answer in the form of lambda functions or in linq if this is easier ...

Answer 1

Well, first of all, if you want to make sure that everything is loading during the execution of the request, 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 will not produce the same results as your SQL query, since you are not filtering out players from the child collection.

In EF 4.1, there are some excellent Use of filters when explicitly loading related objects , but I could not get it to work for sub-sub-collections, so I think that the closest to the original query may be projecting the results onto an anonymous object (or you can create a class for it, if you need to pass this object later):

 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 list and check the results:

 var gamesAndPlayersList = query.ToList(); 

Answer 2

I have found a 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 anyone has a better solution, then I'm all ears.