Deleting multiple records for a related table

Question

I have these two tables in my database, named Vendors and VendorPriceBreaks:

Vendors
-----------------
VendorID (PK)
Name

VendorPriceBreaks
-----------------
VendorPriceBreakID (PK)
VendorID (FK)
Price

When I delete a Vendor, I'd like to have all the VendorPriceBreaks associated with it deleted as well. I'm using Entity Framework.

I tried this first:

public RedirectToRouteResult Delete(int id)
{
    MyEntities entities = new MyEntities();

    var vendor = entities.Vendors.FirstOrDefault(v => v.VendorID == id);
    entities.Vendors.Context.DeleteObject(vendor);
    entities.Vendors.Context.SaveChanges();

    return RedirectToAction("Index");
}

Which gave me the error message: The DELETE statement conflicted with the REFERENCE constraint "FK_VendorPriceBreaks_Vendors"

So then I added this line before I deleted my object: vendor.VendorPriceBreaks.Clear();

But then I got this error message: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

What's the best way to do this?

Answer 1

You could 'include' your child entities.

var vendor = entities.Vendors
                     .Include("VendorPriceBreaks")
                     .FirstOrDefault(v => v.VendorID == id);

Alternatively, you can modify a property on the relationship between your 2 entities in the .edmx designer.

Answer 2

I think you should create a procedor:

Create proc DeleteRecords
@VendorID int       
As
BEGIN TRY
    BEGIN TRAN
           DELETE FROM VendorPriceBreaks
        WHERE VendorID =@VendorID
        DELETE FROM Vendors
        WHERE VendorID =@VendorID        
    COMMIT TRAN
END TRY
BEGIN CATCH
  ROLLBACK TRAN
END CATCH

And after you can add this proc to your entityFramwork as a function.

happy coding

Answer 3

If you don't have enabled cascading delete in the database (which would be best option in your model, I think; see p.campbell's answer) you have to delete the items in the collection explicitely:

public RedirectToRouteResult Delete(int id)
{
    MyEntities entities = new MyEntities();

    var vendor = entities.Vendors.FirstOrDefault(v => v.VendorID == id);
    foreach (var item in vendor.VendorPriceBreaks.ToList())
        entities.VendorPriceBreaks.Context.DeleteObject(item);
    entities.Vendors.Context.DeleteObject(vendor);
    entities.Vendors.Context.SaveChanges();

    return RedirectToAction("Index");
}

Answer 4

Other guys have already provided valuable answers so you should select on of them as accepted answers.

I'm just adding this as answer because it is too long for comment. There are generally four ways to achieve this:

Casade delete as described by @p.campbell. The important is to define ON CASCADE DELETE on relation in database as well as Cascade on relation in EF designer. Here is a reason why the option must be set on both sides.

Stored procedure as described by @Bilgehan. Just in case of stored procedure delete childs before the parent. You can actually execute SQL directly by ExecuteStoreCommand.

Identifying relation - this is one of the strangest architecutre decissions in EF. It requires that child entity has composite key from its unique Id and FK to parent. If this happens you can simply call vendor.VendorPriceBreaks.Clear() and it will not only remove relations but also deletes childs. The disadvantage is that related objects must be loaded from database.

Manual deletion of related objects as described by @Slauma. If you don't have cascade delete, stored procedure or identifying relation there is no other way then load related objects and delete them one by one.