Where'd My Data Go? (and/or...How Do I Get Rid of It?)
- by David Paquette
Want to get a better idea of how cascade deletes work in Entity Framework Code First scenarios? Want to see it in action? Stick with us as we quickly demystify what happens when you tell your data context to nuke a parent entity. This post is authored by Calgary .NET User Group Leader David Paquette with help from Microsoft MVP in Asp.Net James Chambers. We got to spend a great week back in March at Prairie Dev Con West, chalk full of sessions, presentations, workshops, conversations and, of course, questions. One of the questions that came up during my session: "How does Entity Framework Code First deal with cascading deletes?". James and I had different thoughts on what the default was, if it was different from SQL server, if it was the same as EF proper and if there was a way to override whatever the default was. So we built a set of examples and figured out that the answer is simple: it depends. (Download Samples) Consider the example of a hockey league. You have several different entities in the league including games, teams that play the games and players that make up the teams. Each team also has a mascot. If you delete a team, we need a couple of things to happen: The team, games and mascot will be deleted, and The players for that team will remain in the league (and therefore the database) but they should no longer be assigned to a team. So, let's make this start to come together with a look at the default behaviour in SQL when using an EDMX-driven project. The Reference – Understanding EF's Behaviour with an EDMX/DB First Approach First up let’s take a look at the DB first approach. In the database, we defined 4 tables: Teams, Players, Mascots, and Games. We also defined 4 foreign keys as follows: Players.Team_Id (NULL) –> Teams.Id Mascots.Id (NOT NULL) –> Teams.Id (ON DELETE CASCADE) Games.HomeTeam_Id (NOT NULL) –> Teams.Id Games.AwayTeam_Id (NOT NULL) –> Teams.Id Note that by specifying ON DELETE CASCADE for the Mascots –> Teams foreign key, the database will automatically delete the team’s mascot when the team is deleted. While we want the same behaviour for the Games –> Teams foreign keys, it is not possible to accomplish this using ON DELETE CASCADE in SQL Server. Specifying a ON DELETE CASCADE on these foreign keys would cause a circular reference error: The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE – MSDN When we create an entity data model from the above database, we get the following: In order to get the Games to be deleted when the Team is deleted, we need to specify End1 OnDelete action of Cascade for the HomeGames and AwayGames associations. Now, we have an Entity Data Model that accomplishes what we set out to do. One caveat here is that Entity Framework will only properly handle the cascading delete when the the players and games for the team have been loaded into memory. For a more detailed look at Cascade Delete in EF Database First, take a look at this blog post by Alex James. Building The Same Sample with EF Code First Next, we're going to build up the model with the code first approach. EF Code First is defined on the Ado.Net team blog as such: Code First allows you to define your model using C# or VB.Net classes, optionally additional configuration can be performed using attributes on your classes and properties or by using a Fluent API. Your model can be used to generate a database schema or to map to an existing database. Entity Framework Code First follows some conventions to determine when to cascade delete on a relationship. More details can be found on MSDN: If a foreign key on the dependent entity is not nullable, then Code First sets cascade delete on the relationship. If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null. The multiplicity and cascade delete behavior detected by convention can be overridden by using the fluent API. For more information, see Configuring Relationships with Fluent API (Code First). Our DbContext consists of 4 DbSets: public DbSet<Team> Teams { get; set; }
public DbSet<Player> Players { get; set; }
public DbSet<Mascot> Mascots { get; set; }
public DbSet<Game> Games { get; set; }
When we set the Mascot –> Team relationship to required, Entity Framework will automatically delete the Mascot when the Team is deleted. This can be done either using the [Required] data annotation attribute, or by overriding the OnModelCreating method of your DbContext and using the fluent API.
Data Annotations:
public class Mascot
{
public int Id { get; set; }
public string Name { get; set; }
[Required]
public virtual Team Team { get; set; }
}
Fluent API:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Mascot>().HasRequired(m => m.Team);
}
The Player –> Team relationship is automatically handled by the Code First conventions. When a Team is deleted, the Team property for all the players on that team will be set to null. No additional configuration is required, however all the Player entities must be loaded into memory for the cascading to work properly.
The Game –> Team relationship causes some grief in our Code First example. If we try setting the HomeTeam and AwayTeam relationships to required, Entity Framework will attempt to set On Cascade Delete for the HomeTeam and AwayTeam foreign keys when creating the database tables. As we saw in the database first example, this causes a circular reference error and throws the following SqlException:
Introducing FOREIGN KEY constraint 'FK_Games_Teams_AwayTeam_Id' on table 'Games' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint.
To solve this problem, we need to disable the default cascade delete behaviour using the fluent API:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Mascot>().HasRequired(m => m.Team);
modelBuilder.Entity<Team>()
.HasMany(t => t.HomeGames)
.WithRequired(g => g.HomeTeam)
.WillCascadeOnDelete(false);
modelBuilder.Entity<Team>()
.HasMany(t => t.AwayGames)
.WithRequired(g => g.AwayTeam)
.WillCascadeOnDelete(false);
base.OnModelCreating(modelBuilder);
}
Unfortunately, this means we need to manually manage the cascade delete behaviour. When a Team is deleted, we need to manually delete all the home and away Games for that Team.
foreach (Game awayGame in jets.AwayGames.ToArray())
{
entities.Games.Remove(awayGame);
}
foreach (Game homeGame in homeGames)
{
entities.Games.Remove(homeGame);
}
entities.Teams.Remove(jets);
entities.SaveChanges();
Overriding the Defaults – When and How To
As you have seen, the default behaviour of Entity Framework Code First can be overridden using the fluent API. This can be done by overriding the OnModelCreating method of your DbContext, or by creating separate model override files for each entity. More information is available on MSDN.
Going Further
These were simple examples but they helped us illustrate a couple of points. First of all, we were able to demonstrate the default behaviour of Entity Framework when dealing with cascading deletes, specifically how entity relationships affect the outcome. Secondly, we showed you how to modify the code and control the behaviour to get the outcome you're looking for.
Finally, we showed you how easy it is to explore this kind of thing, and we're hoping that you get a chance to experiment even further. For example, did you know that:
Entity Framework Code First also works seamlessly with SQL Azure (MSDN)
Database creation defaults can be overridden using a variety of IDatabaseInitializers (Understanding Database Initializers)
You can use Code Based migrations to manage database upgrades as your model continues to evolve (MSDN)
Next Steps
There's no time like the present to start the learning, so here's what you need to do:
Get up-to-date in Visual Studio 2010 (VS2010 | SP1) or Visual Studio 2012 (VS2012)
Build yourself a project to try these concepts out (or download the sample project)
Get into the community and ask questions! There are a ton of great resources out there and community members willing to help you out (like these two guys!).
Good luck!
About the Authors
David Paquette works as a lead developer at P2 Energy Solutions in Calgary, Alberta where he builds commercial software products for the energy industry. Outside of work, David enjoys outdoor camping, fishing, and skiing. David is also active in the software community giving presentations both locally and at conferences. David also serves as the President of Calgary .Net User Group.
James Chambers crafts software awesomeness with an incredible team at LogiSense Corp, based in Cambridge, Ontario. A husband, father and humanitarian, he is currently residing in the province of Manitoba where he resists the urge to cheer for the Jets and maintains he allegiance to the Calgary Flames. When he's not active with the family, outdoors or volunteering, you can find James speaking at conferences and user groups across the country about web development and related technologies.
