Wednesday, October 1, 2008

LINQ to SQL: DeleteOnNull

My first project at Catalyst just wrapped up this past week, and it involved creating an order fulfillment system using Microsoft .Net 3.5 WinForms technology.

For our data layer, we went ahead and used LINQ to SQL to interact with an installation of Microsoft SQL Server. Most of our business objects were generated via the designer for our DataContext.dbml file in Microsoft Visual Studio 2008. We decided to use LINQ to SQL with hopes of speeding up the development cycle. For the most part, LINQ to SQL fit our needs very well. However there were a few troublesome issues that I would like to see addressed in some form in future releases.

One of the issues I ran into several times while creating business object, was how to delete entities with associations in a relational database. When attempting to delete an entity that contains an association with another object in the database, by default the DataContext throws an InvalidOperationException, with a message similar to the following:

"An attempt was made to remove a relationship between a OrderDetail and a OrderLineItem. However, one of the relationship's foreign keys (OrderLineItem.OrderDetailId) cannot be set to null."

The relationship between an OrderDetail and OrderLineItem is one to many. By default, the DataContext attempts to set the OrderLineItem.OrderDetailId to null. However, we want the object to be removed from the database completely.

The solution to the issue is to set the Association's DeleteOnNull attribute to true. Unfortunately, this attribute cannot be set within the designer. Instead, the attribute must be added by hand within the DataContext.cs.designer file. An example if this implementation is below:

[Association(Name="OrderDetail_OrderLineItem", Storage="_OrderDetail", ThisKey="OrderDetailId", OtherKey="OrderDetailId", IsForeignKey=true, DeleteOnNull=true)]

Further explanations and examples of this issue can be found on Beth Massi's blog and Dinesh Kulkarni's blog.

1 comment:

  1. Instead of modifying the designer-generated code, you can instead just open the DBML as XML (by right-clicking and choosing Open With... -> Xml Editor) and add the DeleteOnNull attribute to the appropriate association's config. This way you will not lose your changes everytime you regenerate the code.