In Entity Framework, how to delete record with foreign key constraint?

Question

I have a method to delete a "project" record in the database, and all the "option items" associated with it.

public bool DeleteProjectById(int id)
{
    using (DbContext db = new DbContext(ConfigHelper.Instance().ConnectionString))
    {
        try
        {
            foreach (var entity in db.ProjectOptionItems.Where(o => o.ProjectId == id))
                db.ProjectOptionItems.DeleteObject(entity);

            db.SaveChanges();

            var project = db.Projects.SingleOrDefault(o => o.Id == id);
            db.Projects.DeleteObject(project);

            db.SaveChanges();
            return true;
        }
        catch (Exception e)
        {
            ErrorLoggingService.Log(this, e);
            return false;
        }
    }
}

This works fine. But if I comment out the first call to db.SaveChanges, which is by the way how I was hoping the code will be, this will result to an SQL exception (The DELETE statement conflicted with the REFERENCE constraint).

Having two calls to SaveChanges() works but surely this is not the way to do it. Please help. Thanks!

in your db if you set delete cascade, you dont need all foreach loop, this is the one possible solution. — AliRıza Adıyahşi, Mar 25 '13 at 08:20

score 5 · Accepted Answer · answered Mar 25 '13 at 08:24

5

Like AliRiza said, set it in your SQL server. Additionally use this in your DbContext class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProjectOptionItems>().WillCascadeOnDelete();
}

answered Mar 25 '13 at 08:24

Linus Caldwell

10,908
12
46
58

What is ProjectOptionItems? Asking for a reference but not telling me how to resolve. – nzondlo Nov 01 '13 at 14:05
@nzondlo: See the code of the original post. Ronalds object name was `ProjectOptionItem`. Replace it with the object you want to cascade. – Linus Caldwell Nov 01 '13 at 17:29
Thanks, but am using Entity 4.x in VS2010, I don't think I have the option to use it. Was also going to use a stored proc now to handle deletes (that's another story) – nzondlo Nov 04 '13 at 04:22
@nzondlo: Of course you have the option. But additionally you must set the cascade for the SQL database to get things working. Configuring the model is important if you let Entity Framework create your database. – Linus Caldwell Nov 04 '13 at 10:34
I used Code First. Database was already there. – nzondlo Nov 04 '13 at 15:27

In Entity Framework, how to delete record with foreign key constraint?

1 Answers1