C# Entity Framework Truncate fails because of foreign key

Viewed 26

I have a C# MVC application where I want to give the user the ability to remove all records from a database table. However, because of a foreign key in a second table, the truncate fails.

This is the code I am trying to execute:

 passedDB.Database.ExecuteSqlCommand("TRUNCATE TABLE Boxes");

This is the model for class Box:

public class Box
{
    [Key]
    public int BoxId { get; set; }
    public string BoxDescription { get; set; }
    public int RoomNumber { get; set; }
    public string RoomName { get; set; }
    public List<Item> Items { get; set; }
}

And this is the model for the class Items:

public class Item
{
    [Key]
    public int BoxID {get; set;}
    public string ItemName { get; set; }
    public int Quantity { get; set; }

}

And this is what I have in my database context file:

public class DatabaseContext : DbContext
{
    public DatabaseContext() : base("DatabaseContext") 
    {
    }

    public DbSet<Box> Box { get; set; }
    public DbSet<Rooms> Rooms { get; set; }
    public DbSet<Item> Item { get; set; }

}

When Entity Framework build the tables the first time I ran the application, it created a foreign key in the Items table linking to the Boxes table, which is what I expected.

Now, I need to allow the user to delete all the records from the Boxes table but when execute the Truncate command, I get the following error:

 System.Data.SqlClient.SqlException
 HResult=0x80131904
 Message=Cannot truncate table 'Boxes' because it is being referenced by a FOREIGN KEY constraint.
 Source=.Net SqlClient Data Provider
 StackTrace:
 <Cannot evaluate the exception stack trace>

I'm not sure how to set this up to allow the deleting of all the records in the Boxes table and the associated records in the Items table.

Thanks in advance!

0 Answers
Related