Cascading Deletes in LINQ to SQL

Introduction

This article will discuss alternative methods for cascading deletes using LINQ to SQL. Cascading delete refers to removing records associated with a foreign key relationship to a record that is the target of a deletion action. LINQ to SQL does not explicitly handle cascading deletes; it is up to the developer to determine whether or not that action is desired. It is also up to the developer to determine how to accomplish the cascading delete.

Problem

The problem with performing a cascading delete is not new to LINQ to SQL, and one has essentially the same alternatives for performing such a delete. The issue is determining how to handle the deletion or retention of records associated with a record targeted for deletion where that record maintains a foreign key relationship with records contained within other tables within the database and, more specifically, where the foreign key fields are not nullable.

As an example, consider the customer table within the Northwind database. The customer table has a foreign key relationship established with the Orders table (which maintains a foreign key relationship with the Order_Details table). To delete a customer with associated Orders, one needs to dispose of or otherwise handle the associated records in both the Orders and Order_Details tables. The related tables are called entity sets in the LINQ to SQL jargon.

LINQ to SQL will not violate the foreign key relationships. If an application attempts to delete a record with such relationships in place, the executing code will throw an exception.

Using the Northwind example, an exception would occur if one attempts to delete a customer with associated orders. That is not a problem; that is how it should be; otherwise, why have foreign key relationships at all? The issue is determining if you want to delete records with associated entity sets. If you do, how would you like to handle it - do you want to keep the associated records or delete them right along with the targeted record?

Figure 1. Customers, Orders, and Order Details - Northwind Database

Solution

There are several possible alternatives at your disposal. You can handle the cascading deletes using LINQ to SQL from within your code or the foreign key relationships from within SQL Server.

If you were to execute this code against the Northwind database, it would create a customer with an associated order and order details.

try  
{  
          Customer c = new Customer();  
          c.CustomerID = "AAAAA";  
          c.Address = "554 Westwind Avenue";  
          c.City = "Wichita";  
          c.CompanyName = "Holy Toledo";  
          c.ContactName = "Frederick Flintstone";  
          c.ContactTitle = "Boss";  
          c.Country = "USA";  
          c.Fax = "316-335-5933";  
          c.Phone = "316-225-4934";  
          c.PostalCode = "67214";  
          c.Region = "EA";  
   
          Order_Detail od = new Order_Detail();  
          od.Discount = .25f;  
          od.ProductID = 1;  
          od.Quantity = 25;  
          od.UnitPrice = 25.00M;  
   
          Order o = new Order();  
          o.Order_Details.Add(od);  
          o.Freight = 25.50M;  
          o.EmployeeID = 1;  
          o.CustomerID = "AAAAA";  
   
          c.Orders.Add(o);  
   
          using (NWindDataContext dc = new NWindDataContext())  
          {  
                   var table = dc.GetTable<Customer>();  
                   table.InsertOnSubmit(c);  
                   dc.SubmitChanges();  
          }  
}  
catch (Exception ex)  
{  
          MessageBox.Show(ex.Message);  
}

But if you then tried to delete the customer without handling the entity sets using something like this.

using (NWindDataContext dc = new NWindDataContext())  
{  
   
          var q =  
                   (from c in dc.GetTable<Customer>()  
                   where c.CustomerID == "AAAAA"  
                   select c).Single<Customer>();  
   
                   dc.GetTable<Customer>().DeleteOnSubmit(q);  
                   dc.SubmitChanges();  
}

It would result in an error, and no changes would be made to the database.

Figure 2. Conflict Error Message

Handling the Delete with LINQ to SQL

You can handle the cascading deletes manually, deleting all of the related entities in the associated entity set; here is a simple approach.

try  
{  
          using (NWindDataContext dc = new NWindDataContext())  
          {  
   
                   var q =  
                   (from c in dc.GetTable<Customer>()  
                   where c.CustomerID == "AAAAA"  
                   select c).Single<Customer>();  
   
                   foreach (Order ord in q.Orders)  
                   {  
                             dc.GetTable<Order>().DeleteOnSubmit(ord);  
   
                             foreach (Order_Detail od in ord.Order_Details)  
                             {  
                                       dc.GetTable<Order_Detail>().DeleteOnSubmit(od);  
                             }  
                    }  
                    dc.GetTable<Customer>().DeleteOnSubmit(q);  
                    dc.SubmitChanges();  
          }  
          UpdateDataGrid();  
}  
catch (Exception ex)  
{  
          MessageBox.Show(ex.Message);  
}

In this example, the code first selects the matching customer by the customer ID field (the primary key) to delete the customer along with the related orders and order details. Once a match is found, the code loops through the orders related to each customer and marks them for deletion using the DeleteOnSubmit call.

The order in which the entities are marked for deletion does not matter; LINQ to SQL sorts that out during the execution of the Submit Changes call based on the configuration of the foreign keys. Further, since another relationship exists between the order and order details, the code loops through all the order details associated with the order and marks them for deletion. Lastly, the customer is marked for deletion, and Submit Changes is called on the data context.

Handling the Cascading Delete from within SQL Server

It is possible to manage the cascading deletes entirely from within SQL Server. To do this, one needs only set the delete rule for the foreign key relationship to cascade.

Figure 3. Setting the Delete Rule

If you have a database diagram built, the easiest way to set the delete rule is to open the diagram merely, click on the foreign key relationship within the diagram, and then open the INSERT and UPDATE property to expose the Delete Rule property, and then set the Delete Rule property to Cascade as shown in Figure 3.

To repeat the example of deleting a customer with related orders, we could delete a customer with this bit of code if we set all of the constraints to delete rules to cascade.

try  
{  
          using (NWindDataContext dc = new NWindDataContext())  
          {  
                   var q =  
                   (from c in dc.GetTable<Customer>()  
                   where c.CustomerID == "AAAAA"  
                   select c).Single<Customer>();  
   
                   dc.GetTable<Customer>().DeleteOnSubmit(q);  
                   dc.SubmitChanges();  
          }  
          UpdateDataGrid();  
}  
catch (Exception ex)  
{  
          MessageBox.Show(ex.Message);  
}

As you can see in this example code, there was no need to bother with marking each member of an entity set for deletion as, in this case, SQL Server was instructed on how to deal with a deletion of the customer or order records—as a result, deleting the customer also deleting the related records in the Order and Order Details tables.

Handling the Cascading Delete From within SQL Server

It is also possible to set the foreign key field in the entity sets to nullable and then set the delete rule for that field to "Set Null." One could also set a default value for the field and the delete rule to "Set Default."

Either approach could be helpful if there were a need to delete (in this example) a customer record but retain the order and order detail records. Either method could be handled like that used in the previous solution alternative. Setting the foreign key value to nullable is probably not advisable, but it is a workable alternative.

Handling the Cascading Delete With a Stored Procedure

One may create or add a stored procedure to accomplish the cascading delete and evoke that stored procedure using LINQ to SQL. Stored procedures added to the designer may be evoked directly from the data context; for example, if we had a stored procedure called DeleteCustomer that took the customer ID as an argument and handled the cascading deletes, we could do something like this:

Using(NwindDataContext dc = new NwindDataContext())  
{  
     dc.DeleteCustomer("AAAAA");  
}

Summary

Cascading deletes are not new to LINQ to SQL; it is the same issue it has always been. In this article, I have described a few approaches to dealing with cascading deletes from within the code and from the SQL Server side, but as is true with many things in .NET, there are several other ways to accomplish such action from within LINQ to SQL.