Odtnhj

I want to delete 2 set of data in database, using EF Core.

All codes are hypothetical.

Data models:

class Parent

 public int Id get; set; 


class Child

 public int Id get; set; 

 public int ParentId get; set; 

 public virtual Parent Parent get; set; 

 public bool Flag get; set; 

Let's assume I want to delete all [Child] records with (ParentId=100) and (flag=false), after that if (child.ParentId=100).length=0 then delete the parent itself too.

So, here is the service class:

class Service

 public void Command(int parentId)
 
 Parent parent = GetParent(parentId);
 List<Child> children = GetChildren(parent);

 List<Child> toDelete = children.Where(x => !x.Flag).ToList();
 foreach(var child in toDelete)
 
 var entry = DbContext.Entry(child);
 entry.State = EntityState.Deleted;
 

 List<Child> remainChildren = children.Where(x => x.Flag).ToList();
 if (!remainChildren.Any())
 
 var entry = DbContext.Entry(parent );
 entry.State = EntityState.Deleted;
 

 SaveChanges();
 

I have multiple scenarios that call the Service.Command method.

Because I call SaveChanges() only once, I assume that all delete operations will be executed in a single transaction, and of course they would be in this order:

1. Delete child records


2. Delete parent

but EF send queries to database like this:

1. Delete parent


2. Delete child records

Obviously it will throw an ForeignKey exception.

Is there any way to enforce EF Core to execute queries in order that I wrote the code?

Set the parent child relationship to cascade delete at the DB level.

Query the needed data in one hit...

var data = context.Parents.Where(p => p.ParentId == parentId)
 .Select(p => new 
 
 Parent = p,
 ChildrenToRemove = p.Children.Where(c => c.Flag).ToList(),
 HasRemainingChildren = p.Children.Any(c => !c.Flag)
 ).Single();

Then it's just a matter of inspecting the data and acting accordingly. If there are no remaining children, delete the parent and let cascade take care of it. Otherwise, just delete the children from the context.

if(!data.HasRemainingChildren)
 context.Parents.Remove(data.Parent);
else
 context.Children.RemoveRange(data.ChildrenToRemove);

For big entities you can further optimize this by selecting just the IDs then associating them to new Entity instances, attach them to a fresh DbContext, and then issue the Remove/RemoveRange calls. This option is an optimization for dealing with large numbers of items, or "big" entities that would otherwise result in a lot of data across the wire.