EF加载外键的一个性能问题

最新推荐文章于 2021-05-24 17:13:49 发布

weixin_30871701

最新推荐文章于 2021-05-24 17:13:49 发布

阅读量222

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/FoundationSoft/archive/2012/03/09/2388478.html

本文介绍在使用Entity Framework进行数据库操作时如何避免加载不必要的数据，通过具体案例对比不同查询方式所生成的SQL语句，展示了如何仅获取所需字段以提高查询效率。

数据库结构简单描述如下：有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