Blog

This blog will show how to use Entity Framework Core to copy multiple related entities stored in a SQL database while maintaining their keyed relationships.

While EF Core doesn’t offer a `Copy` method, there are simple ways to duplicate an entry in a database. However, database schemas often use multiple entities, linked by foreign keys, to represent a single conceptual item. Duplicating an item of this kind is more complicated since a user must copy all the sub-entities and then update their foreign keys.

Fortunately, EF Core can be manipulated to make this duplication trivial. Using the concept of detached entities, EF Core can duplicate entities and automatically update their foreign keys.

Detached Entities

Detached Entities are entities that no longer correspond to data in the database. When EF Core returns entities from a database query, these entities are, by default, tracked relative to the database. If a property on a tracked entity is changed and saved, the corresponding data in the database will be changed. However, we can tell EF Core to detach entities after they have been fetched.

Once detached, an entity no longer corresponds to an entry in the database. Changes to the object will not be applied to the database when SaveChanges is called. EF Core will view these objects as entirely unrelated to the database.

Detach and Duplicate

Once detached, you can add entities back to the database. Since EF Core is no longer tracking them, they will be viewed as new pieces of data. Consequently, a detached entity that is added and saved will result in a new entry in the database. Using this idea, we can copy data by fetching an entity, detaching the entity, and then adding it back to the database (and saving).

Note: If you are using auto-incrementing primary keys, the primary key of the duplicate entities will need to be set to 0 before adding them back to the database. This avoids adding duplicate primary keys to the database (which will cause the database to throw an exception when saving). See Step 2 below for a generic function to handle detaching and resetting primary keys.

Handling Foreign Keys

Duplicating data with this technique will automatically update the new data’s foreign keys. If a duplicate entity’s foreign key points at data that is not being duplicated, then that foreign key will stay the same.

However, if a foreign key is pointed at another piece of data that has been detached, the foreign key will be updated to point at the new, duplicate data. This way, if the database duplicates related entities in the same transaction, their relationships will be duplicated as well.

Example

Here is the idea in practice. In this example, a League is made up of Teams that are made up of Players.

Step One: Fetch the Entities

First, we select the entities we would like to duplicate from the database

	 var league = db.Leagues.FirstOrDefault(l => l.Name == “EPL”);
     var teams = league.Teams.ToList();
     var players = teams.SelectMany(t => t.Players).ToList();

Step Two: Detach the Entities

Using the helper functions DetachEntity and DetachEntities, we detach our fetched entities. The database duplicates any entity that is detached. If an entity is detached, and it is related to another detached entity, that relationship will be duplicated between the two new entities.

Note: The detach functions also set the primary key (“Id” in this case) to 0 since we are using auto-incrementing primary keys.

private T DetachEntity<T>(T entity, ConfigDbContext db) where T : class
{
    db.Entry(entity).State = EntityState.Detached;
    if (entity.GetType().GetProperty("Id") != null)
    {
        entity.GetType().GetProperty("Id").SetValue(entity, 0);
    }
    return entity;
}

private List<T> DetachEntities<T>(List<T> entities, ConfigDbContext db) where T : class
{
    foreach (var entity in entities)
    {
        this.DetachEntity(entity, db);
    }
    return entities;
}
this.DetachEntity(league, db);
this.DetachEntities(teams, db);
this.DetachEntities(players, db);	

Step Three: Add the Detached Entities Back to the Database

  await db.Leagues.AddAsync(league);
  await db.Teams.AddRangeAsync(teams);
  await db.TestPropertyGroups.AddRangeAsync(players);

Step Four: Save Changes

We have duplicated the league “EPL” and its teams and players. The relationships between the teams, players, and league have also been duplicated. So, each duplicate player is tied to a duplicate team, and each duplicate team is tied to the new, duplicate league.

	await db.SaveChangesAsync();

This technique makes Entity Framework do most of the work and saves us from explicitly updating several primary keys. Any related entities that are detached together will have their relationship duplicated as well.

Learn more about DMC's Application Development Services as well as our C# and ASP.NET expertise. Contact us with any questions or inquiries.