自己来控制EntityFramework4.1 Code-First，强大的EF多种加载方式

众所周知，EF对关系表的联表查询有2种方式：延迟加载、贪婪加载，为了弄清这2种方式具体是如何操作数据库的，我开启了SQL Server Profiler，只开启了存储过程和TSQL的监视，并且指定了当前操作的库。

   //3联表，Order Product Category

1. var ordercontext = db.OrderContext.Include(o => o.Product); //外键表贪婪一个，延迟一个Category 

2. var ordercontext = db.OrderContext.Include(o => o.Product).Include(o=>o.Product.Category) //外键表全贪婪

3. var ordercontext = db.OrderContext;  //外键表全延时

对应生成的SQL并执行的语句为：查询输出100条记录

1.半延迟半贪婪  3次数据库查询

执行时间：0.0181664 
解析时间：0.0629575
总时间：0.0811491

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Product_ID] AS [Product_ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[Address] AS [Address], 
[Extent1].[CreateTime] AS [CreateTime], 
[Extent2].[ID] AS [ID1], 
[Extent2].[Name] AS [Name1], 
[Extent2].[CategoryID] AS [CategoryID], 
[Extent2].[Price] AS [Price]
FROM  [dbo].[Order] AS [Extent1]
INNER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[Product_ID] = [Extent2].[ID]

exec sp_executesql N'SELECT 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[CategoryName] AS [CategoryName], 
[Extent1].[IsDel] AS [IsDel]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

exec sp_executesql N'SELECT 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[CategoryName] AS [CategoryName], 
[Extent1].[IsDel] AS [IsDel]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

2. 全贪婪  1次数据库查询

执行时间：0.0173032 
解析时间：0.0533344
总时间：0.0706627

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Product_ID] AS [Product_ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[Address] AS [Address], 
[Extent1].[CreateTime] AS [CreateTime], 
[Extent2].[ID] AS [ID1], 
[Extent2].[Name] AS [Name1], 
[Extent2].[CategoryID] AS [CategoryID], 
[Extent2].[Price] AS [Price], 
[Extent4].[CategoryID] AS [CategoryID1], 
[Extent4].[CategoryName] AS [CategoryName], 
[Extent4].[IsDel] AS [IsDel]
FROM    [dbo].[Order] AS [Extent1]
INNER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[Product_ID] = [Extent2].[ID]
LEFT OUTER JOIN [dbo].[Product] AS [Extent3] ON [Extent1].[Product_ID] = [Extent3].[ID]
LEFT OUTER JOIN [dbo].[Category] AS [Extent4] ON [Extent3].[CategoryID] = [Extent4].[CategoryID]

3.全延迟加载， 延迟2个表 ，5次数据库查询

执行时间：0.0128477 
解析时间：0.0796226
总时间：0.0925025

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Product_ID] AS [Product_ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[Address] AS [Address], 
[Extent1].[CreateTime] AS [CreateTime]
FROM [dbo].[Order] AS [Extent1]

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[Price] AS [Price]
FROM [dbo].[Product] AS [Extent1]
WHERE [Extent1].[ID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

exec sp_executesql N'SELECT 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[CategoryName] AS [CategoryName], 
[Extent1].[IsDel] AS [IsDel]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

exec sp_executesql N'SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[Price] AS [Price]
FROM [dbo].[Product] AS [Extent1]
WHERE [Extent1].[ID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

exec sp_executesql N'SELECT 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[CategoryName] AS [CategoryName], 
[Extent1].[IsDel] AS [IsDel]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

三中加载方式和对应的实际操作SQL命令我贴了出来，每种方式的数据库查询次数都不同，并且延迟加载是以存储过程方式执行的语句； 细心的人可能发现我输出了执行时间，这个我是在MVC过滤器中做的定时器，虽然我已经刷新N次，取了中间值，但这个执行时间还是比较离奇，权当我机器环境不稳定了，但有点可理解的是，比如底3种，按说他应该最慢的，但并没有如此，因为第一条sql语句没有使用联表节约了时间，而其后面的4条延时加载又以存储过程方式执行提高了性能。

需要注意的是，经调试和监视SQL SERVER，发现EF对延迟加载统一使用存储过程方式执行，而贪婪式则为JOIN联表操作，此文并不作几种方式的性能评测结论，因为联表中的外键表记录只有2条，所以聪明的EF是读取所需的外键值针对性的去读库，而不是100条记录联2个表就要额外读200次外键表。自然，当数据库联表外键值较多时，或者每条主表记录的外键值都不同时，的确要读和记录相同数的数据库次数。

因地而异，并不能指定怎样去用何种方式加载，但输出关系表内容，并调用关系表字段值时，仍然建议使用贪婪式加载。