EF Core: Creating a link between items in the same table

user_19819535 0
Viewed 67

Suppose I have a list of companies:

Company CompanyID
CompanyA 1
CompanyB 2
CompanyC 3
CompanyD 4

Let's say we want to show that CompanyA is a competitor of CompanyB and CompanyC but not CompanyD.

I would like to use EF Core to establish such a link/mapping. I'm assuming I will need to create a mapping table that in DB will look something like this:

Competitor mapping:

CompanyId CompetitorCompanyID
1 2
1 3

What I am struggling with is how to create such a link in the OnModelCreating(ModelBuilder modelBuilder) method?

I've been able to create maps between entities in 2 different tables, but unable to configure the DBContext class for this use case (where mapped entities live in the same table).

entity-framework-core sql c#
1 Answers

If was me doing I would do it like this

The Company model

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<CompanyCompetitorRelation> CompanyRelation { get; set; }
    public virtual ICollection<CompanyCompetitorRelation> CompetitorRelation { get; set; }
}

In this model I'm saying that it has 2 relations with the CompanyCompetiorRelation model.

Now for the CompanyCompetitorRelation Model

public class CompanyCompetitorRelation
{
    public int CompanyId { get; set; }
    public int CompetitorId { get; set; }
    public virtual Company CompanyRelation { get; set; }
    public virtual Company CompetitorRelation { get; set; }
}

Now here, I'm saying that a CompanyCompetiorRelation has 2 relations with the Company Model.

Now the magic goes on the FluentAPI

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Company>()
        .HasKey(e => e.Id);

    builder.Entity<CompanyCompetitorRelation>()
        .HasKey(e => new { e.CompanyId, e.CompetitorId });

    builder.Entity<CompanyCompetitorRelation>()
        // CompanyCompetitorRelation property
        .HasOne(e => e.CompanyRelation)
        // Company property
        .WithMany(e => e.CompanyRelation) 
        // Set the property that is FK for this relation
        .HasForeignKey(e => e.CompanyId); 

    builder.Entity<CompanyCompetitorRelation>()
        // CompanyCompetitorRelation property
        .HasOne(e => e.CompetitorRelation) 
        // Company property
        .WithMany(e => e.CompetitorRelation) 
        // Set the property that is FK for this relation
        .HasForeignKey(e => e.CompetitorId); 
}

On the first set of FK of the builder.Entity<CompanyCompetitorRelation>() I'm saying that it has one CompanyRelation with many of the Company and is foreignkey is the CompanyId

on the second set of FK of the builder.Entity<CompanyCompetitorRelation>() I'm saying that it as one CompetitorRelation relation with many Company and is foreignkey is the CompetitorId

This is what it has generated

migrationBuilder.CreateTable(
name: "Company",
columns: table => new
{
    Id = table.Column<int>(type: "int", nullable: false)
        .Annotation("SqlServer:Identity", "1, 1"),
    Name = table.Column<string>(type: "nvarchar(max)", nullable: true)
},
constraints: table =>
{
    table.PrimaryKey("PK_Company", x => x.Id);
});

migrationBuilder.CreateTable(
    name: "CompanyCompetitorRelation",
    columns: table => new
    {
        CompanyId = table.Column<int>(type: "int", nullable: false),
        CompetitorId = table.Column<int>(type: "int", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_CompanyCompetitorRelation", x => new { x.CompanyId, x.CompetitorId });
        table.ForeignKey(
            name: "FK_CompanyCompetitorRelation_Company_CompanyId",
            column: x => x.CompanyId,
            principalTable: "Company",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
        table.ForeignKey(
            name: "FK_CompanyCompetitorRelation_Company_CompetitorId",
            column: x => x.CompetitorId,
            principalTable: "Company",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    });

migrationBuilder.CreateIndex(
    name: "IX_CompanyCompetitorRelation_CompetitorId",
    table: "CompanyCompetitorRelation",
    column: "CompetitorId");

Hope that this will help out to understand

You can create as many you want if require, you just have point them in the rigth direction on the FluentAPI

I think it's possible to this on the model also, but I like to use the FluentAPI

user_33100910
Related