EF加载外键的一个性能问题
数据库结构简单描述如下：有3个表，企业利润报表T1,企业表T2,机构表T3, T1通过外键关联到T2，T2又外键关联到T3。
现在在查询T1表的数据时，需要同时加载到机构名称，这是存储在T3表中的Name列中。
在使用Entity Framework查询数据时，假如已经得到了T1的实体对象保存在entity中，那么，要得到T3表中的名称，最简单的写法就是：
string name = entity.T2.T3.Name;
但是这种写法有比较大的性能问题，这样会加载了与entity所关联的整个T2对象，然后又加载了与T2相关联的T3整个对象，最后从T3中取出Name字段。实际上，这里只需要一个Name字段，却加载了2个表的所有列，也许会有30列。这显示是一种性能浪费，造成数据库以及内存的不必要的负担。
所生成的SQL语句如下：
1 exec sp_executesql N'SELECT
2 [Extent1].[Id] AS [Id],
3 [Extent1].[TypeId] AS [TypeId],
4 [Extent1].[CredibilityGrade] AS [CredibilityGrade],
5 [Extent1].[WorkRange] AS [WorkRange],
6 [Extent1].[OwnerTypeId] AS [OwnerTypeId],
7 [Extent1].[FoundDate] AS [FoundDate],
8 [Extent1].[Fax] AS [Fax],
9 [Extent1].[Email] AS [Email],
10 [Extent1].[WebSite] AS [WebSite],
11 [Extent1].[BusinessCertificateId] AS [BusinessCertificateId],
12 [Extent1].[QualificationCerId] AS [QualificationCerId],
13 [Extent1].[SafetyCerId] AS [SafetyCerId]
14 FROM [dbo].[Enterprise] AS [Extent1]
15 WHERE [Extent1].[Id] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(20)',@EntityKeyValue1=N'bz003'
16
17
18
19 exec sp_executesql N'SELECT
20 [Extent1].[Id] AS [Id],
21 [Extent1].[Name] AS [Name],
22 [Extent1].[TypeId] AS [TypeId],
23 [Extent1].[Address] AS [Address],
24 [Extent1].[ContactPerson] AS [ContactPerson],
25 [Extent1].[ContactPhone] AS [ContactPhone],
26 [Extent1].[Zipcode] AS [Zipcode],
27 [Extent1].[Cellphone] AS [Cellphone],
28 [Extent1].[AreaId] AS [AreaId]
29 FROM [dbo].[Institution] AS [Extent1]
30 WHERE [Extent1].[Id] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(20)',@EntityKeyValue1=N'bz003'
正确的应该是仅查询必须的列，对应的代码是：
string name=(from t in context where t.Id == entity.Id select t.T2.T3.Name).FirstOrDefault();
所生成的SQL语句如下
1 exec sp_executesql N'SELECT
2 [Limit1].[Name] AS [Name]
3 FROM ( SELECT TOP (1)
4 [Extent2].[Name] AS [Name]
5 FROM [dbo].[EnterpriseReport] AS [Extent1]
6 LEFT OUTER JOIN [dbo].[Institution] AS [Extent2] ON [Extent1].[EnterpriseId] = [Extent2].[Id]
7 WHERE [Extent1].[Id] = @p__linq__0
8 ) AS [Limit1]',N'@p__linq__0 int',@p__linq__0=1
