如何:从数据库中删除行 (LINQ to SQL)

更新:November 2007

可以通过将对应的 LINQ to SQL 对象从其与表相关的集合中删除来删除数据库中的行。LINQ to SQL 会将更改转换为相应的 SQL DELETE 命令。

LINQ to SQL 不支持且无法识别级联删除操作。如果要在对行有约束的表中删除行,则必须完成以下任务之一:

  • 在数据库的外键约束中设置 ON DELETE CASCADE 规则。

  • 使用自己的代码首先删除阻止删除父对象的子对象。

否则会引发异常。请参见本主题中后面的第二个代码示例。

说明:

您可以重写 Insert、Update 和 Delete 数据库操作的 LINQ to SQL 默认方法。有关更多信息,请参见自定义插入、更新和删除操作 (LINQ to SQL)

使用 Visual Studio 的开发人员可以使用 对象关系设计器 来开发用于实现相同目的的存储过程。对象关系设计器(O/R 设计器)
对象关系设计器(O/R 设计器)
对象关系设计器(O/R 设计器)

以下步骤假定您已通过有效的 DataContext 连接到 Northwind 数据库。有关更多信息,请参见如何:连接到数据库 (LINQ to SQL)

删除数据库中的行

  1. 查询数据库中要删除的行。

  2. 调用 DeleteOnSubmit 方法。

  3. 将更改提交到数据库。

示例

这第一个代码示例查询数据库中 11000 号订单的详细信息,将这些订单详细信息标记为删除,然后将这些更改提交到数据库。

' 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.
}

在第二个示例中,目的是删除订单(10250 号)。代码首先检查 OrderDetails 表以查看要删除的订单是否有子项。如果订单有子项,则首先将子项标为删除,然后将订单标为删除。DataContext 为实际删除设置正确的顺序,以使发送到数据库的删除命令遵守数据库约束。

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();

请参见

任务

如何:指定存储过程以执行更新、插入和删除(O/R 设计器)

其他资源

如何:管理更改冲突 (LINQ to SQL)

生成和提交数据更改 (LINQ to SQL)