剖析 LINQ to SQL

引言

以下示例需要为 Northwind 数据库生成一个映射文件。

LINQ to SQL 解决了面向对象世界与关系数据库世界之间的不匹配问题。在应用程序中，我们以客户和订单等领域对象来处理。然而，在保存这些对象和解决编程世界与数据库世界之间的数据类型不匹配问题时，传统上需要开发人员负责编写代码将这些对象持久化到数据库并处理数据类型转换。LINQ to SQL 与其他市场上的 ORM 工具相比的优势在于，它不仅抽象了业务对象背后的数据库结构，还支持一种名为 LINQ 的查询语言。虽然 LINQ to SQL 提供了查询语言，但你仍然可以使用存储过程或动态 SQL 来编写查询。LINQ to SQL 将实体类映射到数据库表，将实体类属性映射到数据库表列。我们来看一个例子。

/// <summary>
/// Examples shows how to dynamically insert rows into Northwind database without

/// calling sumbit changes.
/// </summary>
public static void DynamicInsert()
{
    NorthWindDataContext db = new NorthWindDataContext();
    db.ExecuteCommand("insert into shippers(companyname,
        phone) values({0},{1})", "Test shipper", "(111)111-1111");
    var shipper = db.Shippers.Single(s =>

    s.CompanyName == "Test shipper");
    Console.WriteLine(shipper.ShipperID);
}

上面的例子展示了如何在不使用我们的 Shipper 对象的情况下直接向 Northwind 插入记录，然后使用我们的 Context 查询数据库以确认记录已插入。我们不仅可以插入，还可以使用动态 SQL 查询数据库，并直接获取我们的对象。这样做的好处是可以编写自己的 SQL，而无需处理 DataReader 到对象的转换问题，因为只要 SQL 返回的字段与对象上的属性匹配，DataContext 就会负责这一切。让我们来看一个例子。

public static void DynamicSql()
{
    NorthWindDataContext db = new NorthWindDataContext();
    IEnumerable<Category>

    categories = db.ExecuteQuery<Category>(
       "select categoryid,categoryName CatName from categories where categoryname = {0}",
       "Beverages");
    foreach (var cat in categories)
    {
        Console.WriteLine(cat.CategoryName);
    }
}

运行上面的查询时，您会注意到 CategoryName 将被设置为 null。在构造动态 SQL 时，列名需要与对象 (Category) 上的属性匹配。另请注意，我们并没有返回数据库中的所有字段，但也没有收到任何异常。原因是 LINQ to SQL 不会为未从 SQL 返回的属性设置任何值。然而，当你的动态 SQL 语句不包含定义为主键的列时，LINQ to SQL 会引发运行时异常，如下所示。

/// <summary>
/// this example demonstrates that if the dynamic sql statment does not have primary
/// key linq to sql will raise exception
/// </summary>

public static void DynamicSqlWithNoPrimaryKey()
{
    NorthWindDataContext db = new NorthWindDataContext();
    IEnumerable<Category> categories = db.ExecuteQuery<Category>(
        "select categoryName from categories where categoryname = {0}", "Beverages");
    foreach (var cat in categories)
    {
        Console.WriteLine(cat.CategoryName);
    }
}

我在 DataContext 上发现的另一个有用的方法是 Translate。Translate 方法基本上接受一个 DataReader 并返回一个对象集合。这对于返回 DataReader 的现有数据访问层非常有用，你可以使用 Translate 方法来获取你的业务对象。让我们来看一个例子。

/// <summary>
/// This example illustrates how you can use datareaders to get strongly typed objects.
/// </summary>

public static void ReturnObjectsUsingDataReader()
{
    NorthWindDataContext db = new NorthWindDataContext();
    db.Connection.Open();
    SqlCommand cmd = new SqlCommand("select categoryid,categoryName,
        Description from categories",
        db.Connection as SqlConnection);
    IEnumerable<Category> categories = db.Translate<Category>(
        cmd.ExecuteReader(CommandBehavior.CloseConnection));
    foreach (var category in categories)
    {
        Console.WriteLine(category.CategoryName);
    }
}

