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
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:
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)
Next
Next
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.
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.