众所周知，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) //外键表全贪婪<br>3. var ordercontext = db.OrderContext; //外键表全延时 |
对应生成的SQL并执行的语句为：查询输出100条记录
1.半延迟半贪婪 3次数据库查询执行时间：0.0181664 <br>解析时间：0.0629575<br>总时间：0.0811491SELECT <br>[Extent1].[ID] AS [ID], <br>[Extent1].[Product_ID] AS [Product_ID], <br>[Extent1].[Name] AS [Name], <br>[Extent1].[Address] AS [Address], <br>[Extent1].[CreateTime] AS [CreateTime], <br>[Extent2].[ID] AS [ID1], <br>[Extent2].[Name] AS [Name1], <br>[Extent2].[CategoryID] AS [CategoryID], <br>[Extent2].[Price] AS [Price]<br>FROM [dbo].[Order] AS [Extent1]<br>INNER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[Product_ID] = [Extent2].[ID]exec sp_executesql N'SELECT <br>[Extent1].[CategoryID] AS [CategoryID], <br>[Extent1].[CategoryName] AS [CategoryName], <br>[Extent1].[IsDel] AS [IsDel]<br>FROM [dbo].[Category] AS [Extent1]<br>WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1exec sp_executesql N'SELECT <br>[Extent1].[CategoryID] AS [CategoryID], <br>[Extent1].[CategoryName] AS [CategoryName], <br>[Extent1].[IsDel] AS [IsDel]<br>FROM [dbo].[Category] AS [Extent1]<br>WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=22. 全贪婪 1次数据库查询执行时间：0.0173032 <br>解析时间：0.0533344<br>总时间：0.0706627SELECT <br>[Extent1].[ID] AS [ID], <br>[Extent1].[Product_ID] AS [Product_ID], <br>[Extent1].[Name] AS [Name], <br>[Extent1].[Address] AS [Address], <br>[Extent1].[CreateTime] AS [CreateTime], <br>[Extent2].[ID] AS [ID1], <br>[Extent2].[Name] AS [Name1], <br>[Extent2].[CategoryID] AS [CategoryID], <br>[Extent2].[Price] AS [Price], <br>[Extent4].[CategoryID] AS [CategoryID1], <br>[Extent4].[CategoryName] AS [CategoryName], <br>[Extent4].[IsDel] AS [IsDel]<br>FROM [dbo].[Order] AS [Extent1]<br>INNER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[Product_ID] = [Extent2].[ID]<br>LEFT OUTER JOIN [dbo].[Product] AS [Extent3] ON [Extent1].[Product_ID] = [Extent3].[ID]<br>LEFT OUTER JOIN [dbo].[Category] AS [Extent4] ON [Extent3].[CategoryID] = [Extent4].[CategoryID]3.全延迟加载， 延迟2个表 ，5次数据库查询执行时间：0.0128477 <br>解析时间：0.0796226<br>总时间：0.0925025SELECT <br>[Extent1].[ID] AS [ID], <br>[Extent1].[Product_ID] AS [Product_ID], <br>[Extent1].[Name] AS [Name], <br>[Extent1].[Address] AS [Address], <br>[Extent1].[CreateTime] AS [CreateTime]<br>FROM [dbo].[Order] AS [Extent1]exec sp_executesql N'SELECT <br>[Extent1].[ID] AS [ID], <br>[Extent1].[Name] AS [Name], <br>[Extent1].[CategoryID] AS [CategoryID], <br>[Extent1].[Price] AS [Price]<br>FROM [dbo].[Product] AS [Extent1]<br>WHERE [Extent1].[ID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1exec sp_executesql N'SELECT <br>[Extent1].[CategoryID] AS [CategoryID], <br>[Extent1].[CategoryName] AS [CategoryName], <br>[Extent1].[IsDel] AS [IsDel]<br>FROM [dbo].[Category] AS [Extent1]<br>WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1exec sp_executesql N'SELECT <br>[Extent1].[ID] AS [ID], <br>[Extent1].[Name] AS [Name], <br>[Extent1].[CategoryID] AS [CategoryID], <br>[Extent1].[Price] AS [Price]<br>FROM [dbo].[Product] AS [Extent1]<br>WHERE [Extent1].[ID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2exec sp_executesql N'SELECT <br>[Extent1].[CategoryID] AS [CategoryID], <br>[Extent1].[CategoryName] AS [CategoryName], <br>[Extent1].[IsDel] AS [IsDel]<br>FROM [dbo].[Category] AS [Extent1]<br>WHERE [Extent1].[CategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2 |
三中加载方式和对应的实际操作SQL命令我贴了出来，每种方式的数据库查询次数都不同，并且延迟加载是以存储过程方式执行的语句； 细心的人可能发现我输出了执行时间，这个我是在MVC过滤器中做的定时器，虽然我已经刷新N次，取了中间值，但这个执行时间还是比较离奇，权当我机器环境不稳定了，但有点可理解的是，比如底3种，按说他应该最慢的，但并没有如此，因为第一条sql语句没有使用联表节约了时间，而其后面的4条延时加载又以存储过程方式执行提高了性能。
需要注意的是，经调试和监视SQL SERVER，发现EF对延迟加载统一使用存储过程方式执行，而贪婪式则为JOIN联表操作，此文并不作几种方式的性能评测结论，因为联表中的外键表记录只有2条，所以聪明的EF是读取所需的外键值针对性的去读库，而不是100条记录联2个表就要额外读200次外键表。自然，当数据库联表外键值较多时，或者每条主表记录的外键值都不同时，的确要读和记录相同数的数据库次数。
因地而异，并不能指定怎样去用何种方式加载，但输出关系表内容，并调用关系表字段值时，仍然建议使用贪婪式加载。