有时，你会想知道 LINQ to SQL 在幕后做了什么。有多种方法可以捕获 SQL。例如，你可以使用 SQL Server Profiler。DataContext 有一个名为 log 的属性，可以用来输出 SQL，或者你可以使用 DataContext 上的 GetQueryText 方法来找出执行 LINQ to SQL 查询时会执行的 SQL。让我们看一个例子。

重写 Insert、Update 和 Delete 方法

LINQ to SQL 允许你使用存储过程，并为将对象插入数据库指定自己的实现。这样做需要开发人员更多的参与，包括解决可能发生的冲突。实现这一目标的一种方法是使用设计器，将存储过程拖到方法窗格上。右键单击 Category，按如下方式配置其行为。

create proc dbo.InsertCategory
(
 @CategoryID int output,
 @CategoryName nvarchar(15),
 @Description nvarchar(100)
)

as
begin

insert into dbo.categories(categoryname,[description])
values (@CategoryName,@Description)
set @CategoryID = @@identity

end

完成向导配置了存储过程后，你可以按如下方式插入 Category。

/// <summary>
/// Example illustrates how to insert category using stored procedures.
/// </summary>

public static void InsertCategoryUsingStoredProcedure()
{
    NorthWindDataContext db = new NorthWindDataContext();
    Category category = new Category { CategoryName = "test",
        Description = "test description" };
    db.Categories.InsertOnSubmit(category);
    db.SubmitChanges();
    Console.WriteLine(category.CategoryID);
}

插入对象图

当你通过调用 InsertOnSubmit 插入一个对象时，LINQ to SQL 实际上不会插入行，直到你调用 SubmitChanges。LINQ to SQL 的一个优点是，如果你有一个对象层次结构，比如：Category、Product 和 Supplier，它们在层次结构中相互关联，你就不一定需要对每个对象都调用 InsertOnSubmit。对 Category 这样的基对象调用就足够了，LINQ to SQL 会保存所有对象，包括 Product 和 Supplier。让我们看一个例子。

/// <summary>

/// This example illustrates that you do not need to call insertonsubmit 
/// for each object you like to persist to database. calling it on the base object is
/// sufficent.
/// </summary>

public static void InsertingHiearachy()
{
    NorthWindDataContext db = new NorthWindDataContext();
    Category category = new Category
    {
        CategoryName = "Test",
        Description = "test description",
        Products =
        {
            new Product
            {
                ProductName="my product",
                Supplier= new Supplier{CompanyName="test company"}
            }
        }
};
db.Categories.InsertOnSubmit(category);
db.SubmitChanges();
var cat = db.Categories.Single(c =>

c.CategoryID == category.CategoryID);
Console.WriteLine(cat.CategoryName);
Console.WriteLine(cat.Products[0].ProductName);
Console.WriteLine(cat.Products[0].Supplier.CompanyName);
}

从上面的例子中，你会注意到我们没有对 Product 或 Supplier 调用 InsertOnSubmit，而是 LINQ to SQL 调用 SubmitChanges 时，因为它有关系，能够插入所有三个对象。

由于从 DataContext 返回的所有对象都是作为 Table<span class="code-keyword"><T> 返回的，而它恰好实现了 IQueryable<span class="code-keyword"><T>，因此大多数 LINQ to SQL 查询都是延迟执行的，并被转换为表达式树而不是中间语言代码。一旦它们被转换为表达式树，LINQ to SQL 提供程序就会将表达式树转换为 SQL，并在数据库上执行。由于查询是在数据库上执行的，在查询中调用 .NET 方法不会被翻译到数据库，这会导致查询失败。

LINQ TO SQL 延迟执行

默认情况下，LINQ to SQL 不会加载任何子实体，除非访问了子属性。这种行为称为延迟执行。这种行为确保 LINQ to SQL 不会检索任何子对象，除非它们被明确引用。让我们看一个例子。

