Custom Delete For LinqDataSource and DetailsView or GridView

Recently I have been working on an ASP.NET web application. It reads data out of a database, does stuff with it, and writes out some files. One of the required features is the ability to delete records from the database. For this application, I have a DropDownList for selecting an object (where the Value on the DropDownList is the primary key of the object, and the Text is the name of the object) and a DetailsView where the fields can be displayed and edited. The data source for the DetailsView is a LinqDataSource, based on a data context defined in a .dbml file.

To enable deleting, click the quick tag (the little ">" icon on the corner of the widget on the aspx page) on the LinqDataSource and choose Enable Delete. Then click the quick tag of the DetailsView and choose Enable Deleting. A new row will be added to the DetailsView containing a link for deleting.

That is all well and good, and for a very simple database schema, that will be sufficient for deletes to work correctly. The LinqDataSource will use the primary key of the record to delete the record from the database: behind the scenes it generates SQL that is something like "DELETE FROM recordtable WHERE id=@id". This will fail, however, if the record has child records in another table, because the foreign key constraint on the other table will prevent the original record from being deleted. For better or worse, Linq swallows the error and fails silently.

The LinqDataSource does not have a straightforward way to customize the DELETE command, although other data sources such as SqlDataSource do have that. I wanted to use LinqDataSource, and after trying many solutions, I found one that worked: write a simple stored procedure to do the delete, and call that.

1. Create a stored procedure in the database for deleting the record. As an example, it might look like this:
   

   CREATE PROCEDURE [dbo].[DELETE_RECORD]
   
    @RECORD_ID INT
   
   AS
   
   BEGIN
   
    DELETE FROM RECORDCHILDTABLE WHERE RECORDID = @RECORD_ID
   
    DELETE FROM RECORDTABLE WHERE ID = @RECORD_ID
   
   END

   
   
   You can see that I simply delete the child records first, then delete the master record. My schema is still pretty simple, having only this one relationship to worry about. A more complicated schema would require a more complicated stored procedure.



2. Open the .dbml data context file, and find the stored procedure in the Server Explorer pane. Drag the stored procedure from the Server Explorer to the data context pane (the main part of the window displaying the tables in your .dbml file) and it appears in that pane, in a split pane. The stored procedure is now part of the data context.



3. In the properties for the main record table (not the child record table), change the Delete property from Use Runtime to use the new stored procedure.
   
   • In the properties pane, click on the "..." button next to the value for the Delete property, and a Configure Behavior window will open.
   • Select your object class and choose "Delete" from the Behavior dropdown menu.
   • Select the Customize radio button, select the stored procedure from the dropdown menu, and choose the primary key of the record as a method parameter.



4. The LINQ TO SQL data context now knows to use the stored procedure when trying to delete that record. The LinqDataSource in the .aspx page will follow this behavior, and should now safely and successfully delete the records.

I have a personal bias against stored procedures because I prefer to implement data layers as part of the application, not as part of the database. Thus, this is a less optimal solution than to teach LINQ itself how to delete the record. Nevertheless, it's pretty good, and it works.