How to insert triangle relationship into DB using EF

Question

Hello i have a database with 3 tables designed in a triangle like below:

File  has many Page 
Page  has many Segment
File  has many Segment

When i try to insert the an entity of type File i get the following error:

InnerException {"The MERGE statement conflicted with the FOREIGN KEY constraint \"FK_Segment_File_FileId\". The conflict occurred in database \"mydatabase\", table \"dbo.File\", column 'Id'.\r\nThe statement has been terminated."} System.Exception {Microsoft.Data.SqlClient.SqlException}

Models

public class File
{
  public int Id{get;set;}
  public ICollection<Segment> Segments{get;set;}
  public ICollection<Pages> Pages{get;set;}
}
public class Page
{
  public int Id{get;set;}
  public ICollection<Segment> Segments{get;set;}
}
public class Segment
{
  public int Id{get;set;}

  public Page Page{get;set;}
  public int PageId{get;set;}  //foreign key from page

  public File File{get;set;}
  public int FileId{get;set;} //foreign key from file
}

Context

public class MyContext: DbContext {
        public DbSet<Segment> Segments { get; set; }
        public DbSet<Page> Pages { get; set; }
        public DbSet<File> Files { get; set; }
        public SXSContext(DbContextOptions<MyContext> options):base(options) {

        }
        protected override void OnModelCreating(ModelBuilder modelBuilder) {

            modelBuilder.Entity<File>(ent => {
                ent.ToTable("File");
                ent.HasKey(x => x.Id);
                ent.Property(x => x.Id).IsRequired();
            });
            modelBuilder.Entity<Page>(ent => {
                ent.ToTable("Page");
                ent.HasKey(x => x.Id);

                ent.Property(x => x.Id).IsRequired();
                ent.Property(x => x.FileId).IsRequired();
                ent.HasOne(y => y.File).WithMany(t => t.Pages).HasForeignKey(g => g.FileId).OnDelete(DeleteBehavior.NoAction);
            });
            modelBuilder.Entity<Segment>(ent => {
                ent.ToTable("Segment");
                ent.HasKey(x => x.Id);
                ent.Property(x => x.Id).IsRequired();
                ent.Property(x => x.PageId).IsRequired();
                ent.Property(x => x.FileId).IsRequired();
                ent.HasOne(y => y.Page).WithMany(t => t.Segments).HasForeignKey(g => g.PageId).OnDelete(DeleteBehavior.Cascade);
                ent.HasOne(y => y.File).WithMany(t => t.Segments).HasForeignKey(g => g.FileId).OnDelete(DeleteBehavior.Cascade);
            });
        }
    }

Usage

static async Task Main(string[] args) {
            await Task.Delay(1);

            DbContextOptionsBuilder<MyContext> optionsBuilder = new DbContextOptionsBuilder<MyContext>();
            optionsBuilder.UseSqlServer(connectionString);
            SXSContext context = new SXSContext(optionsBuilder.Options);
            context.Database.EnsureCreated();
            List<Page> pages = new List<Page>{
                new Page{
                Segments = new List<Segment> {
                    new Segment{  },
                    new Segment{  },
                    new Segment{ }}
                },
                new Page{
                Segments = new List<Segment> {
                    new Segment{  },
                    new Segment{  }}
                }
            };
            File file = new File {  Pages = pages };
            context.Files.Add(file);

            context.SaveChanges();
            int val = await context.SaveChangesAsync();

        }

P.S Could it be a problem that i have Segment's foreign keys as required and they might get inserted before their parents File and Page?

How should a schema like this be inserted ?I start with a File and i have Pages and Segments they do not posess id's until insertion.

Can they be inserted in one operation or they must be inserted starting from the root which in my case is File ?

Irakli · Accepted Answer · 2023-04-20T15:23:03.343

3

I had the similar question and this is workaround that I followed:

First you should insert non dependent entities and trigger SaveChanges / SaveChangesAsync so that your entities will get id-s (for more information check the answer from another thread), in your case, first you have to add File and call SaveChanges / SaveChangesAsync, then the same you should do for Page, after each step, you'll get id-s filled in. After that you can use third insert (which in your case is Segment) without getting any errors, as you can have all referenced id-s in place.

edited Apr 20 '23 at 15:23

answered Dec 23 '19 at 09:10

Irakli

562
4
15

`Page` depends on `File` in my case.So in my case i could insert the `File` and then get its id and from there just insert `Page`-s with their `Segments` and all would get linked ? `Segment` needs foreign key of both `FIle` and `Page` while `Page` needs only `File` – Bercovici Adrian Dec 23 '19 at 09:11
Missed that link in my previous version of my answer. Indeed, this approach means that you are inserting entities one by one, or group after group, but keep the order, and do not forget calling `SaveChanges` or `SaveChangesAsync` on them, as that step fills in the id for your entity. Updated my answer accordingly. – Irakli Dec 23 '19 at 09:28

How to insert triangle relationship into DB using EF

1 Answers1