Multiple many-to-many and multiple one-to-one relationships in the same entities

Question

The first class:

public class Route
{
    [Key]
    public int Id { get; private set; }

    public decimal Price { get; set; }

    public decimal CapitalPrice { get; set; }

    public int CurrencyId { get; set; }

    [DeleteBehavior(DeleteBehavior.NoAction)]
    public Currency Currency { get; set; } = null!;

    public int DeparturePlaceId { get; set; }
    [DeleteBehavior(DeleteBehavior.NoAction)]

    public City DeparturePlace { get; set; } = null!;

    public int ArrivePlaceId { get; set; }
    [DeleteBehavior(DeleteBehavior.NoAction)]

    public City ArrivePlace { get; set; } = null!;

    [DefaultValue(value: false)]
    public bool IsDeleted { get; set; }

    public DateTime CreatedAt { get; set; } = DateTime.Now;

    public DateTime UpdatedAt { get; set; } = DateTime.Now;

    [DeleteBehavior(DeleteBehavior.NoAction)]
    public ICollection<City>? FromCities { get; set; } = null!;

    [DeleteBehavior(DeleteBehavior.NoAction)]
    public ICollection<City>? ToCities { get; set; } = null!;

    [DeleteBehavior(DeleteBehavior.NoAction)]
    public ICollection<Trip>? Trips { get; set; } = null!;

    [DeleteBehavior(DeleteBehavior.NoAction)]
    public ICollection<BookingItem>? BookingItems { get; set; } = null!;

    [DeleteBehavior(DeleteBehavior.Cascade)]
    public ICollection<DepartureTime>? DepartureTimes { get; set; } 
}

And this is the second class:

public class City
{
     [Key]
     public int Id { get;  set; }

     [Column(TypeName = "nvarchar(35)")]
     public string Name { get; set; } = null!;
   
     public byte CountryId { get; set; }

     [DeleteBehavior(DeleteBehavior.NoAction)]
     public Country Country { get; set; } = null!;

     [DeleteBehavior(DeleteBehavior.NoAction)]
     public ICollection<Branch>? Branches { get; set; }

     public ICollection<Route>? Routes { get; set; }
}

I want to make Entity Framework Core understand this complex relationship between Route and City

I tried this:

modelBuilder.Entity<Route>()
  .HasOne(a => a.ArrivePlace)
  .WithMany()
  .HasForeignKey(a => a.ArrivePlaceId);

   modelBuilder.Entity<Route>()
  .HasOne(a => a.DeparturePlace)
  .WithMany()
  .HasForeignKey(a => a.DeparturePlaceId);

    modelBuilder.Entity<Route>()
           .HasMany(a => a.FromCities)
           .WithMany()
           .UsingEntity<RouteFromCity>();

            modelBuilder.Entity<Route>()
           .HasMany(a => a.ToCities)
           .WithMany()
           .UsingEntity<RouteToCity>();

   public class RouteFromCity
   {
       public City City { get; set; }
       public int CityId { get; set; }
       public int RouteId { get; set; }
       public Route Route { get; set; }
   }

   public class RouteToCity
   {
       public int CityId { get; set; }
       public City City { get; set; }
       public int RouteId { get; set; }
       public Route Route { get; set; }
   }

When I try to edit cities in the FromCities or ToCities field, I get an error:

Violation of PRIMARY KEY constraint 'PK_RouteFromCity'. Cannot insert duplicate key in object 'dbo.RouteFromCity'. The duplicate key value is (2, 1013)

Answer 1

To have multiple many-to-many relationships referenced, you either need multiple navigation sets on both sides (if you want bi-directional references) or if you only care about one relationship, it needs to be explicitly set, with the second being told there is no bi-directional relationship.

To explain it in your model. In Route you have:

public ICollection<City>? FromCities { get; set; } = null!;
public ICollection<City>? ToCities { get; set; } = null!;

Yet in City you have only:

public ICollection<Route>? Routes { get; set; }

Which set of Routes in a given City would these routes represent? It cannot contain both the To and From cities. If you wanted inverse navigation properties in a city to link to both cities the route might be part of To and From you would need:

public ICollection<Route>? RoutesIAmToCity { get; set; }
public ICollection<Route>? RoutesIAmFromCity { get; set; }

These navigation properties have to be explicitly mapped. EF can auto-map simple relationships that it can resolve by type, but where you have multiple relationships of the same type then you have to tell it which is which:

For instance in Route Configuration:

    .HasMany(r => r.ToCities)
    .WithMany(c => c.RoutesIAmToCity)
    .UsingEntity<RouteToCity>() // may need mapping for Left/Right joins

    .HasMany(r => r.FromCities)
    .WithMany(c => c.RoutesIAmFromCity)
    .UsingEntity<RouteFromCity>()

Alternatively if City just has "Routes" that we only care about routes where the city is a destination (To) then:

    .HasMany(r => r.ToCities)
    .WithMany(c => c.Routes)
    .UsingEntity<RouteToCity>() // may need mapping for Left/Right joins

    .HasMany(r => r.FromCities)
    .WithMany() // No inverse navigation property collection.

Similar when dealing with multiple many-to-one relationships etc. as well as situations where your navigation property name does not reflect the type. EF's auto-configuration is based on the entity Type rather than the property name. So a common issue when having multiple relationships of the same type, or simply using a different property name is that you need to tell EF explicitly about the relationship. For instance if I have a relationship like:

public City Destination { get; set; }

With a FK called DestinationId or DestinationCityId, EF will expect a FK named "CityId" or "City_Id" based on the Type and not be able to map this automatically.

    .HasOne(r => r.Destination)
    .WithMany()
    .HasForeignKey("DestinationId"); // or (r => r.DestinationId) if you have a FK property.

MS covers off relationship configuration pretty thoroughly on the MS Learn (https://learn.microsoft.com/en-us/ef/core/modeling/relationships/many-to-many) but it can still leave gaps for things like multiple overlapping and potentially conflicting relationships. Mapping by convention works most of the time, but can leave devs abandoned if they're not familiar with implementing explicit mapping.

Edit: Provided the mapping is set up, the next thing to check is how you are updating the Route/City. To help reduce issues you should avoid exposing a public setter on collection navigation properties. They also generally should not be null-able, entities either have relationships or they don't (empty collection) so remove the setter and initialize the set to a default empty set:

public ICollection<City> FromCities { get; } = [];
public ICollection<City> ToCities { get; } = [];

Any code that might try and set these collections should be changed. When loading a Route if you plan to alter the associated cities then eager load those sets:

var route = _context.Routes
    .Include(r => r.FromCities)
    .Include(r => r.ToCities)
    .Single(r => r.RouteId == routeId);

If you want to update the collection you cannot merely replace the collection with a new List, or even call Clear /w Add, instead you should identify which items need to be removed vs. added and remove and add any adjustments explicitly.