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