Entity Framework Core 3.0 - Creating a self-referencing many to many relationship

c# entity-framework-core many-to-many self-referencing-table

Question

So here is my issue...what I am trying to create is a self-referencing many to many relationship. Basically here is my model.

public class InformationSystem
{
  public InformationSystem()
  {
     Systems = new HashSet<InformationSystem>();
     ParentSystems = new HashSet<InformationSystem>();
  }
 [Key()]
 public int InformationSystemID { get; set; }
 public string InformationSystemName { get; set; }
 //Navigation properties
 public virtual ICollection<InformationSystem> Systems { get; set; }
 public virtual ICollection<InformationSystem> ParentSystems { get; set; }
}

The idea being that a system can have many parents and a parent can have many children. I know how to do a self-referencing entity where many children can have one parent. What is tripping me up is the many to many part. Below is my DbContext.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<InformationSystem>(entity =>
  {
    entity
       .HasMany(e => e.ParentSystems)
       .WithMany(e => e.Systems)
       .OnDelete(DeleteBehavior.Restrict);
  });

However on my DbContext, I get an error that .WithMany does not contain a definition for with many that would accept an input of type collection. I know that basically what needs to be built is a link table when the code first creates a migration and updates the database. The link table I think would have two columns and no key. One column would be InformationSystemID and one would be ParentInformationSystemID. Both would be foreign keys. I also know that for this to work right, the delete behavior should be restrict so that if an entry is deleted or updated in the link table, that change won't cascade (and create a loop). Can someone please point me in the right direction of what I need to do to get EF Core 3 to do this correctly? If I had to create a link table myself, how would I go about doing that? And what would I need to do in my DbContext? I know that a link table would look something like this:
I'd greatly appreciate it.

public class InfoSysToParentInfoSys
{
  public int InfoSysID;
  public virtual InformationSystem InfoSys;

  public int ParentInfoSysID;
  public virtual InformationSystem ParentInfoSys;
}
1
0
4/10/2020 6:40:38 AM

Accepted Answer

In EF Core it is necessary to include an entity in the model to represent the join table in the M:N relation, and then add navigation properties to either side of the many-to-many relations that point to the join entity.

The new tableS:

public class InformationSystem
{
    public InformationSystem()
    {
    }

    [Key()]
    public virtual int InformationSystemID { get; set; }
    public virtual string InformationSystemName { get; set; }

    public virtual ICollection<InformationSystemRelation> Systems { get; set; }
    public virtual ICollection<InformationSystemRelation> ParentSystems { get; set; }

}


public class InformationSystemRelation
{
    public int ParentId { get; set; }
    public InformationSystem Parent { get; set; }

    public int ChildId { get; set; }
    public InformationSystem Child { get; set; }
}

The mapping:

modelBuilder.Entity<InformationSystemRelation>()
    .HasKey(x => new { x.ParentId, x.ChildId });

modelBuilder.Entity<InformationSystemRelation>()
    .HasOne(x => x.Parent)
    .WithMany(x => x.Systems)
    .HasForeignKey(x => x.ParentId)                    
    .OnDelete(DeleteBehavior.Restrict);


modelBuilder.Entity<InformationSystemRelation>()
    .HasOne(x => x.Child)
    .WithMany(x => x.ParentSystems)
    .HasForeignKey(x => x.ChildId)
    .OnDelete(DeleteBehavior.Restrict);

The whole sample:

class Program
{
    static void Main(string[] args)
    {
        var db = new MyDbContext();

        var is1 = new InformationSystem() { InformationSystemName = "is1" };
        var is2 = new InformationSystem() { InformationSystemName = "is2" };
        var is3 = new InformationSystem() { InformationSystemName = "is3" };
        var is4 = new InformationSystem() { InformationSystemName = "is4" };

        db.InformationSystems.Add(is1);
        db.InformationSystems.Add(is2);
        db.InformationSystems.Add(is3);
        db.InformationSystems.Add(is4);

        db.SaveChanges();

        var r1 = new InformationSystemRelation() { ParentId = 1, ChildId = 2 };
        var r2 = new InformationSystemRelation() { ParentId = 1, ChildId = 3 };
        var r3 = new InformationSystemRelation() { ParentId = 4, ChildId = 2 };
        var r4 = new InformationSystemRelation() { ParentId = 2, ChildId = 3 };
        var r5 = new InformationSystemRelation() { ParentId = 2, ChildId = 4 };

        db.InformationSystemRelations.Add(r1);
        db.InformationSystemRelations.Add(r2);
        db.InformationSystemRelations.Add(r3);
        db.InformationSystemRelations.Add(r4);
        db.InformationSystemRelations.Add(r5);

        db.SaveChanges();

        var o2 = db.InformationSystems.Include(x => x.Systems).Include(x => x.ParentSystems).Single(x => x.InformationSystemID == 2);
    }

}

public class InformationSystem
{
    public InformationSystem()
    {
    }

    [Key()]
    public virtual int InformationSystemID { get; set; }
    public virtual string InformationSystemName { get; set; }

    public virtual ICollection<InformationSystemRelation> Systems { get; set; }
    public virtual ICollection<InformationSystemRelation> ParentSystems { get; set; }

}

public class MyDbContext : DbContext
{

    public DbSet<InformationSystem> InformationSystems { get; set; }
    public DbSet<InformationSystemRelation> InformationSystemRelations { get; set; }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<InformationSystem>(entity =>
        {
            modelBuilder.Entity<InformationSystemRelation>()
                    .HasKey(x => new { x.ParentId, x.ChildId });

            modelBuilder.Entity<InformationSystemRelation>()
                .HasOne(x => x.Parent)
                .WithMany(x => x.Systems)
                .HasForeignKey(x => x.ParentId)
                .OnDelete(DeleteBehavior.Restrict);

            modelBuilder.Entity<InformationSystemRelation>()
                .HasOne(x => x.Child)
                .WithMany(x => x.ParentSystems)
                .HasForeignKey(x => x.ChildId)
                .OnDelete(DeleteBehavior.Restrict);
        });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("data source=(local)\\SQLEXPRESS;Initial catalog=Test;Integrated security=SSPI");
        base.OnConfiguring(optionsBuilder);
    }
}

public class InformationSystemRelation
{
    public int ParentId { get; set; }
    public InformationSystem Parent { get; set; }

    public int ChildId { get; set; }
    public InformationSystem Child { get; set; }
}
0
4/10/2020 8:40:33 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow