1.使用延迟。

（1）延迟执行和立即执行：延迟执行不仅仅是LINQ TO SQL独有的，基本上所有的LINQ都具有此特性，它能提高效率让我们在适当的时间执行适当的查询，如果在某些特殊的情况下不想使用此特性可以使用 ToList() or ToArray()方法让该查询立即执行。

示例1：延迟执行01 NorthwndDataContext dc = new NorthwndDataContext();
02 dc.Log = Console.Out;
03 var query = from c in dc.Customers
04 select c; Oracle 连接错误;ORA-27101: shared memory realm does not exist
05 Console.WriteLine("this is deferred execute.");
06
07 foreach (var item in query)
08 {
09 Console.WriteLine(item.CustomerID);
10 Console.ReadKey();
11 }


结果：

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：不延迟执行01 NorthwndDataContext dc = new NorthwndDataContext();
02 dc.Log = Console.Out;
03 var query = (from c in dc.Customers
04 select c).<span style="background-color: #ff0000;">ToList();</span>
05 Console.WriteLine("this is not deferred execute.");
06
07 foreach (var item in query)
08 {
09 Console.WriteLine(item.CustomerID);
10 Console.ReadKey();
11 }


结果：

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：延迟加载01 var query = (from c in dc.Customers
02 select c).ToList();
03
04 foreach (var item in query)
05 {
06 Console.WriteLine(item.CustomerID);
07 Console.WriteLine("this is deferred load.");
08 foreach (var o in item.Orders)
09 {
10 Console.WriteLine(o.OrderID);
11 }
12 Console.ReadKey();
13 }


结果：

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：立即执行01 NorthwndDataContext dc = new NorthwndDataContext();
02 dc.Log = Console.Out;
03
04 <span style="background-color: #ff0000;">DataLoadOptions lo = new DataLoadOptions();
05 lo.LoadWith<Entity.Customer>(c => c.Orders);
06 dc.LoadOptions = lo;
07 </span>
08 var query = (from c in dc.Customers
09 select c).ToList();
10
11 foreach (var item in query)
12 {
13 Console.WriteLine(item.CustomerID);
14 Console.WriteLine("this is deferred load.");
15 foreach (var o in item.Orders)
16 {
17 Console.WriteLine(o.OrderID);
18 }
19 Console.ReadKey();
20 }



结果：

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了。

示例：
view source
print
?01 //编译查询核心
02 var query1 = CompiledQuery.Compile((NorthwndDataContext dc1, string initialChar) =>
03 from c in dc1.Customers
04 where c.CustomerID.StartsWith(initialChar)
05 select c);
06
07 foreach (var item1 in new[] { "A", "B", "C", "D" })
08 {
09 //调用方法
10 var query = query1(dc, item1);
11 foreach (var item in query)
12 {
13 Console.WriteLine("Customer Id :{0}", item.CustomerID);
14 }
15 }