How to: Set Up Cascade Delete With Multi-Table Entities

Deleting objects from database tables mapped to a multi-table entity, by default, results in a DELETE statement against the main table and multiple DELETE statements against those that hold properties of the entity. In cases when the entity is separated between a lot of tables, this may cause a low performance rate of the application.

Telerik Data Access helps you to solve this issue while you create the fluent model by allowing you to set the delete rule of the constraints between the tables to CASCADE. After the database is synchronized with this setting, Telerik Data Access will issue DELETE statements only against the main table and will rely on the backend to delete the related records from the other tables.

Telerik Data Access offers two ways to control the usage of cascade delete in fluent models that have multi-table entities structures:

• Cascade Delete on Fluent Model Level
• Cascade Delete on Class Level

The usage of cascade delete varies according to the settings on both model and class level as follows:

Cascade Delete on Fluent Model Level

The usage of CASCADE delete for the whole fluent model is enabled and disabled through the AllowCascadeDelete property of the RuntimeConfiguration class.

The workflow for enabling it includes the next steps:

1. In the backend configuration used by the context, set AllowCascadeDelete property to True. For example:

   public static BackendConfiguration GetBackendConfiguration()
   {
       BackendConfiguration backend = new BackendConfiguration();
       backend.Runtime.AllowCascadeDelete = true;
       backend.Backend = "MsSql";
       backend.ProviderName = "System.Data.SqlClient";
       return backend;
   }
   

   Public Shared Function GetBackendConfiguration() As BackendConfiguration
       Dim backend As BackendConfiguration = New BackendConfiguration()
       backend.Runtime.AllowCascadeDelete = True
       backend.Backend = "MsSql"
       backend.ProviderName = "System.Data.SqlClient"
       Return backend
   End Function
   

2. With the help of the UpdateSchema() method, migrate the fluent model to the database. If you look through the script this method produces, you will notice that all of the constraints between the tables which form the entity are (re)created to allow cascade delete. For example:

   ALTER TABLE [Categories] 
   ADD CONSTRAINT [ref_Categories_Products] 
   FOREIGN KEY ([CategoryID]) 
   REFERENCES [Products]([Id]) 
   ON DELETE CASCADE
   go
   ALTER TABLE [Orders] 
   ADD CONSTRAINT [ref_Orders_Products] 
   FOREIGN KEY ([OrderID]) 
   REFERENCES [Products] ([Id]) 
   ON DELETE CASCADE
   go
   

3. If you attempt to delete the first object of the entity, you will notice that Telerik Data Access issues a DELETE statement only against the Products table:

   using (FluentModel dbContext = new FluentModel())
   {
       Product firstProduct = dbContext.Products.FirstOrDefault();
       dbContext.Delete(firstProduct);
       dbContext.SaveChanges();
   }
   

   Using dbContext As New FluentModel()
       Dim firstProduct As Product = dbContext.Products.FirstOrDefault()
       dbContext.Delete(firstProduct)
       dbContext.SaveChanges()
   End Using
   

The SQL generated would be similar to the following:

```SQL
DELETE FROM [Products] WHERE [Id] = @p0
```

The value of AllowCascadeDelete will influence the constraints between the tables that form the multi-table entity and between tables participating in vertical inheritance only.

Cascade Delete on Class Level

Since a fluent model can contain a variety of entities connected with one another in different ways, you may need to control the usage of CASCADE delete only for a particular multi-table entity.

The workflow for enabling it includes the following steps:

1. In the mapping configuration of the entity, add a call to the WithCascadeDelete() method (or to WithCascadeDelete(CascadeDeleteOption.Yes)):

   configuration.MapType().WithCascadeDelete(CascadeDeleteOption.Yes).ToTable("Products");
   

   configuration.MapType().WithCascadeDelete(CascadeDeleteOption.Yes).ToTable("Products")
   

2. In scenarios when you do not need to use cascade delete on fluent model level, set the AllowCascadeDelete to False:

       backend.Runtime.AllowCascadeDelete = false;
   

       backend.Runtime.AllowCascadeDelete = False
   

3. With the help of the UpdateSchema() method, migrate the fluent model to the database. If you look through the script this method produces, you will notice that all of the constraints between the tables are (re)created to allow cascade delete. For example:

   ALTER TABLE [Categories] 
   ADD CONSTRAINT [ref_Categories_Products] 
   FOREIGN KEY ([CategoryID]) 
   REFERENCES [Products] ([Id]) 
   ON DELETE CASCADE
   go
   ALTER TABLE [Orders] 
   ADD CONSTRAINT [ref_Orders_Products] 
   FOREIGN KEY ([OrderID]) 
   REFERENCES [Products] ([Id]) 
   ON DELETE CASCADE
   go
   

4. You can test the removal of the objects from the multi-table entity structure as shown here.

Choosing CascadeDeleteOption.No will explicitly disable the usage of cascade delete for the particular multi-table entity. CascadeDeleteOption.Default will respect the value of the AllowCascadeDelte property of the runtime configuration.