Many-to-many on the same Entity (EF Core)

Question

I have a Station entity (in the context of bus stations). This Station entity has a collection of Stations to represent the destinations. How can I map that as a many-to-many relationship in EF Core?

    public class Station {

        [Key]
        public int StationId { get; set; }
        
        [Required]
        [Column(TypeName = "VARCHAR")]
        [StringLength(100)]
        public string TerminalName { get; set; }
        
        [Required]
        [Column(TypeName = "VARCHAR")]
        [StringLength(100)]
        public string City { get; set; }

        [Required]
        [Column(TypeName = "VARCHAR")]
        [StringLength(100)]
        public string Province { get; set; }

        public ICollection<Station> Destinations { get; set; }
    }

I'm having trouble doing it and upon running a migration for this, it produces this error:

The navigation 'Station.Destinations' cannot be used for both sides of a many-to-many relationship. Many-to-many relationships must use two distinct navigation properties.

score 0 · Answer 1 · answered May 12 '23 at 14:56

We can't really model undirected graphs in a first-class way. The linking table has to have a FromStation,ToStation, and if you can always go both ways, you need two entries. So try something like

public class Station
{

    [Key]
    public int StationId { get; set; }

    [Required]
    [Column(TypeName = "VARCHAR")]
    [StringLength(100)]
    public string TerminalName { get; set; }

    [Required]
    [Column(TypeName = "VARCHAR")]
    [StringLength(100)]
    public string City { get; set; }

    [Required]
    [Column(TypeName = "VARCHAR")]
    [StringLength(100)]
    public string Province { get; set; }

    public ICollection<Station> IncomingDestinations { get; set; }
    public ICollection<Station> OutgoingDestinations { get; set; }
}

score 0 · Answer 2 · edited May 13 '23 at 02:37

First you need this additional class

public class Mapped_Stations
{
    public int OriginId { get; set; }

    public int DestinationId { get; set; }

    public Station Origin { get; set; }

    public Station Destination { get; set; }
}

Next add two lists in your station class

public class Station
{
    [Key]
    public int StationId { get; set; }

    [Required]
    [Column(TypeName = "VARCHAR")]
    [StringLength(100)]
    public string TerminalName { get; set; }

    [Required]
    [Column(TypeName = "VARCHAR")]
    [StringLength(100)]
    public string City { get; set; }

    [Required]
    [Column(TypeName = "VARCHAR")]
    [StringLength(100)]
    public string Province { get; set; }

    //Add This Lists
    public List<Mapped_Stations> Origins { get; set; }
    public List<Mapped_Stations> Destinations { get; set; }
}

Then create a mapping folder and add class in this folder and use IEntityTypeConfiguration

public class Mapped_SatationsMap : IEntityTypeConfiguration<Mapped_Stations>
{
    public void Configure(EntityTypeBuilder<Mapped_Stations> builder)
    {
        builder.HasKey(t => new { t.OriginId, t.DestinationId });
        builder
            .HasOne(t => t.Origin)
            .WithMany(p => p.Origins)
            .HasForeignKey(f => f.OriginId);
        builder
            .HasOne(t => t.Destination)
            .WithMany(p => p.Destinations)
            .HasForeignKey(f => f.DestinationId).OnDelete(DeleteBehavior.Restrict);
    }
}

Finally configure your Db

public class Db:DbContext
{
    public Db() : base() { }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(@"Server=(local);Database=StackOverFlow;Trusted_Connection=True");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new Mapped_SatationsMap());
    }

    DbSet<Mapped_Stations> Mapped_Stations { get; set; }
    DbSet<Station> stations { get; set; }
}

you can get this project sample in my git hub: [link](https://github.com/Mohammad-Abbasi2559/Many-to-many-on-the-Same-Entity-EF-Core) — mamad2559, May 13 '23 at 03:59

Many-to-many on the same Entity (EF Core)

2 Answers2