与多个（连接表）关系与同一个实体与codefirst或流畅的API？ [英] Many to many (join table) relationship with the same entity with codefirst or fluent API?

问题描述

我正在用EF 4.3.1 CodeFirst开发。我有一个机场表，如下所示：

 公共课机场
 {
 [Key] 
 public int ID {get;组; } 
 public string Name {get;组; 
} 
  

我需要的是一张路线表，其中有2个FK来自同一个机场表如：

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

如何实现？

解决方案

这应该是你需要的...

  public class机场
 {
 public int ID {get;组; } 
 public string Name {get;组; } 
 public virtual ICollection< Route> DepartureRoutes {get;组; } 
 public virtual ICollection< Route> DestinationRoutes {get;组; } 
} 
 public class Route 
 {
 public int DepartureAirportID {get;组; } 
 public int DestinationAirportID {get;组; } 
 public Airport DestinationAirport {get;组; } 
 public Airport DepartureAirport {get;组; 
 
 
 protected override void OnModelCreating（DbModelBuilder modelBuilder）
 {
 modelBuilder.Entity< Route>（）
 .HasKey（i =>新的{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）; 
} 
  

...这样创建像...的表...

  CREATE TABLE [机场]（
 [ID] [int] NOT NULL IDENTITY，
 [名称] [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]）参考[机场]（[ID]）
 ALTER TABLE [Routes] ADD CONSTRAINT [FK_Routes_Airports_DepartureAirportID] FOREIGN KEY（[DepartureAirportID]）参考[机场]（[ID]）
  

...，你可以这样使用...

  using（var db = new MyDbContext（））
 {
 foreach（var routeid in Enumerable.Range ，100））
 {
 var departure = new Airport {Name =departure+ routeid}; 
 db.Airports.Add（出发）; 
 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）; 
} 
} 
  

...希望这有帮助。
注意：不要在必需的属性上使用 virtual （因为这些是索引 - 对于这种类型的映射只能这样工作，你会得到一些错误我想）。

另外我总是添加相反的关系，但你可以使用WithMany（）空白，应该也工作。

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?

解决方案

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.

这篇关于与多个（连接表）关系与同一个实体与codefirst或流畅的API？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！