Entity Framework maintain foreign key for multiple inserts

Question

I inherited a simple database with 3 tables.

Board Table : BoardID, BoardName City Table: CityID, CityName, BoardID Child Table: ChildID, ChildName, CityID, BoardID

All of the ID fields have identity insert turned on.

When I do inserts,

I am inserting into Board first, saving changes, inserting the ID into City Table, saving changes and then finally inserting both IDs into the Child Table and saving changes.

Is there way to maintain this within the context and save the changes at once ?

Answer 1

Yes, start by reading up on navigation properties and mapping relationships with EF. Using these relationships, EF will track which entities depend on the other so that you would create your entity graph like:

using ( var context = new MyContext())
{
    var newBoard = new Board { BoardName = boardName };
    var newCity = new City { CityName = cityName, Board = newBoard };
    var newChild = new Child { ChildName = childName, City = newCity };
    context.Children.Add(newChild);
    context.SaveChanges();
}

When EF goes to commit the changes, each entity mapped with an identity column will get it's new ID, and those IDs will automatically be assigned to the FKs of the associated entities.

With EF you also do not need to expose FK fields in your entities, and it is advisable to avoid exposing FKs. With EF Core you can use Shadow Properties to map your entities. Exposing FKs can lead to issues when updating. If a child exposes a City reference and a CityId FK property and I want to assign a new city to the child, I now have 2 ways:

child.City = fourthCity;
// or 
child.CityId = 4;

This can lead to inconsistent behaviour and bugs around bad assumptions. For instance:

var child = context.Children.Find(1); // Child #1 references City #1.
var fourthCity = context.Cities.Find(4);

child.City = fourthCity;
Console.WriteLine(child.City.CityId); // "4"
Console.WriteLine(child.CityId); // "1" 
context.SaveChanges();
Console.WriteLine(child.CityId); // Now it's "4" 

Any code referencing the FK prior to a SaveChanges will get the old ID, while code referencing the related entity's ID will get the new one. The FK is not updated until SaveChanges gets called.

Changing the FK can lead to issues and errors as well..

var child = context.Children.Find(1); // Child #1 references City #1.

child.CityId = 4;
Console.WriteLine(child.CityId); // "4" 
Console.WriteLine(child.City.CityId); // NullReferenceException! (Lazy load call fails)

Lazy loading (at least in EF6) chucks a wobble if you try to do this. If you eager load the city:

var child = context.Children.Include(x => x.City).Single(x => x.ChildId == 1); // Child #1 references City #1.

child.CityId = 4;
Console.WriteLine(child.CityId); // "4" 
Console.WriteLine(child.City.CityId); // "1"
Context.SaveChanges();
Console.WriteLine(child.City.CityId); // "4"

You will encounter inconsistent behaviour where code references FKs vs. navigation properties. There are cases where you may want entities to expose FKs rather than navigation properties (such as when performing high volume operations) so I would recommend using one or the other, never both.

Also, avoid denormalizing your schema. If a Child is associated to a City, and a City to a Board, you can reference a child's board through it's city rather than having a BoardID on the child.

I.e. child.City.Board.BoardName

Denormalization is problematic because there is no way to enforce that a child's board reference will match it's assigned City's board reference.

For instance:

var board1 = context.Boards.Find(1);
var board2 = context.Boards.Find(2);
var child = context.Children.Find(1);

If Child Id #1 references City Id #1 which references Board Id #1, and Child also has a reference to Board Id #1, then something like this in the code, intentional or by mistake will potentially cause issues down the road:

child.Board = board2;

Any code referencing a child's board via child.Board.BoardId would return "2", however any code that references child.City.Board.BoardId would still get return "1". Similar to the issue with exposing FKs on your entities, these denormalized references can lead to bad references depending on where you look, except unlike the FK issue, these don't auto-resolve when changes are committed. A child's board ID and the Board ID of it's referenced City have fallen out of sync in the data.