After several years of NHibernate, and a couple of years Dapper and NoSQL, I’m now working on a project that uses Entity Framework as its ORM. It’s a mature ORM at this point. However, it does give me a headache as I’m struggling to find ways to do certain things. I’ll admit that I’m trying to dive in without sitting down and doing a few hours of learning first; for sure I’ll be on Pluralsight some time soon.
Fair warning: what comes next is a highly specific, rather technical, and possibly stupidly uninformed dump of a problem I ran into. Feel free to skip this one: no hard feelings, and I’ll see you on my next post!
The main problem: I’ve got a repro, but it’s extremely similar to various other questions on Stack Overflow. Except… that my code contains the accepted (and often highly upvoted) answers’ code as well, but still doesn’t work as advertised!
What I’m trying to do is make sure that EF will delete a “Child” property (i.e. the database row) automatically when its “Parent” is explicitly deleted via the DbContext. Note that the Child is optional in my scenario.
Here’s the repro. Create a new class library and install EntityFramework (I used 6.1.3) and NUnit (I used 3.6.1). Then drop in one namespace these entities:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
public class Parent { [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } public virtual Child Child { get; set; } } public class Child { [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } } |
And then this DbContext:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
public class MyDbContext : DbContext { public MyDbContext(string nameOrConnectionString) : base(nameOrConnectionString) { Database.SetInitializer } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity .HasOptional(p => p.Child) .WithOptionalDependent() .WillCascadeOnDelete(true); } public virtual DbSet public virtual DbSet } |
And finally this TestFixture:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
[TestFixture] public class Tests { const string connectionString = @"Data Source=(LocalDb)\cascades;Integrated Security=True;Initial Catalog=TestDb"; [OneTimeSetUp] public void OneTimeSetUp() { ExecuteNonQuery(@" IF OBJECT_ID('Parents') IS NOT NULL EXEC('DROP TABLE Parents;'); IF OBJECT_ID('Children') IS NOT NULL EXEC('DROP TABLE Children;'); CREATE TABLE Children ( Id INT IDENTITY(1,1) PRIMARY KEY ); CREATE TABLE Parents ( Id INT IDENTITY(1,1) PRIMARY KEY, Child_Id INT, CONSTRAINT FK_Parent_Child FOREIGN KEY (Child_Id) REFERENCES Children(Id) ); "); } [SetUp] public void SetUp() { ExecuteNonQuery(@"DELETE FROM Parents;"); ExecuteNonQuery(@"DELETE FROM Children;"); } [Test] public void Delete_will_cascade() { using (var context = new MyDbContext(connectionString)) { context.Parents.Add(new Parent { Child = new Child() }); context.SaveChanges(); var loadedParent = context.Parents.Include(p => p.Child).Single(); context.Parents.Remove(loadedParent); context.SaveChanges(); Assert.That(context.Database.SqlQuery Assert.That(context.Database.SqlQuery } } private static void ExecuteNonQuery(string ddl) { using (var conn = new SqlConnection(connectionString)) using (var cmd = new SqlCommand(ddl, conn)) { conn.Open(); cmd.ExecuteNonQuery(); } } } |
Make sure the “TestDb” database is available on your “(LocalDb)\cascades” instance (or use another Sql Server instance and database). Then run the tests, and you’ll get:
Test Failed – Delete_will_cascade.
Message: “Child”.
Expected: 0, but was: 1.
All I want is that a “Child” is deleted with its “Parent” once that is deleted. I know I can use a Sql CASCADE, and I know I could manually remove the Child from the context, but I want EF to handle this automatically, damnit!
For sure I’ve missed the obvious solution. I was somewhat hoping it would come to me while writing this. But perhaps I just need a good night’s rest.
References:
- Exactly my issue AFAICT: http://stackoverflow.com/questions/17487577/entity-framework-ef-code-first-cascade-delete-for-one-to-zero-or-one-relations
- Probably also exactly the same: http://stackoverflow.com/questions/22815693/how-do-you-set-entity-framework-to-cascade-on-delete-with-optional-foreign-key
- Probably very similar or even the same: http://stackoverflow.com/questions/5471374/how-do-you-ensure-cascade-delete-is-enabled-on-a-table-relationship-in-ef-code-f
- Similar, but with collections: http://stackoverflow.com/questions/16654828/how-to-remove-child-one-to-many-related-records-in-ef-code-first-database
- Another one about collections: http://stackoverflow.com/questions/15226312/entityframewok-how-to-configure-cascade-delete-to-nullify-foreign-keys
- A three year old (so I’d like to assume no longer relevant?) post with exactly my frustration, and to my surprise a slightly hackish workaround: https://lostechies.com/jimmybogard/2014/05/08/missing-ef-feature-workarounds-cascade-delete-orphans/ – as an afterthought, I had not considered the possibility that EF doesn’t have the feature I’m after, but perhaps I’m just wrong.