Entity Framework - Inserting a Parent and Child model with FK simultaneously

Question

I have an ASP.NET MVC application using Entity Framework. I have developed 2 models, using the database first (code) approach for a SQL Server 2008 R2 database. One model is essentially a parent model, and the other is a child model that contains a foreign key to a database generated column of the parent model.

Pseudo code:

CREATE TABLE PARENT
(
    ROWID BIGINT IDENTITY(1,1) NOT NULL,
    VALUE NVARCHAR(255) NOT NULL,

    PRIMARY KEY(ROWID)
);

CREATE TABLE CHILD
(
    ROWID BIGINT IDENTITY(1,1) NOT NULL,
    PARENT_ROWID BIGINT NOT NULL,
    VALUE NVARCHAR(255) NOT NULL,

    PRIMARY KEY(ROWID),
    FOREIGN KEY(PARENT_ROWID) REFERENCES PARENT(ROWID)
);

All of this works fine, and I'm able to bind my data to my model and edit the fields and so on. My question is, if I am trying to create records for these models, how can I do that when the foreign key for CHILD is in PARENT which isn't yet available if I create them at the same time?

So, my current basic steps are:

1. Get a list of the PARENT models to insert into the database
2. Insert the PARENT models

3. Call SaveChanges()

   • For each PARENT:

     1. Get FK for PARENT
     2. Get a List of the CHILD models to insert into the database
     3. For each CHILD model, insert CHILD with that FK
     4. Call SaveChanges()

4. Additional operations and updates to database for other models

5. Call SaveChanges()

I'm sure I could optimize that, but that's not what I'm concerned with. It seems to me like there should be some way to do this without manually looking up the FK or manually setting the FK. If I have to, I can do this, but I would like to use only one SaveChanges() call to push these commands through in a single transaction. I'm looking for something along the lines of:

1. Get a List of the PARENT models to insert into the database

   • For each PARENT
     1. Insert PARENT and return new FK (without saving)
     2. Get a List of the CHILD models to insert into the database
     3. For each CHILD model, insert CHILD with that FK

2. Additional operations and updates to Database for other models

3. Call SaveChanges()

To provide some context, my actual table has many columns that aren't guaranteed to be unique (which is why the PK is on ROWID), and each of those rows has a none to many relationships with the child table, where the child table also may have non-unique rows. Not very ideal, but it's what our operations require as of now. I included pseudo-code here because the actual code is proprietary, but I could probably create a real mock-up if needed.

Any suggestions?

Thank you

EDIT: So I changed my code to make use of the navigation property as suggested. This almost works. Here is my code:

List<PARENT> parents = getParents();
List<CHILD> children = getChildren();
foreach (PARENT parent in parents)
{
    db.PARENT.add(parent);
    foreach (CHILD child in children)
    {
        child.PARENT = parent;
        db.CHILD.add(child);
    }
}

db.SaveChanges();

At first glance, this is exactly what I was looking for. However, when I run it, the child records are only being inserted for the first parent record. So, for example, if I have 3 PARENT records, and 3 CHILD records, I should have 9 new records in my CHILD table. However, after this code, I only have 3 records for the first PARENT.

I'm assuming this has something to do with the fact that I'm using the same list of children repeatedly and Entity Framework is viewing them as duplicates somehow. I moved it into the PARENT for loop as follows and it works as expected:

List<PARENT> parents = getParents();
foreach (PARENT parent in parents)
{
    db.PARENT.add(parent);
    List<CHILD> children = getChildren();
    foreach (CHILD child in children)
    {
        child.PARENT = parent;
        db.CHILD.add(child);
    }
}

db.SaveChanges();

But, I would prefer not to do this as the getChildren() function is rather cumbersome. Any thoughts?

Answer 1

I used the below code to seed initial dB values for a countries table with cities, districts, neighborhoods and their translations within one dB call.

you can implement similar structure to create parent —> child —> grandchild entities in the same dB call;

            var country = new MyCountry {
                Title ="Syria",
                ShortName="SYR",
                 Translations=new[]
                 {
                     new CountryTranslate { LCID=1, Name="سوريا" },
                     new CountryTranslate { LCID=31, Name="Suriye" },
                     new CountryTranslate {LCID=9, Name="Syria" }
                 },
                  Cities=new[]
                  {
                      new MyCity
                      {
                          Title="Lattakia",
                          ShortName = "LAT",
                          Translations=new[]
                          {
                              new CityTranslate { LCID=1, Name="اللاذقية" },
                              new CityTranslate {LCID=31, Name="Lazkiye" },
                              new CityTranslate {LCID=9, Name="Lattakia" }
                          },
                          Districts=new[]
                          {
                              new MyDistrict
                              {
                                  Title="Owineh",
                                  Translations=new[]
                                  {
                                      new DistrictTranslate { LCID=1, Name="العوينة" },
                                      new DistrictTranslate { LCID=31, Name="Uveyne" },
                                      new DistrictTranslate {LCID=9, Name="Owineh" }
                                  },
                                  Neighborhoods = new[]
                                  {
                                      new MyNeighborhood
                                      {
                                          Title="Reji",
                                          Translations = new[]
                                          {
                                              new NeighborhoodTranslate { LCID=1, Name="الريجي"},
                                              new NeighborhoodTranslate { LCID=31, Name="Eski Reji"},
                                              new NeighborhoodTranslate { LCID=9, Name="Old Reji"}
                                          }
                                      }
                                  }
                              }
                          }
                      }
                  }
            }

context.SaveChanges();

Answer 2

Use the navigation property; set the ‘Child.Parent’ nav property to the as-yet unsaved ‘Parent’ entity and EF will insert them in the proper order with a single call to ‘SaveChanges’