How to insert with foreign key that doesn't exist yet in Entity Framework?

Question

Using .NET 4, MVC 4, Entity Framework 5, SQL Server;

I want to insert in one transaction a new Header record and several new HeaderData records which all have a Foreign Key to the inserted Header record. Header record has an Identity int Primary Key.

Entities.Header h = new Entities.Header();

h.Name = name;
h.Time = DateTime.Now;
h.Comments = comments;

db.Headers.Add(h);
// db.SaveChanges(); // Save changes here?
// and get ID to use below via h.ID?

foreach (DataRecord dr in datarecords) // my own custom types here
{
    Entities.HeaderData hd = new Entities.HeaderData();
    // hd.header = thisid // ?? this is the FK to Header.ID, its Identity int PK
    hd.name = dr.name
    hd.value = dr.value

    db.HeaderDatas.Add(hd)
}

db.SaveChanges(); // or wait to save all here?

So problem is, I don't know what the header record ID is going to be to put in the data records' FK field until after it is committed. Or do I? Just referencing h.ID before the SaveChanges/Commit did not work, it returned 0.

Options: 1) Should I just commit the header record first, get the PK to use, then populate the data record models and commit them separately? Might have to do a rollback of the header in such case, sounds like a less than optimal way to go about it.

2) Should I be using a GUID PK or similar instead, where I create it here in the app? That is the only place the records can be added from anyway.

3) Is there a slick way in Entity Framework (temporary EntityKey maybe?), and have it do the transaction inserts so that it will automatically put the right header ID in the data records' FK fields? This seems doable to EF but I couldn't exactly find a reference to it.

Slauma · Accepted Answer · 2013-03-29 17:26:29Z

9

If Header and HeaderData are related by a foreign key (one-to-many) relationship you should have a navigation collection Header.HeaderDatas (of type ICollection<HeaderData> or another collection type) in Header or a navigation reference HeaderData.Header (of type Header) in HeaderData or even both.

In either case the better way is to build the relationship using those navigation properties:

Entities.Header h = new Entities.Header();
h.HeaderDatas = new List<HeaderData>();
// ...
foreach (DataRecord dr in datarecords)
{
    Entities.HeaderData hd = new Entities.HeaderData();
    //...
    h.HeaderDatas.Add(hd)
}
db.Headers.Add(h);
db.SaveChanges();

Or:

Entities.Header h = new Entities.Header();
// ...
foreach (DataRecord dr in datarecords)
{
    Entities.HeaderData hd = new Entities.HeaderData();
    //...
    hd.Header = h;

    db.HeaderDatas.Add(hd);
}
db.SaveChanges();

You don't need to set the FK directly. EF will correctly "translate" the navigation properties you've set into the necessary foreign key values for the database tables.

answered Mar 29, 2013 at 17:26

Slauma

177k60 gold badges407 silver badges424 bronze badges

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

1 Comment

StackticMain Over a year ago

Is there any reason why the child table (HeaderData in the above example) would generate zero in the foreign key column (i.e., PK w.r.t Header table) instead of DB generated ID (using identity(1,1)).....In other words the DB generated PKs are not propagating to the child table when a new record is added to the Parent and Child table...please help.

Josh Fischer · Accepted Answer · 2017-03-28 16:45:53Z

Navigation properties are not necessary. Use a transaction and call SaveChanges() twice; once after adding the first entity and once at the end. Because EF can be used many different ways, the sample below may not be the exact solution for your situation, but it should drive home the point.

using (MyContext context = new MyContext())
using (DbContextTransaction txn = context.Database.BeginTransaction())
{
    try
    {
        EntityA a = new EntityA();
        a.Foo = "bar";
        context.A_DbSet.Add(a); // Assuming the use of the DbSet<T> class
        context.SaveChanges();
        // A.ID is now set, but not committed

        EntityB b = new EntityB();
        b.A_ID = a.ID;
        b.Foo2 = "bar2";
        context.B_DbSet.Add(b);
        context.SaveChanges();
        txn.Commit();
    }
    catch (Exception ex)
    {
        txn?.Rollback();
    }
}

Collectives™ on Stack Overflow

How to insert with foreign key that doesn't exist yet in Entity Framework?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related