LINQ to SQL Tips 4: Use DeleteOnNull if you want to delete object with null FK

  • Article

I often get a question along the following lines:

If I remove one of the OderDetails from Order.OrderDetails collection, I see that the reference OrderDetail.Order is set to null but this just orphans the OrderDetail; it is not deleted. How can I change that?

Here is how:

This is not exposed in the designer so you will need to change the generated code (ouch!). In the Association attribute, set the DeleteOnNull property to true as follows:

Once this is set, either of the following operations

ord.Order_Details.Remove(od); // ord is Order; od is OrderDetail

od.Order = null;

will result in the following being inferred:

// db is an instance of the strongly typed DataContextdb.Order_Details.DeleteOnSubmit(od);

Please note that this is not the same as cascade delete. Cascade delete is what you specify on your foreign key in the database - for all apps. LINQ to SQL deliberately does not take over this database role. What it does is provide a shorthand notation to say that a particular type is the target of cascade delete constraint in the database (not the source) and hence can be cleaned up when the nullable foreign key is set to null. LINQ to SQL does not by itself provide cascade delete behavior.