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.
