How to: Delete Rows From the Database (LINQ to SQL)

You can delete rows in a database by removing the corresponding LINQ to SQL objects from their table-related collection. LINQ to SQL translates your changes to the appropriate SQL DELETE commands.

LINQ to SQL does not support or recognize cascade-delete operations. If you want to delete a row in a table that has constraints against it, you must complete either of the following tasks:

• Set the ON DELETE CASCADE rule in the foreign-key constraint in the database.

• Use your own code to first delete the child objects that prevent the parent object from being deleted.

Otherwise, an exception is thrown. See the second code example later in this topic.

The following steps assume that a valid DataContext connects you to the Northwind database. For more information, see How to: Connect to a Database (LINQ to SQL).

To delete a row in the database

1. Query the database for the row to be deleted.

2. Call the DeleteOnSubmit method.

3. Submit the change to the database.

Example

This first code example queries the database for order details that belong to Order #11000, marks these order details for deletion, and submits these changes to the database.

' Query the database for the rows to be deleted. 
Dim deleteOrderDetails = _
    From details In db.OrderDetails() _
    Where details.OrderID = 11000 _
    Select details

For Each detail As OrderDetail In deleteOrderDetails
    db.OrderDetails.DeleteOnSubmit(detail)
Next 

Try
    db.SubmitChanges()
Catch ex As Exception
    Console.WriteLine(ex)
    ' Provide for exceptions 
End Try

// Query the database for the rows to be deleted. 
var deleteOrderDetails =
    from details in db.OrderDetails
    where details.OrderID == 11000
    select details;

foreach (var detail in deleteOrderDetails)
{
    db.OrderDetails.DeleteOnSubmit(detail);
}

try
{
    db.SubmitChanges();
}
catch (Exception e)
{
    Console.WriteLine(e);
    // Provide for exceptions.
}

In this second example, the objective is to remove an order (#10250). The code first examines the OrderDetails table to see whether the order to be removed has children there. If the order has children, first the children and then the order are marked for removal. The DataContext puts the actual deletes in correct order so that delete commands sent to the database abide by the database constraints.

Dim db As New Northwnd("c:\northwnd.mdf")

db.Log = Console.Out
' Specify order to be removed from database. 
Dim reqOrder As Integer = 10252

' Fetch OrderDetails for requested order. 
Dim ordDetailQuery = _
From odq In db.OrderDetails _
Where odq.OrderID = reqOrder _
Select odq

For Each selectedDetail As OrderDetail In ordDetailQuery
    Console.WriteLine(selectedDetail.Product.ProductID)
    db.OrderDetails.DeleteOnSubmit(selectedDetail)
Next 

' Display progress.
Console.WriteLine("Detail section finished.")
Console.ReadLine()

' Determine from Detail collection whether parent exists. 
If ordDetailQuery.Any Then
    Console.WriteLine("The parent is present in the Orders collection.")
    ' Fetch order. 
    Try 
        Dim ordFetch = _
        (From ofetch In db.Orders _
        Where ofetch.OrderID = reqOrder _
        Select ofetch).First()

        db.Orders.DeleteOnSubmit(ordFetch)
        Console.WriteLine("{0} OrderID is marked for deletion.,", ordFetch.OrderID)

    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Console.ReadLine()
    End Try 

Else
    Console.WriteLine("There was no parent in the Orders collection.")

End If 


' Display progress.
Console.WriteLine("Order section finished.")
Console.ReadLine()

Try
    db.SubmitChanges()

Catch ex As Exception
    Console.WriteLine(ex.Message)
    Console.ReadLine()

End Try 

' Display progress.
Console.WriteLine("Submit finished.")
Console.ReadLine()

Northwnd db = new Northwnd(@"c:\northwnd.mdf");

db.Log = Console.Out;

// Specify order to be removed from database 
int reqOrder = 10250;

// Fetch OrderDetails for requested order. 
var ordDetailQuery =
    from odq in db.OrderDetails
    where odq.OrderID == reqOrder
    select odq;

foreach (var selectedDetail in ordDetailQuery)
{
    Console.WriteLine(selectedDetail.Product.ProductID);
    db.OrderDetails.DeleteOnSubmit(selectedDetail);
}

// Display progress.
Console.WriteLine("detail section finished.");
Console.ReadLine();

// Determine from Detail collection whether parent exists. 
if (ordDetailQuery.Any())
{
    Console.WriteLine("The parent is presesnt in the Orders collection.");
    // Fetch Order. 
    try
    {
        var ordFetch =
            (from ofetch in db.Orders
             where ofetch.OrderID == reqOrder
             select ofetch).First();
        db.Orders.DeleteOnSubmit(ordFetch);
        Console.WriteLine("{0} OrderID is marked for deletion.", ordFetch.OrderID);
    }
    catch (Exception e)
    {
        Console.WriteLine(e.Message);
        Console.ReadLine();
    }
}
else
{
    Console.WriteLine("There was no parent in the Orders collection.");
}


// Display progress.
Console.WriteLine("Order section finished.");
Console.ReadLine();

try
{
    db.SubmitChanges();
}
catch (Exception e)
{
    Console.WriteLine(e.Message);
    Console.ReadLine();
}

// Display progress.
Console.WriteLine("Submit finished.");
Console.ReadLine();

See Also

Tasks

How to: Assign Stored Procedures to Perform Updates, Inserts, and Deletes (O/R Designer)

Other Resources

How to: Manage Change Conflicts (LINQ to SQL)

Making and Submitting Data Changes (LINQ to SQL)