Nrthugu

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.