Add multiple relational data into SQL server using EF core

Question

I am trying to insert multiple relational data into SQL server DB using EF core 5. I want to insert multiple parents with multiple child. Parents and child has one to many relationship. I am trying to add it using context.AddRang(lstparents) It is inserting child entity data only for one parents and for rest of the parents there is no entry. Could you please help me to resolve this issue.

My Models

    Public class Parent
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      Public int64 Id {get; set;}// this is identity column
    
      Public string Name { get; set;}
    
      Public List<Child> child { get; set;}
    }

    Public class Child
    {
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       Public int64 id { get; set;} // identity column
       Public string ChildName { get; set;}
    
       Public int64 ParentId { get; set;}
    
       [Foreign key("ParentId")]
       Public Parent parent { get; set;}
    }


// Here is insertion logic

Public void main(string[] args)
{

List<Child> lstChild = new List<Child> () 
                      { 
                        new Child{ ChildName= "Child1Name"}, 
                        new Child{ ChildName= "Child2Name"}, 
                        new Child{ ChildName= "Child3Name"} 
                      }; 
List<Parent> lstparents = new List<Parent>()
                       { 
                        new Parent {Name = "xyz", Child= lstChild }, 
                        new Parent {Name = "xyz1", Child= lstChild} 
                       };
Context.AddRangeAsync(lstparents); 
Context.SaveChangesAsync();


}

I have tried below options as well but I ran into another problem.

Option 1:

Public void main(string[] args)
    {
    
    List<Child> lstChild = new List<Child> () 
                          { 
                            new Child{ ChildName= "Child1Name"}, 
                            new Child{ ChildName= "Child2Name"}, 
                            new Child{ ChildName= "Child3Name"} 
                          }; 
    List<Parent> lstparents = new List<Parent>()
                           { 
                            new Parent {Name = "xyz", Child= lstChild }, 
                            new Parent {Name = "xyz1", Child= lstChild} 
                           };
    foreach(var item in lstparents)
    {
        Context.Add(item); 
        foreach(var child in lstChild)
        {
            Context.Add(child); 
         }
     }
     Context.SaveChangesAsync();   
    }

option2: In below line of code i am getting an error "Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF"

Public void main(string[] args) {

    List<Child> lstChild = new List<Child> () 
                          { 
                            new Child{ ChildName= "Child1Name"}, 
                            new Child{ ChildName= "Child2Name"}, 
                            new Child{ ChildName= "Child3Name"} 
                          }; 
    List<Parent> lstparents = new List<Parent>()
                           { 
                            new Parent {Name = "xyz", Child= lstChild }, 
                            new Parent {Name = "xyz1", Child= lstChild} 
                           };
    foreach(var item in lstparents)
    {
        Context.Entry(item).State=EntityState.Added; 
        foreach(var child in lstChild)
        {
            Context.Entry(child).State=EntityState.Added; 
         }
     }
     Context.SaveChangesAsync();   
    }

Serge · Accepted Answer · 2021-10-15 02:55:35Z

1

Since you have one-to-many relations (one parent can have many children) you can't add one child set to many parents. If you neeed it , you need to configure many-to-many relations. So if you want to add 2 parents you will have to create 2 lists of children, not one

List<Child> childList1 = new List<Child> () 
                      { 
                        new Child{ ChildName= "Child1Name"}, 
                        new Child{ ChildName= "Child2Name"}, 
                        new Child{ ChildName= "Child3Name"} 
                      }; 
List<Child> childList2 = new List<Child> () 
                      { 
                        new Child{ ChildName= "Child4Name"}, 
                        new Child{ ChildName= "Child5Name"}, 
                        new Child{ ChildName= "Child6Name"} 
                      }; 
List<Parent> lstparents = new List<Parent>()
                       { 
                        new Parent {Name = "parent1", Child= childList1 }, 
                        new Parent {Name = "parent2", Child= childList2 } 
                       };

and since your main is not async you can only use sync operations

Context.AddRange(lstparents); 
Context.SaveChanges();

and IMHO maybe it is a good idea to make ParentId nullable

 public long? ParentId { get; set;}

and if you need to add many to many you need to add a ParentChild table.

If you use net5+ , ef core can add it for you , you just need to change navigaion properties

 Public class Parent
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
      public long Id {get; set;}// this is identity column
    
      public string Name { get; set;}
    
      public virtual ICollection<Child> Childs { get; set;}
    }

    Public class Child
    {
       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       public long Id { get; set;} // identity column
       public string ChildName { get; set;}
          
       public virtual ICollecton<Parent> Parents { get; set;}
    }

edited Oct 15, 2021 at 2:55

answered Oct 15, 2021 at 2:24

Serge

44.4k4 gold badges26 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Manoj Raut Over a year ago

As you rightly said I need to have one parent and many children. In my case I would like to add same children with different primary key to different parent. My Child list will be same but while inserting in DB it has different primary key for each parent. Can we do that in EF Core.

Serge Over a year ago

@user1776386 I updated my answer. After this you will have to make a clean migration to Db again.

Ivan Stoev Over a year ago

@user1776386 "I would like to add same children with different primary key to different parent" They are not the same if PK (entity identity) is different. Having same Name and other properties does not make them same. Use separate objects and lists as Serge showed at the beginning of the answer.

Collectives™ on Stack Overflow

Add multiple relational data into SQL server using EF core

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related