I have tow tables with one-to-many relationship.

1. Flights Table
2. Destination Table

I have to use AirportCode from a Destination table for two columns in Flights table.

1. DepartureAirportCode - column_1
2. ArrivalAirportCode - c0lumn_2

How to do that by using EF code First?

I have tried with below codes. But That is not correct. I can add foreign key for only one column.

public class Flight
    {
        public int Id { get; set; }

        [Required]
        public string DepartureAirportCode { get; set; }   // sholud be foreign key of Destinations_AirportCode

        [Required]
        public string ArrivalAirportCode { get; set; }   // sholud be foreign key of Destinations_AirportCode

        public Destination Airport { get; set; }
    }

 public class Destinations
    {
        public int Id { get; private set; }
        public string AirportCode { get; private set; }
        public IEnumerable<Flight> Flights { get; set; }
  }

DbContext

builder.Entity<Flight>()
                .HasOne(x => x.Airport)
                .WithMany(x => x.Flights)
                .HasForeignKey(x => x.ArrivalAirportCode)
                .HasPrincipalKey(x => x.AirportCode);
            
            builder.Entity<Flight>()
                .HasOne(x => x.Airport)
                .WithMany(x => x.Flights)
                .HasForeignKey(x => x.DepartureAirportCode)
                .HasPrincipalKey(x => x.AirportCode);

Edit: Added Migration file

I already have did migration and DB update for add ArrivalAirportCode as a foreignKey.

When I try to add a migration for DepartureAirportCode as a foreign key, I got this

 migrationBuilder.DropForeignKey(
                name: "FK_Flight_Destinations_ArrivalAirportCode",
                table: "Flight");                
// here dropped FK_Flight_Destinations_ArrivalAirportCode . But not added later. 


            migrationBuilder.DropForeignKey(   
                name: "FK_Flight_Departures_DepartureAirportCode",
                table: "Flight");             
 // please ignore above line. Because already I used FK_Flight_Departures_DepartureAirportCode - foreign key from a different table named as Departures. Now I want to use FK_Flight_Destinations_DepartureAirportCode and FK_Flight_Destinations_ArrivalAirportCode from Destinations table


migrationBuilder.AddForeignKey(
                name: "FK_Flight_Destinations_DepartureAirportCode",
                table: "Flight",
                column: "DepartureAirportCode",
                principalTable: "Destinations",
                principalColumn: "AirportCode",
                onDelete: ReferentialAction.Cascade);     
// added only FK_Flight_Destinations_DepartureAirportCode. I want two column with  foreign key  FK_Flight_Destinations_DepartureAirportCode and FK_Flight_Destinations_ArrivalAirportCode

CodePudding user response：

Your problem is that you are trying to use the same properties for two different relationships between Flight and Destination. Both the Airport property on Flight and the Flights property on Destination are used for the "Arrival" relationship as well as for the "Destination" relationshio - and this is not possible with EF.

So in your Flight class you are missing a property for the second relationship to Destination as you cannot use the same property for Arrival and Depature (because they are different airports):

public class Flight
{
    public int Id { get; set; }

    [Required]
    public string DepartureAirportCode { get; set; }   // sholud be foreign key of Destinations_AirportCode

    [Required]
    public string ArrivalAirportCode { get; set; }   // sholud be foreign key of Destinations_AirportCode

    public Destination DepartureAirport { get; set; }

    public Destination ArrivalAirport { get; set; }
}

And same for Destination:

public class Destination
{
    public int Id { get; private set; }

    public string AirportCode { get; private set; }

    public IEnumerable<Flight> ArrivalFlights { get; set; }

    public IEnumerable<Flight> DepartureFlights { get; set; }
}

once you add it and change your configuration to:

builder
    .Entity<Flight>()
    .HasOne(x => x.ArrivalAirport)
    .WithMany(x => x.ArrivalFlights)
    .HasForeignKey(x => x.ArrivalAirportCode)
    .HasPrincipalKey(x => x.AirportCode);
            
builder.Entity<Flight>()
    .HasOne(x => x.DepartureAirport)
    .WithMany(x => x.DepartureFlights)
    .HasForeignKey(x => x.DepartureAirportCode)
    .HasPrincipalKey(x => x.AirportCode);

it should work.