在这个版块中我来谈谈几种使用LINQ TO SQL时，提升效率的方法。
（本版块会不断更新，大家共同学习进步）
（DataContext 实例下有个 Log 属性通过设置此属性能看到，LINQ 生成的SQL代码，一下代码将用到此技巧）
1.使用延迟。
（1）延迟执行和立即执行：延迟执行不仅仅是LINQ TO SQL独有的，基本上所有的LINQ都具有此特性，它能提高效率让我们在适当的时间执行适当的查询，如果在某些特殊的情况下不想使用此特性可以使用ToList() or ToArray()方法让该查询立即执行。
示例1：延迟执行
NorthwndDataContext dc = new NorthwndDataContext();
dc.Log = Console.Out;
var query = from c in dc.Customers
select c;
Console.WriteLine("this is deferred execute.");
foreach (var item in query)
{
Console.WriteLine(item.CustomerID);
Console.ReadKey();
}
结果：
this is deferred execute.
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
ALFKI
示例2：不延迟执行
NorthwndDataContext dc = new NorthwndDataContext();
dc.Log = Console.Out;
var query = (from c in dc.Customers
select c).ToList();
Console.WriteLine("this is not deferred execute.");
foreach (var item in query)
{
Console.WriteLine(item.CustomerID);
Console.ReadKey();
}
结果：
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
this is not deferred execute.
ALFKI
（注意黄色标注的位置，就能很清楚延迟和普通执行的区别了）
(2)延迟加载和立即加载：在C#代码中往往会使用对象实例的属性去和其他对象建立关系（Customer.Order）,但有时我们需要同时需要一个完整的对象链的信息，往往只要当前对象的信息，因此我们能通过延迟加载和立即加载来实现不同的需求。
（通过DataLoadOptions实例去实现 ）
实例1：延迟加载
var query = (from c in dc.Customers
select c).ToList();
foreach (var item in query)
{
Console.WriteLine(item.CustomerID);
Console.WriteLine("this is deferred load.");
foreach (var o in item.Orders)
{
Console.WriteLine(o.OrderID);
}
Console.ReadKey();
}
结果：
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
ALFKI
this is deferred load.
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [
t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[Sh
ipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPosta
lCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [ALFKI]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
10643
...
实例2：立即执行
NorthwndDataContext dc = new NorthwndDataContext();
dc.Log = Console.Out;
DataLoadOptions lo = new DataLoadOptions();
lo.LoadWith<Entity.Customer>(c => c.Orders);
dc.LoadOptions = lo;
var query = (from c in dc.Customers
select c).ToList();
foreach (var item in query)
{
Console.WriteLine(item.CustomerID);
Console.WriteLine("this is deferred load.");
foreach (var o in item.Orders)
{
Console.WriteLine(o.OrderID);
}
Console.ReadKey();
}
结果：
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS [CustomerID
2], [t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate]
, [t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[Shi
pCity], [t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t2]
WHERE [t2].[CustomerID] = [t0].[CustomerID]
) AS [value]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1
ALFKI
this is deferred load.
10643
...
(同样可以看到2者的差别,注意使用这种延迟加载,要在对应的实体类里为要做映射的类使用EntityRef和EntitySet包装，
对实体类映射的某个字段做延迟加载要使用Link包装）
2.编译查询：往往一个查询表达式要被重复使用很多次，如果每次都反复的生成表达式树，然后生成SQL语句势必开销很大，效率很低。因此可以使用编译查询，它把不变的部分编译好，把需要改变的部分再进去就OK了。
示例：
//编译查询核心
var query1 = CompiledQuery.Compile((NorthwndDataContext dc1, string initialChar) =>
from c in dc1.Customers
where c.CustomerID.StartsWith(initialChar)
select c);
foreach (var item1 in new[] { "A", "B", "C", "D" })
{
//调用方法
var query = query1(dc, item1);
foreach (var item in query)
{
Console.WriteLine("Customer Id :{0}", item.CustomerID);
}
}
以上方法请在适合的地方选择性使用，这样才能提高效率。其他能提高LINQ TO SQL的效率和方法会在今后继续补充
