Entity Framework 4 - pivot tables and navigation properties

Question

I'm just starting to learn Entity Framework 4, and am a bit confused about how pivot tables enter the mix. Case in point: I'm migrating a video game review site from PHP 5/Kohana framework to ASP.NET MVC 2. I have a few pivot tables to map the many-to-many relationships I have. Example:

Video games can be available for several platforms (the Grand Theft Auto series, for instance, is available on XBox 360, Playstation 3, PC, PSP, and even Nintendo DS). And, naturally, each platform has a library of games. So, I have a table named GamesPlatforms that acts as a pivot between, well, games and platforms:

GamePlatforms

• GamesPlatformsID -- int, primary key, identity
• GameID -- int, foreign key from the Games table
• PlatofrmID -- int, foreign key from the Platforms table

I'm just having a hard time seeing how this would be translated into EF4 navigation properties, and, in turn, how I could write LINQ queries rather than traditional JOINs. Is it as simple as something like:

using(var context = MyEntities();)
{
    var gamePlatformCount = (from gpc in context.Games
                            where gpc.GamesPlatforms.Platforms.Name == "XBox 360"
                            select gpc).Count();
}

??

Basically, I just want to know if I'm on the right track, as none of the tutorials I've seen deal with many-to-many relationships.

Answer 1

Almost. You want something like:

using(var context = new MyEntities()
{
    var gamePlatformCount = (from gpc in context.Games
                            where gpc.GamesPlatforms.Any(p => p.Platforms.Name == "XBox 360")
                            select gpc).Count();
}

Answer 2

If you explicitly model the link between Game and Platform as an Entity, you can define your query like this:

        var q = from g in context.GameSet
                from gp in g.GamePlatforms
                where gp.Platform.Name == "Xbox 360"
        var count = q.Count()

However, you don't need the many-to-many link table as an explicit part of your object model. You can directly model a many-to-many relation in your (object) model, backed by the link table in your database.

So in your Entity model, you would simply have Game and Platform, with a many-to-many relationship between them. The query would then look something like:

        var q = from g in context.GameSet
                from p in g.Platforms
                where p.Name == "Xbox 360"

        var count = q.Count();