Cascading Deletes in LINQ to SQL with Visual Basic

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?

Northwind- Database-in-VB.NET.gif

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

Dim c As 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"

Dim od As New Order_Detail()

od.Discount = 0.25F

od.ProductID = 1

od.Quantity = 25

od.UnitPrice = 25.0

Dim o As New Order()

o.Order_Details.Add(od)

o.Freight = 25.5

o.EmployeeID = 1

o.CustomerID = "AAAAA"

c.Orders.Add(o)

Dim dc As New NWindDataContext()

dc.Customers.InsertOnSubmit(c)

dc.SubmitChanges()

UpdateDataGrid()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

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

Try

Dim dc = New NWindDataContext()

Dim q = _

(From c In dc.GetTable(Of Customer)() _

Where c.CustomerID = "AAAAA" _

Select c).SingleOrDefault()

dc.Customers.DeleteOnSubmit(q)

dc.SubmitChanges()

UpdateDataGrid()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

It would result an error and no changes would be made to the database:

Conflict-Error- Message-in-VB.NET.gif

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

Dim dc = New NWindDataContext()

Dim q = _

(From c In dc.GetTable(Of Customer)() _

Where c.CustomerID = "AAAAA" _

Select c).SingleOrDefault()

Dim ord As New Order()

For Each ord In q.Orders

dc.Orders.DeleteOnSubmit(ord)

Dim od As Order_Detail

For Each od In ord.Order_Details

dc.Order_Details.DeleteOnSubmit(od)

dc.Customers.DeleteOnSubmit(q)

dc.SubmitChanges()

UpdateDataGrid()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

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.

Setting-the- Delete-Rule-in-VB.NET.gif

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

Dim dc = New NWindDataContext()

Dim q = _

(From c In dc.GetTable(Of Customer)() _

Where c.CustomerID = "AAAAA" _

Select c).SingleOrDefault()

dc.Customers.DeleteOnSubmit(q)

dc.SubmitChanges()

UpdateDataGrid()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

As you can see in this example code, there was not 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 results in the deletion of the relation records contained in the Order and Order Details tables.

Solution Alternative 3 - 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 set the delete rule to "Set Default". Either approach could be useful if there was a need to delete (in this example) a customer record but retain the order and order detail records. Either approach could be handled in a manner similar to that used in the previous solution alternative. Setting the foreign key value to nullable is probably not advisable but it is a workable alternative.

Solution Alternative 4 - Handling the Cascading Delete With a Stored Procedure

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

Dim dc as 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 an action from within LINQ to SQL.