How to delete child automatically based on parent deletion for database first approach (.edmx)?

user_4927379 0
Viewed 188

Below are my 2 class sharing 1 to many relationship :

public partial class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Skills> Skills { get; set; }
}

public partial class Skills
{
    public int Id { get; set; }
    public Nullable<int> EmployeeId { get; set; }
    public string Skills { get; set; }
    public virtual Employee Employee { get; set; }
}

Now I am trying to remove employees with its corresponding skills in following way :

1) Deleting both employee and skills in 1 method with only save changes. I guess I will be having performance benefit in this case as I need to call save changes only once but there is also 1 issue that if skills got deleted but if error occurs while deleting employee in that case I will lose Skills of corresponding Employee.

public void Delete(int[] ids)
{
    using (var context = new MyEntities())
    {
        context.Skills.RemoveRange(context.Skills.Where(cd => ids.Contains(cd.EmployeeId)));
        context.Employee.RemoveRange(context.Employee.Where(t => ids.Contains(t.Id)));
        context.SaveChanges();
    }
}

2) Another option is to use transaction to make sure that both and child gets deleted successfully like below :

public HttpResponseMessage Delete(int[] ids)
{ 
    using (var context = new MyEntities())
    {
        using (var transaction = context.Database.BeginTransaction())
        {
            try
            { 
                DeleteSkills(ids,context);
                DeleteEmployees(ids,context);
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                // throw exception.
            }
        }
    }
}

public void DeleteEmployees(int[] ids,MyEntities _context)
{
    _context.Employee.RemoveRange(_context.Employee.Where(t => ids.Contains(t.Id)));
    _context.SaveChanges();
}

public void DeleteSkills(int[] ids, MyEntities _context)
{
    _context.Skills.RemoveRange(_context.Skills.Where(cd => ids.Contains(cd.EmployeeId)));
    _context.SaveChanges();
}

3) I am looking for an option where I don't need to remove child (Skills) explicitly and child gets removed automatically based on removal of parent (Employee) entity like the way it happens in case of Code first Cascade on delete so that I don't have to fire 2 queries to remove parent and child (my first option) or I don't have to maintain transaction (my second option.)

I did some research but couldn't find any help on removing child automatically based on removal of parent in case of Database first approach (.edmx).

What is an efficient way to handle this scenario?

edmx ef-database-first entity-framework c#
2 Answers

EF automatically deletes related records in the middle table for many-to-many relationship entities if one or the other entity is deleted.

Thus, EF enables the cascading delete effect by default for all the entities.

If you want manually handle you can use:

 .WillCascadeOnDelete(false);


 protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<parent>()
            .HasOptional<child>(c => c.child)
            .WithMany()
            .WillCascadeOnDelete(false);
    }
Admin1

Read about delete behaviour in Entity Framework.

You can choose how an enitity behave on delete so it can affect child/dependant.

In your case you need "Cascade" delete behaviour which automatically delete children/dependants of the entity you are deleting.

Do it like this in you OnModelCreating method:

 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PARENT>()
             .........
            .OnDelete(DeleteBehavior.Cascade);
    }

Take a look here on how to use and what is about:

https://entityframeworkcore.com/saving-data-cascade-delete#:~:text=Entity%20Framework%20Core%20Cascade%20Delete&text=Cascade%20delete%20allows%20the%20deletion,delete%20behaviors%20of%20individual%20relationships.

user_114372870
Related