Many to many (join table) relationship with the same entity with codefirst or fluent API?

Question

I am developing with EF 4.3.1 CodeFirst. I have an Airport table as shown below:

 public class Airport
    {
        [Key]
        public int ID { get; set; }
        public string Name{ get; set; }
    }

What I need is a Route table with 2 FKs from the same Airport table like:

 public class Route
    {
        public int DepartureAirportID { get; set; }
        public int DestinationAirportID { get; set; }
        public virtual Airport DestinationAirport { get; set; }
        public virtual Airport DepartureAirport { get; set; }
    }

How can this be achieved?

score 4 · Accepted Answer · answered Apr 06 '12 at 20:39

This should do what you need...

public class Airport
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Route> DepartureRoutes { get; set; }
    public virtual ICollection<Route> DestinationRoutes { get; set; }
}
public class Route
{
    public int DepartureAirportID { get; set; }
    public int DestinationAirportID { get; set; }
    public Airport DestinationAirport { get; set; }
    public Airport DepartureAirport { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Route>()
        .HasKey(i => new { i.DepartureAirportID, i.DestinationAirportID});

    modelBuilder.Entity<Route>()
        .HasRequired(i => i.DepartureAirport)
        .WithMany(u => u.DepartureRoutes)
        .HasForeignKey(i => i.DepartureAirportID)
        .WillCascadeOnDelete(false);

    modelBuilder.Entity<Route>()
        .HasRequired(i => i.DestinationAirport)
        .WithMany(u => u.DestinationRoutes)
        .HasForeignKey(i => i.DestinationAirportID)
        .WillCascadeOnDelete(false);
}

...this creates tables like...

CREATE TABLE [Airports] (
    [ID] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](4000),
    CONSTRAINT [PK_Airports] PRIMARY KEY ([ID])
)
CREATE TABLE [Routes] (
    [DepartureAirportID] [int] NOT NULL,
    [DestinationAirportID] [int] NOT NULL,
    CONSTRAINT [PK_Routes] PRIMARY KEY ([DepartureAirportID], [DestinationAirportID])
)
CREATE INDEX [IX_DestinationAirportID] ON [Routes]([DestinationAirportID])
CREATE INDEX [IX_DepartureAirportID] ON [Routes]([DepartureAirportID])
ALTER TABLE [Routes] ADD CONSTRAINT [FK_Routes_Airports_DestinationAirportID] FOREIGN KEY ([DestinationAirportID]) REFERENCES [Airports] ([ID])
ALTER TABLE [Routes] ADD CONSTRAINT [FK_Routes_Airports_DepartureAirportID] FOREIGN KEY ([DepartureAirportID]) REFERENCES [Airports] ([ID])

...and you can use it like so...

using (var db = new MyDbContext())
{
    foreach (var routeid in Enumerable.Range(1, 100))
    {
        var departure = new Airport { Name = "departure" + routeid };
        db.Airports.Add(departure);
        var destination = new Airport { Name = "destination" + routeid };
        db.Airports.Add(destination);

        var route = new Route{ DepartureAirport = departure, DestinationAirport = destination };
        db.Routes.Add(route);
    }

    int recordsAffected = db.SaveChanges();

    foreach (var route in db.Routes)
    {
        Console.WriteLine("{0}, {1}, {2}, {3}", route.DepartureAirportID, route.DestinationAirportID, route.DepartureAirport.Name, route.DestinationAirport.Name);
    }
}

...hope this helps. Note: don't use virtual on required properties (as those are indexes - and for this type of mapping will only work like that, you'll get some error I think).
Also I always add the opposite relations but you can use WithMany() blank, should work too.

Thank you NSGaga! I am new to MVC. I am using EF 4.3.1. Can you suggest me any latest and efficient sources to learn about CodeFirst Fluent API and relationships? — oislek, Apr 06 '12 at 23:38
Give it a 'entity framework walkthrough' on google (for the past year) - and that should give you plenty. Follow [MS ADO.NET forums] (http://blogs.msdn.com/b/adonet/) as most of things are being asked there (though responses are slower:). Btw please 'upvote' too if you find this useful! — NSGaga-mostly-inactive, Apr 06 '12 at 23:46
Nsaga I'd do it even it actually"is" not:) but the answer above and your effort would very much deserve it. But I couldnt coz I got a warning as "Vote Up requires 15 reputation". Sorry mate :( — oislek, Apr 06 '12 at 23:50
ok :) sorry on my side, forgot about that completely. One more link http://msdn.microsoft.com/en-us/data/ef — NSGaga-mostly-inactive, Apr 06 '12 at 23:57
I found that tool but it generates hashsets on the constructor so I believe it doesnt cope with the latest version of EF: [link]http://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d — oislek, Apr 07 '12 at 09:12
NSGaga I have another problem now:( I believe because we have declared the keys with Fluent API on Route table as: `code`modelBuilder.Entity() .HasKey(i => new { i.DepartureAirportID, i.DestinationAirportID});`code` now I cant just run my sample with a 1-1 to relation from my Flight table which has a Route property. It raises me the error: **The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical.** I think EF expects me to supply the destination and the departure airport ID's on my **Flight** table too ? — oislek, Apr 07 '12 at 09:46
I think the best way to go is to create the UNIQUE key we achieved with **modelBuilder.Entity() .HasKey(i => new { i.DepartureAirportID, i.DestinationAirportID});** with another way as such: **context.Database.ExecuteSqlCommand("ALTER TABLE [Route] ADD CONSTRAINT [UX_RouteDeptDest] UNIQUE ([DepartureAirportID], [DestinationAirportID])");** What do you think? — oislek, Apr 07 '12 at 10:34
hard to follow :) over comments. I'd suggest to post another question (and it's good for your score) as that's recommended on SO. So, reference this one as a starting point, so don't repeat that - and add extra data and the problem - then me, others will take a look (you'd also get more exposure, like this it's only me chatting). — NSGaga-mostly-inactive, Apr 07 '12 at 15:08

score 0 · Answer 2 · answered Apr 18 '12 at 08:25

You should add an additional self-related property to your class Airport:

public class Airport
{
    public int ID { get; set; }
    public string Name{ get; set; }
    public virtual ICollection<Airport> AirportRoutes { get; set; }
}

And override OnModelCreating method with the next Fluent API code:

protected override void OnModelCreating(DbModelBuilder modelBuilder) 
{
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Airport>().HasMany(x => x.AirportRoutes).WithMany().Map(
            x => 
               {
                   x.MapLeftKey("DepartureId");       // left key name
                   x.MapRightKey("DestinationId");    // right key name
                   x.ToTable("Routes");               // table name
               });
}

Hope this helps you.

Thanks @Beebik. Are you recommending this as an alternative to the ALTER TABLE solution that I have referred in my comments to NSGaga? — oislek, Apr 19 '12 at 10:30

Many to many (join table) relationship with the same entity with codefirst or fluent API?

2 Answers2

Linked