LINQ to SQL and One-To-Many Relationships

Recently a customer sent me a question related to the video I posted on creating a one-to-many form with LINQ to SQL. In that video I use the Northwind database to set up a one-to-many association between the Customers and Orders entities. We all love to use Northwind in demos because developers tend to have this ancient sample database lying around, but sometimes I forget how unrealistic some of the design in there really is, i.e. nullable foreign keys everywhere. All the foreign keys in the child tables like CustomerID in the Orders table and CategoryID in the Product table are nullable which means that the database will allow the child rows to be orphaned.

Typically those types of relationships are set up with non-nullable foreign keys (FK) because the child rows are required to belong to a parent -- meaning the database will keep the integrity of the data intact. Allowing nulls on a FK is usually only used for lookup-style relationships when the lookup value is not required. Setting the FK as not allowing null values and indicating the relationship between the tables also means you can't delete the parent without deleting the children first (unless you specifically set the relationship's delete rule to "Cascade"). Here's a more typical example with Customers and Orders tables, a non-nullable FK, and a relationship set up between them to enforce the data integrity:

Now when working with the O/R Designer and LINQ to SQL in this scenario this common relationship causes the designer to infer the delete behavior of the child incorrectly (if your relationship's delete rule is set to the default "No Action" and your FK does not allow nulls). So when you try to delete the child object which contains a non-nullable foreign key, an error will occur when you try to call SubmitChanges() on the DataContext. For instance, if we bind the LINQ to SQL classes to two grids, delete just the child Order row, and attempt to call SubmitChanges(), we get the following error "An attempt was made to remove a relationship between a Customer and a Order. However, one of the relationship's foreign keys (Order.CustomerID) cannot be set to null." (This error comes from the ChangeTracker called by the DataContext, never making it to the database.)

This behavior may not obvious when using the O\R designer and drag-dropping your database tables onto the design surface to create the entities. Let's look a little bit deeper into the association between our entities. When we drag the Customers and Orders table we defined above from the Server Explorer onto the O/R designer, the following entities and association is automatically created for us:

This model looks like this XML contained in the .dbml file if you look at it in a text editor. (Note: Right-click on the dbml file in the Solution Explorer and select Open With --> XML Editor) Notice the association element under the Order entity.

In order to be able to delete a child row independently in the database when calling SubmitChanges(), we must indicate DeleteOnNull="true" on this association. Unfortunately the only way to change this is to drop out of the designer and make the change manually in the .dbml file. Luckily, however, you can switch back to the designer and it will not remove this attribute if you continue to modify the model as long as you don't remove the entities entirely. Once we make this change we can now delete just a single Order from the grid and save normally:

<Association Name="Customer_Order" Member="Customer" ThisKey="CustomerID" Type="Customer" IsForeignKey="true" DeleteOnNull="true"/>

The other option to fix this issue is to modify the Delete Rule to "Cascade" on the relationship in the database. In that case the designer correctly infers this attribute on the association. This may be a better solution if you want to always automatically delete the related orders when a customer is deleted in your database no matter what application is working against the data. Additionally, if you apply the Cascade delete rule on your database relationship, then you will not have to manually delete the children first every time the parent is deleted when working with the DataContext. (Note: To enable cascading deletes, just right click on the parent table in the Server Explorer, select "Open Table Definition", right-click on any column and select "Relationships", select the relation and expand the "INSERT and UPDATE Specification" then for the Delete Rule set it to CASCADE.)

However, sometimes you don't have the ability to modify the database you're working with. In our example we can open the DataContext partial class and write some code in the DeleteCustomer partial method so that anytime we delete a customer, the orders will be deleted first. (Note: You'll be able to right-click on the O/R designer's surface and select "View Code" at RTM to access the partial class code, but if you're playing with Beta 2 you'll have to create the partial class file manually).

Partial Class MyDataDataContext

    Private Sub DeleteCustomer(ByVal instance As Customer)

        For Each o In instance.Orders

            'Always delete the orders before the customer is deleted.

            'This means that the database relationship's delete rule

            ' does not need to be modified to CASCADE.

            Me.ExecuteDynamicDelete(o)

        Next

        Me.ExecuteDynamicDelete(instance)

    End Sub

End Class

I've attached the example above which works with VS 2008 Beta 2 to illustrate the point. In general, LINQ to SQL is a very clean way of accessing your data as true objects in your application and I especially like how it handles changes and transactions automatically for you in the background. You just need to be aware of the types of associations it infers from your schema and drop away from the designer to enable these scenarios.

Enjoy!