LINQ to SQL: Deleting Entities in Memory

In my previous post, I demonstrated how to use DeleteOnNull in LINQ to SQL database markup language (dmbl) association definitions in order to delete relational objects. However, DeleteOnNull is not the complete solution when working with relational objects in memory. The following example will better explain the situation where DeleteOnNull does not work.

In my database, I have an OrderDetail table that has a One-to-Many relationship with a Shipments table. Both of the business objects were generated using the Visual Studio 2008 dbml designer. In addition, I have the DeleteOnNull attribute for the association between the tables set to true.

Based upon the products in a given Order, my system will create appropriate Shipment objects. An example of constructing a shipment object and adding it directly to an OrderDetail is shown below:

1 OrderDetail od = new OrderDetail();

2

3 Shipment ship = new Shipment();

4

5 ship.Cost = 20.00M;

6 ship.Weight = 10.00M;

7 ship.TrackingNumber = "ABCD123456789";

8

9 od.Shipments.Add(ship);

In my application, the shipment objects are generated automatically depending on the products in the customer's shopping cart. Once the Shipment objects have been created, the user managing the order has the option to custom configure how many boxes and their weights should be shipped in order to reduce cost. The Shipment object is added directly to the OrderDetail's shipment collection.

The list of shipment objects were bound to a DataGridView with editing and deleting enabled. The DataSource of the DataGrid was set to od.Shipments.ToList(). However, I quickly discovered that despite the DeleteOnNull attribute being set to true for the association between OrderDetails and Shipments, attempting to delete a Shipment throws an InvalidOperationException with the following message:

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

After doing some research on the msdn forums, I discovered that the DeleteOnNull attribute does not work for items created in memory that are not attached directly to an Entity's source table. The ultimate solution in this case was to use the DataGridView's UserDeletingRow event with the following code:

1 private void shipmentDataGridView_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)

2 {

3 Shipment temp = e.Row.DataBoundItem as Shipment;

4 _context.Shipments.Attach(temp);

5 _context.Shipments.DeleteOnSubmit(temp);

6 }

_context is the DataContext on which the Shipment object was originally created. By attaching the Shipment entity directly to the Shipments table and calling DeleteOnSubmit on the attached Shipment, the DataContext is able to successfully delete the Shipment object.