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

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?

Answers


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.


Need Your Help

UITableViewCell with autolayout left margin different on iPhone and iPad

ios uitableview autolayout margin

I am using a grouped UITableView with static cells for an options screen/scene. Everything is done in Xcode 6.1 / iOS 8.1.x / Storyboard using Autolayout. Within the table groups there are mixed ty...