Wednesday, September 05, 2007

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:

• 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:

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.

Workarounds

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

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.

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

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

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.

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.

The dual Web role application has been running in Microsoft's South Central US (San Antonio) data center since September 2009. I believe it is the oldest continuously running Windows Azure application.

About Me

I'm a Windows Azure Insider, a retired Windows Azure MVP, the principal developer for OakLeaf Systems and the author of 30+ books on Microsoft software. The books have more than 1.25 million English copies in print and have been translated into 20+ languages.

Full disclosure: I make part of my livelihood by writing about Microsoft products in books and for magazines. I regularly receive free evaluation software from Microsoft and press credentials for Microsoft Tech•Ed and PDC. I'm also a member of the Microsoft Partner Network.