public static void LinqDifferedExection()
{
    NorthWindDataContext db = new NorthWindDataContext();
    db.Log = Console.Out;
    var customer = db.Customers.First();
    Console.WriteLine(customer.ContactName);
    Console.WriteLine(customer.Orders.Count());
}

如果你查看执行的 SQL 语句，你会注意到执行了两个单独的查询。只有在访问 Orders 对象时，LINQ to SQL 才进行了额外的调用来访问该客户的所有订单。

尽管延迟加载是 LINQ to SQL 的默认行为，但在你不想每次访问某个子对象时都产生开销的情况下，你可以使用 DataLoadOptions 来告诉 LINQ to SQL 立即加载在 LoadWith<span class="code-keyword"><T> 操作符中指定的类。使用 LoadWith<span class="code-keyword"><T> 时，当 LINQ to SQL 查询主类时，会检索关联的类。立即加载不限于加载一个关联类。你可以提供任意数量的关联类，以便在调用它们之前加载。让我们看一个例子。

public static void ImmedidateLoading()
{
    NorthWindDataContext db = new NorthWindDataContext();
    db.Log = Console.Out;
    DataLoadOptions option = new DataLoadOptions();
    option.LoadWith<Customer>(c => c.Orders);
    db.LoadOptions = option;
    var customers = db.Customers.Where(c => c.City == "London");
    foreach (var customer in customers)
    {
        Console.WriteLine(customer.ContactName);
        foreach (Order order in customer.Orders)
        {
            Console.WriteLine(order.OrderID);
        }
    }
}

从上面的例子可以看出，我们正在遍历客户的订单集合，但 LINQ to SQL 只执行了一个查询，因为我们使用了 LoadOptions，它确保在调用时所有相关客户的订单都已加载。

当加载多个关联类时，立即加载的行为会发生变化。只有第一个类会与主类连接，其他的会在你引用主类时加载。然而，这仍然被认为是立即加载，因为 LINQ to SQL 会在实际引用对象之前查询对象。每次访问主类时，都会执行一个单独的查询来获取关联类的数据。对于这个演示，我在 Northwind 中添加了另一个名为 Address 的表。基本上，我将立即加载某个客户的所有地址和订单。

CREATE TABLE [dbo].[Address](

[AddressId]
[int] IDENTITY(1,1) NOT NULL,

[Address1]
[varchar](50) NULL,

[CustomerID]
[nchar](5) NOT NULL,

)

public static void ImmediateMultipleAssociations()
        {
            NorthWindDataContext db = new NorthWindDataContext();
            db.Log = Console.Out;
          
 
            DataLoadOptions option = new DataLoadOptions();
            option.LoadWith<Customer>(c => c.Addresses);
            option.LoadWith<Customer>(c => c.Orders);
            db.LoadOptions = option;
            var customers = db.Customers.Where(c => c.City == "London");
            foreach (Customer customer in customers)
            {
                Console.WriteLine(customer.ContactName);
            }
        }

查看 LINQ to SQL 生成的查询时，你会注意到客户与订单表连接，但每次访问一个客户时，都会为访问其地址运行一个单独的查询。由于代码中没有引用 Order 或 Order Details，我们可以正确地说它正在执行立即加载。然而，每次客户被引用时都会执行地址查询。

让我们看一个立即加载客户、其订单以及每个订单的订单详情的例子。

