Cascade Deletion Problem with LINQ to SQL Beta 2

LINQ to SQL Beta 2's graphical O/R Designer generates defective DBML files from tables that have foreign key constraints with cascading deletions specified by ON DELETE CASCADE. I encountered the issue when performing a series of tests to determine if cascading deletions behaved as expected in LINQ to SQL Beta 2. Other than the following problem, they do.

Symptoms of the Problem

One or more of the following problems will occur if you've specified cascading deletions for any associations of your object graph and a foreign key field allows nulls:

• Silent build failure with the following message in the Build window:

----- Build started: Project: ProjectName, Configuration: Debug Any CPU -----
O/R Designer validation failed for file: d:\Path\FileName.dbml
Error: The operation could not be completed. Unspecified error

• The following error message in the Error List window on attempting a build:

Error 9 DBML1055: The DeleteOnNull attribute of the Association element 'ConstraintName' can only be true for singleton association members mapped to non-nullable foreign key columns.

• The following message in the Error List window with or without the preceding message:

Build failed due to validation errors in d:\Path\FileName.dbml.  Open the file and resolve the issues in the Error List, then try rebuilding the project.

The last error message should suggest that you open the file in the XML Editor because you can't resolve the issue in the O/R Designer.

The culprit is a DeleteOnNull="true" attribute name/value pair in the DBML file's <Association Name="ConstraintName" ...> element for the table containing the foreign key field, as in the following for the Northwind Orders table:

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

Here's the story on the DeleteOnNull="true" attribute, according to a response to a question from Microsoft's Kathy Lu in the Project LINQ General Forum:

Linq to SQL supports Inferred Delete by actually deleting the [DependentObject] instance from the [database] table if you have the DeleteOnNull property of the Association attribute on the FK side set to true.  The DeleteOnNull property is set to true via the SqlMetal and Linq to SQL Designer if you have a cascading delete is set on the database.

Kathy says the behavior is a bug in this response to another question in the Forum about the problem :

Thank you for your feedback.  We are aware of this bug on Visual Studio 2008 Beta 2 and the product team is looking into this for appropriate triage and resolution.

However, I believe DeleteOnNull="true" behaves as expected. What's needed is more consistent and informative error reports and, perhaps, a message box that asks if you want to remove the attribute.

Note: Bob Beauchemin encountered the same problem when he attempted to use SQL Metal to create a DBML file from the sample Northwind.sdf SQL Server CE 3.5 file.

Workarounds

Following are the most obvious options to enable building the DBML file:

1. Set the NOT NULL constraint on foreign key fields for which cascading deletion is specified. Relational integrity requires a valid foreign key value, so such fields should contain no NULL values. This is likely to be the best solution for most applications.
2. Remove the DeleteOnNull="true" attribute from the <Association Name="ConstraintName" ...> element in the XML Editor. You don't need to be concerned about having to fix the file because the DBML file gets updated only when you add or delete an entity widget. (This is Kathy Lu's recommended workaround.)
3. Remove cascading deletions by selecting No Action as the Delete Rule in the mis-named UPDATE and INSERT Specification node of the Table Designer's Relationships window. Alternatively, remove ON DELETE CASCADE from the table-generation script and run ALTER TABLE. This is likely to be the least desirable option because of the amount of additional code required to delete dependent records.

Unexpected Behavior of the O/R Designer

1. The DBML file doesn't reflect the changes you make to table metadata until you remove the table widget that changed from the O/R Designer, save Designer changes, add the table from Server Explorer, and save the changes again.
2. Refreshing the changes for one table doesn't cause the Designer to refresh the DBML file for changes to other tables.

Not good, especially considering the new anonymous methods for partial class files provided by new Extensibility Method Definitions.

Technorati Tags: LINQ to SQL, LINQ, O/R Designer, DBML, Cascading Deletions