LINQ to SQL Delete Operation by Stored Procedure using C#

LINQ to SQL DataContext class generated by using O/R mapper designer tool provides the two ways to perform the delete operation for permanently removing the records from the database. The first way generates the Delete SQL command at runtime and deletes a record from the data table whereas the second way enables to attach the stored procedure for performing the delete operation. You can drag and drop the stored procedure created from the Data Explorer of Visual Studio Web Developer 2010 onto the designer area of O/R mapper tool displaying layout diagram of data context class. In this tutorial we have used the Northwind SQL database to perform the delete operation over the categories table by stored procedure using LINQ to SQL C# code.

Stored Procedure

CREATE PROCEDURE sproc_DeleteCategory
 @CategoryID   INT
AS
BEGIN
 DELETE FROM Categories WHERE CategoryID = @CategoryID
END

The above stored procedure has been used to delete a category from the categories table. After creating the above stored procedure you can follow the steps given below to attach and customize the way of delete operation using LINQ to SQL ORM:

1. In the Data Explorer, locate the delete stored procedure created above and drop it onto the designer area of O/R mapper as shown in the figure 1. This will add a new method on the designer surface with a name same as of the stored procedure. You can easily rename it by selecting the stored procedure on the designer area and pressing the F4 key. It will open the properties window where you can edit the name for the selected stored procedure. 

Figure 1

2. Now select the category entity class diagram in the designer area and press F4 to open its properties window. In the properties window locate the Delete property to attach the stored procedure added in the first step to the delete operation of category class as shown in the figure 2.

Figure 2

3. Click the [...] button that appears in the Delete property locator to open the configuration behavior of delete operation for category entity class as shown in the figure 3.

Figure 3

By default the configure behavior dialog box will show the "Use runtime" behavior option selected. Change the selection to "Customize" and select the Delete method for categories from the dropdown menu just below the "Customize" option as shown in the above figure.

4. When you will select the Delete method associated to the update operation for category entity class from the dropdown menu it will populate the method parameters and class properties below it automatically as shown in figure 4.

Figure 4

Press ok and save the changes made to data context class in the O/R designer. It will auto-generate the strongly typed method in the DataContext class that will call the stored procedure for deleting a category from the SQL database table.

C# Code Example for LINQ to SQL Delete Operation using Stored Procedure

DataClassesDataContext dc = new DataClassesDataContext();

Category category = (from c in dc.Categories
                        where c.CategoryID == 15
                        select c).First();

dc.Categories.DeleteOnSubmit(category);

dc.SubmitChanges();

The above C# code will call the stored procedure to delete a category in the underlying categories table of SQL Northwind database.

Also read the tutorial: LINQ to SQL Stored Procedures to Insert, Update and Delete using C# to learn how to implement create, update and delete functionality using LINQ with stored procedures.