Introduction
This article will discuss alternative methods for performing cascading deletes using LINQ to SQL. Cascading delete refers to the action of removing records associated by a foreign key relationship to a record that is the target of a deletion action. LINQ to SQL does not specifically handle cascading deletes and 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 go about accomplishing the cascading delete.
Problem
The problem with performing a cascading delete is not new to LINQ to SQL and one has essentially the the same alternatives for performing such a delete. The issue is one of 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 in turn maintains a foreign key relationship with the Order_Details table). In order to delete a customer which has associated Orders, one needs to dispose of or otherwise handle the associated records in both the Orders and Order_Details tables. In the LINQ to SQL jargon, the associated tables are referred to as entity sets.
LINQ to SQL will not violate the foreign key relationships and if an application attempts to delete a record with such relationships in place, the executing code will throw an exception. Using the Northwind example, if one were to attempt to delete a customer with associated orders, an exception will occur. That is not really a problem, that is how it should be, otherwise, why have foreign key relationships at all. The issue is really one of determining if you would really want to delete records with associated entity sets, and if you do, how would you want 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 you can handle 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 an error and no changes would be made to the database:
Figure 2: Conflict Error Message
Solution Alternative 1 - 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 to doing that:
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 looking at this example, to delete the customer along with the related orders and order details, the code first selects the matching customer by the customer ID field (it's primary key). Once a match is found, the code loops through the orders related to each customer and marks them for deletion using the DeleteOnSubmit call.
Further, since another relationship exists between the order and order details, the code loops through all of the order details associated with the order and marks them for deletion as well. Lastly, the customer itself if marked for deletion and then Submit Changes is called on the data context. 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 upon the configuration of the foreign keys.
Solution Alternative 2 - 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 need 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 merely open the diagram, 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, if we were to set all of the constraint delete rules to cascade we could delete a customer with this bit of code:
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);
}