EF Core Many to many with same class

Question

I have City class representing City with its postal code. I have also a Route class representing bus route between two cities. So, i want in my Route class to have cityFrom and cityTwo properties, both with type City - many to many relationship with same class involved.

How can I achieve this with EF Core code first?

Thx!

UPDATE:

My models looks something like this:

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

    public City CityFrom { get; set; }

    public City CityTo { get; set; }
}  
public class City
{
    public int Id { get; set; }

    public int PostCode { get; set; }

    public string Name { get; set; }
} 

Would this solve my problem:

        modelBuilder.Entity<Route>()
            .HasOne(f => f.CityFrom)
            .WithMany()
            .HasForeignKey(f => f.CityFromId);

        modelBuilder.Entity<Route>()
            .HasOne(f => f.CityTo)
            .WithMany()
            .HasForeignKey(f => f.CityToId);

And also to add int properties CityFromId and CityToId in Route model?

Answer 1

EFCore 3. Needs its own many to many class.

My problem was: Person to Person relations (n->m)

My solution. Create a relationship class that implements the many to many. I chose for the records in the class to have their own ID. So the relationship class has 1 PK and 2 FKs (both to the Person class).

public class Person
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<Relationship> MyRelationships { get; set; }
    public List<Relationship> TheirRelationships { get; set; }
}

//MyRelationships for people I added as a relation. //TheirRelationships for people that added me as a relation.

public class Relationship {
    public int RelationshipID { get; set; }

    public DateTime Since { get; set; }
    //ref to person myrelationships
    public int MyID { get; set; }
    public Person Me { get; set; }

    //ref to person theirrelationships
    public int TheirID { get; set; }
    public Person They { get; set; }
}

Using add-migration and update-database I learned that in this particular case EFCore cannot resolve which FK to use for which relation. I solved that using the fluent API.

public class ApplicationDbContext : IdentityDbContext
{

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        //Write Fluent API configurations here

        modelBuilder.Entity<Person>()
            .HasMany<Relationship>(mr => mr.MyRelationships)
            .WithOne(p => p.Me)
            .HasForeignKey(m => m.MyID)
            .OnDelete(DeleteBehavior.NoAction);

        modelBuilder.Entity<Person>()
            .HasMany<Relationship>(tr => tr.TheirRelationships)
            .WithOne(p => p.They)
            .HasForeignKey(t => t.TheirID)
            .OnDelete(DeleteBehavior.NoAction);

    }
    public DbSet<Person> People { get; set; }
    public DbSet<Relationship> Relationships { get; set; }
}

Now add-migration xxx will work:

    protected override void Up(MigrationBuilder migrationBuilder)
    {

        migrationBuilder.CreateTable(
            name: "People",
            columns: table => new
            {
                ID = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                FirstName = table.Column<string>(nullable: true),
                MiddleName = table.Column<string>(nullable: true),
                LastName = table.Column<string>(nullable: true),
                Email = table.Column<string>(nullable: true),
                UserID = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_People", x => x.ID);
                table.ForeignKey(
                    name: "FK_People_AspNetUsers_UserID",
                    column: x => x.UserID,
                    principalTable: "AspNetUsers",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Restrict);
            });

        migrationBuilder.CreateIndex(
            name: "IX_People_UserID",
            table: "People",
            column: "UserID");

        migrationBuilder.CreateTable(
            name: "Relationships",
            columns: table => new
            {
                RelationshipID = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                Since = table.Column<DateTime>(nullable: false),
                Kind = table.Column<int>(nullable: false),
                MyID = table.Column<int>(nullable: false),
                TheirID = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Relationships", x => x.RelationshipID);
                table.ForeignKey(
                    name: "FK_Relationships_People_MyID",
                    column: x => x.MyID,
                    principalTable: "People",
                    principalColumn: "ID");
                table.ForeignKey(
                    name: "FK_Relationships_People_TheirID",
                    column: x => x.TheirID,
                    principalTable: "People",
                    principalColumn: "ID");
            });

        migrationBuilder.CreateIndex(
            name: "IX_Relationships_MyID",
            table: "Relationships",
            column: "MyID");

        migrationBuilder.CreateIndex(
            name: "IX_Relationships_TheirID",
            table: "Relationships",
            column: "TheirID");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Relationships");

        migrationBuilder.DropTable(
            name: "People");
    }
}

Answer 2

I wouldn't design this is a many-to-many relation.

Every Route has exactly one FromCity, defined by foreignkey FromCityId.
Every Route has exactly one ToCity, defined by foreignkey ToCityId.

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

    // Every Route has one starting City defined by foreign key:
    public int FromCityId { get; set; }
    public virtual City FromCity { get; set; }

    // Every Route has one termination city, defined by foreign key
    public virtual int ToCityId { get; set; }
    public virtual City ToCity { get; set; }

    ...
}

And the DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // Proper name for table of Cities
    ModelBuilder.Entity<City>().ToTable("Cities");

    // Every Route has one required starting point defined by foreign key
    // Several routes may use the same City Id
    modelBuilder.Entity<Route>()
        .HasRequired(route => route.FromCity)
        .WithMany()
        .HasForeignKey(route => route.FromCityId)
        .WillCascadeOnDelete(false);

    // Every Route has one required termination point defined by foreign key
    // Several routes may use the same City Id
    modelBuilder.Entity<Route>()
        .HasRequired(route => route.ToCity)
        .WithMany()
        .HasForeignKey(route => route.ToCityId)
        .WillCascadeOnDelete(false);

    base.OnModelCreating(modelBuilder);

If you delete a Route, then you don't want their cities to be deleted, as they might be used by other Routes: hence we don't want cascading on delete CascadeOnDelete(false)

Answer 3

You can achieve many-to-many relationships in ASP.NET Core using Data Annotations

In your requirements, we can introduce a new class named CityRoute that represent many-to-many between the City and the Route

public class Route
{
    public int RouteId { get; set; } // change to make it more specific id
    public string CityFrom { get; set; }
    public string CityTo { get; set; }
    public ICollection<CityRoute> CityRoutes { get; set; }
}  

public class City
{
    public int CityId { get; set; } // change to make it more specific id
    public int PostCode { get; set; }
    public string Name { get; set; }
    public ICollection<CityRoute> CityRoutes { get; set; }
}

// new class
public class CityRoute 
{
    public City City { get; set; }
    public int CityId { get; set; }
    public Route Route { get; set; }
    public int RouteId { get; set; }
}

Your DataAnnotations can be like this

modelBuilder.Entity<CityRoute>()
        .HasKey(cr => new {cr.CityId, cr.RouteId});

I hope this helps you