public static void ImmmediateLoadingWithDependency()
        {
            NorthWindDataContext db = new NorthWindDataContext();
            db.Log = Console.Out;
            DataLoadOptions option = new DataLoadOptions();
            option.LoadWith<Customer>(c => c.Orders);
            option.LoadWith<Order>(o => o.Order_Details);
            db.LoadOptions = option;
            var customers = db.Customers.Where(c => c.City == "London");
            foreach (Customer customer in customers)
            {
                Console.WriteLine(customer.ContactName);
            }

根据查询，你会注意到相同的行为，即每次在 foreach 循环中引用 Customer 时，都会执行一个单独的查询来访问 Order 表。这个例子和前面的例子之间唯一的主要区别是，这次 LINQ to SQL 只检索了 Customers。直到在 for 循环中引用 customer 对象时，LINQ to SQL 才不仅查询 Order，还查询 Order Details 表。

DataLoadOptions 的另一个用途是通过使用 AssociateWith 方法来过滤关联类。正如你在下面的结果中看到的，尽管每个类别有多个产品，但由于在 DataLoadOptions 的 AssociateWith 方法上应用的过滤器，我们使用 LINQ to SQL 只检索了每个类别的一个产品。

public static void FilteringAssociatedClasses()
        {
            NorthWindDataContext db = new NorthWindDataContext();
           
            DataLoadOptions option = new DataLoadOptions();
            option.AssociateWith<Category>(c => c.Products.Take(1));
            db.LoadOptions = option;                                   
            foreach (Category category in db.Categories)
            {
                Console.WriteLine(category.CategoryName);
                Console.WriteLine(category.Products.Count());
 
            }
        }

操作符转换

.NET 中定义了许多方法来操作字符串。其中一些方法在 LINQ to SQL 中有等效的翻译，并在 SQL Server 上执行。

public static void OperatorConversions()
        {
            NorthWindDataContext db = new NorthWindDataContext();
            db.Log = Console.Out;
            string[] cities =  {"London","Seattle"};
            var orders = db.Orders.Where(o => cities.Contains(o.ShipCity));
            Console.WriteLine(orders.Count());
        }

请注意，我们使用了 "Contains" 子句，它会被转换为 SQL Server 中的 "IN" 子句。

DataContext 查询行为

正如我在文章开头提到的，LINQ to SQL 在调用 SubmitChanges 之前不会执行你的查询。因此，如果你将一个客户添加到客户集合中，然后查询 DataContext，你将不会取回该记录，因为 SubmitChanges 还没有被调用。让我们来看一个例子。

public static void SubmitChangesBehavior()
        {
            NorthWindDataContext db = new NorthWindDataContext();
            db.Log = Console.Out;
            Customer customer = new Customer { CustomerID = "TestID",
                CompanyName = "Test Company" };
            db.Customers.InsertOnSubmit(customer);
 
            var _customer = db.Customers.SingleOrDefault(c => c.CustomerID == "TestID");
            Console.WriteLine(
                _customer == null ? "Customer not found" : "Customer found");
        }

有两点需要注意。首先，尽管 LINQ to SQL 在其上下文中跟踪该对象，但它仍然向 SQL Server 发送了一个查询来搜索具有特定 customerid 的客户。其次，你还会注意到没有找到客户。

这证实了客户对象在调用 SubmitChanges 之前不会从上下文中返回。

我注意到的另一个有趣的现象是关于 Single 操作符的行为：当你根据主键查询一个对象时，下次你使用相同的主键再次查询时，LINQ to SQL 甚至不会去数据库，它会从其内部缓存中检索对象。让我们看一个例子。

public static void LinqToSqlCachingBehavior()
        {
            NorthWindDataContext db = new NorthWindDataContext();
            db.Log = Console.Out;
            var cust1 = db.Customers.Single(c => c.CustomerID == "ALFKI");
            Console.WriteLine(cust1.ContactName);
            var cust2 = db.Customers.Single(c => c.CustomerID == "ALFKI");
            Console.WriteLine(cust2.ContactName);
        }

从结果中你会注意到，查询只被检索了一次。即使我们请求了两次，因为我们是根据主键查询的。如果不是这样的话，LINQ to SQL 会返回相同的对象，但仍然会执行两次查询。让我们看一个例子。

public static void LinqToSqlSameObjectReturned()
        {
            NorthWindDataContext db = new NorthWindDataContext();
            db.Log = Console.Out;
            var cust1 = db.Customers.Single(c => c.ContactName == "Maria Anders");
            Console.WriteLine(cust1.ContactName);
            var cust2 = db.Customers.Single(c => c.ContactName == "Maria Anders");
            Console.WriteLine(cust2.ContactName);
            Console.WriteLine(cust1 == cust2);
        }

从结果中你会注意到，尽管对象的引用是相同的，LINQ to SQL 还是执行了两次查询。