EF Core DB-first: How to delete dependent item without cascade delete?

Question

(Example code is posted at the end.)

I'm using EF Core 5 database-first (i.e. reverse engineered POCOs). When 'deleting' an item in a collection using .Remove(), a subsequent SaveChanges() complains about the orphaned child. Yup, plenty of Q&A can be found about that.

For code-first scenarios, one can add this to the configuration (from here):

modelBuilder.Entity<Child>.HasKey(t => new { t.ParentId, t.ChildId });

but it is not practical for DB-first. If the database has cascade delete set up for the FK relationship (docs reference here), the generated model [correction] does not include

entity.OnDelete(DeleteBehavior.ClientSetNull)

and the delete (using .Remove()) does work, including SaveChanges(). But ...

The problem I face here is that this "solution" requires all our FK relationships to be configured with cascade delete in the database. Yikes! Data integrity is important to us and safeguarding it with UNIQUE constraints and non-nullable FK relationships has saved us before. Not having cascade deletes set up is a similar safeguard, and using it only for the severed relationship case is arguably overkill.

So, on to the third option: Explicitly delete the orphaned child item, noting that I'm in the second bullet described here. All I need is the good old ObjectContext.DeleteObject() but there is no EF Core equivalent.

Q: How can I delete that child item? And if it is not possible, do I have any other options?

Here is a quick example the brings it to life in code:

//Create a parent with two children
var db = new Context();            
var newParent = new Parent() {Name = "NewParent"+DateTime.Now.Second};
db.Parents.Add(newParent);
newParent.Children = new List<Child>() {new Child() {Name="ChildOne"}, new Child() {Name="ChildTwo"}};
db.SaveChanges();
db.Dispose();

//Pick a parent item and delete one child item
var db2 = new Context();
var Parent = db2.Parents.First();
db2.Entry(Parent).Collection(f => f.Children).Load();
var ChildToDelete = Parent.Children.First();
Parent.Children.Remove(ChildToDelete);
db2.SaveChanges(); // -----> FAILS with "The association between entity types 'Parent' 
                   // and 'Child' has been severed, but the relationship is either marked
                   //  as required or is implicitly required because the foreign key 
                   // is not nullable."

and the tables are essentially

CREATE TABLE [dbo].[Child](
    [ChildId] [int] IDENTITY(1,1) NOT NULL,
    [ParentId] [int] NOT NULL,
    [Name] [nchar](20) NOT NULL,
 CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED)

CREATE TABLE [dbo].[Parent](
    [ParentId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](20) NOT NULL,
 CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED)

ALTER TABLE [dbo].[Child]  WITH CHECK ADD  CONSTRAINT [FK_Child_Parent] FOREIGN KEY([ParentId])
REFERENCES [dbo].[Parent] ([ParentId])
ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK_Child_Parent]

score 1 · Accepted Answer · answered Jul 21 '21 at 19:00

1

Instead of removing the Child from the Parent's collection

Parent.Children.Remove(ChildToDelete);

delete the child with

db.Set<Child>().Remove(ChildToDelte);

answered Jul 21 '21 at 19:00

David Browne - Microsoft

80,331
6
39
67

1

Oh! `ICollection.Remove(T)` vs. `EntityFrameworkCore.DbSet.Remove(T)`. Two quite different `Remove()`'s – Ian W Jul 22 '21 at 01:11
What if you do not have access to the data context? In a DDD based model there is no knowledge or access to persistence from inside the model, so what are the alternatives? Doesn't EF detect the removal of the child from the collection? In my case it does but it tries to set the foreign key to null, even if I set the relationship to cascade or even client cascade. Does this not work at all for database first? – Jason Rodman Feb 10 '23 at 14:18
You don't access the DbContext from the model, but from some service which wraps the DbContext. – David Browne - Microsoft Feb 10 '23 at 15:18

EF Core DB-first: How to delete dependent item without cascade delete?

1 Answers1