通过

您可以使用 LINQ to SQL 执行哪些操作

LINQ to SQL 支持作为 SQL 开发人员所期望的所有关键功能。 可以查询信息，以及插入、更新和删除表中的信息。

选择

只需使用自己的编程语言编写 LINQ 查询，然后执行该查询来检索结果，即可选择（投影）。 LINQ to SQL 本身会将所有必要的操作转换为您熟悉的必要的 SQL 操作。 有关详细信息，请参阅 LINQ to SQL。

在以下示例中，检索伦敦客户的公司名称并在控制台窗口中显示。

// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@"northwnd.mdf");
// or, if you are not using SQL Server Express
// Northwnd nw = new Northwnd("Database=Northwind;Server=server_name;Integrated Security=SSPI");

var companyNameQuery =
    from cust in nw.Customers
    where cust.City == "London"
    select cust.CompanyName;

foreach (var customer in companyNameQuery)
{
    Console.WriteLine(customer);
}

' Northwnd inherits from System.Data.Linq.DataContext.
Dim nw As New Northwnd("c:\northwnd.mdf")
' or, if you are not using SQL Server Express
' Dim nw As New Northwnd("Database=Northwind;Server=dschwart7;Integrated Security=SSPI")

Dim companyNameQuery = _
    From cust In nw.Customers _
    Where cust.City = "London" _
    Select cust.CompanyName

For Each customer In companyNameQuery
    Console.WriteLine(customer)
Next

插入

要执行 SQL Insert，只需将对象添加到您已创建的对象模型中，然后在 SubmitChanges 上调用 DataContext。

在以下示例中，将通过使用 Customers 将新客户及客户的信息添加到 InsertOnSubmit 表中。

// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@"northwnd.mdf");

Customer cust = new Customer();
cust.CompanyName = "SomeCompany";
cust.City = "London";
cust.CustomerID = "98128";
cust.PostalCode = "55555";
cust.Phone = "555-555-5555";
nw.Customers.InsertOnSubmit(cust);

// At this point, the new Customer object is added in the object model.
// In LINQ to SQL, the change is not sent to the database until
// SubmitChanges is called.
nw.SubmitChanges();

' Northwnd inherits from System.Data.Linq.DataContext.
Dim nw As New Northwnd("c:\northwnd.mdf")

Dim cust As New Customer With {.CompanyName = "SomeCompany", _
    .City = "London", _
    .CustomerID = 98128, _
    .PostalCode = 55555, .Phone = "555-555-5555"}
nw.Customers.InsertOnSubmit(cust)
' At this point, the new Customer object is added in the object model.
' In LINQ to SQL, the change is not sent to the database until
' SubmitChanges is called.
nw.SubmitChanges()

更新

要对 Update 数据库条目进行编辑，首先检索项目，然后直接在对象模型中修改它。 修改对象后，调用SubmitChangesDataContext以更新数据库。

在下面的示例中，检索来自伦敦的所有客户。 然后，城市的名称从“伦敦”更改为“伦敦 - 地铁”。 最后，调用 SubmitChanges 将修改发送到数据库。

Northwnd nw = new Northwnd(@"northwnd.mdf");

var cityNameQuery =
    from cust in nw.Customers
    where cust.City.Contains("London")
    select cust;

foreach (var customer in cityNameQuery)
{
    if (customer.City == "London")
    {
        customer.City = "London - Metro";
    }
}
nw.SubmitChanges();

Dim nw As New Northwnd("c:\northwnd.mdf")
Dim cityNameQuery = _
    From cust In nw.Customers _
    Where cust.City.Contains("London") _
    Select cust

For Each customer In cityNameQuery
    If customer.City = "London" Then
        customer.City = "London - Metro"
    End If
Next
nw.SubmitChanges()

正在删除

要Delete一个项目，请先从其所属的集合中删除该项目，然后在SubmitChanges上调用DataContext来提交更改。

注释

LINQ to SQL 无法识别级联删除作。 如果要删除表中具有约束的行，请参阅 “如何：从数据库中删除行”。

在下面的示例中，从数据库中检索 CustomerID 为 98128 的客户。 然后，在确认客户行已被检索后，将调用 DeleteOnSubmit 以从集合中删除该对象。 最后，调用SubmitChanges将删除操作转发到数据库。

Northwnd nw = new Northwnd(@"northwnd.mdf");
var deleteIndivCust =
    from cust in nw.Customers
    where cust.CustomerID == "98128"
    select cust;

if (deleteIndivCust.Count() > 0)
{
    nw.Customers.DeleteOnSubmit(deleteIndivCust.First());
    nw.SubmitChanges();
}

Dim nw As New Northwnd("c:\northwnd.mdf")
Dim deleteIndivCust = _
    From cust In nw.Customers _
    Where cust.CustomerID = 98128 _
    Select cust

If deleteIndivCust.Count > 0 Then
    nw.Customers.DeleteOnSubmit(deleteIndivCust.First)
    nw.SubmitChanges()
End If

另请参阅