EntityModel的定义是: Personnel有一个国家/地区的链接
当在LinqPad中执行这段代码时,我看到在第一个查询中生成的SQL没有优化(所有字段都被返回)?我在这里遗漏了什么或者做错了什么?
查询1 LINQ
var Country = Countries.FirstOrDefault(o => o.Id == 100000581);
var personnelIds = Country.Personnels.Select(p => p.Id).ToArray();
personnelIds.Dump();
查询%1 SQL
exec sp_executesql N'SELECT [t0].[Id], [t0].[Version], [t0].[Identifier], [t0].[Name], , [t0].[UpdatedBy] FROM [Personnel] AS [t0] WHERE [t0].[Country_Id] = @p0',N'@p0 bigint',@p0=100000581
查询2 LINQ
var Country = Countries.FirstOrDefault(o => o.Id == 100000581);
var personnelIds2 = Personnels.Where(p => p.Country == Country).Select(p => p.Id).ToArray();
personnelIds2.Dump();
查询2 SQL
exec sp_executesql N'SELECT [t0].[Id] FROM [Personnel] AS [t0] WHERE [t0].[Country_Id] = @p0',N'@p0 bigint',@p0=100000581
使用的数据库是SQL Express 2008。LinqPad版本为4.43.06
发布于 2013-03-29 03:47:25
//var Country = Countries.FirstOrDefault(o => o.Id == 100000581);
var personnelIds = context.Personnels
.Where(p => p.Country.Id == 100000581)
.Select(p => p.Id)
.ToArray();
personnelIds.Dump();
试试这个,应该会更好。
发布于 2013-03-29 08:10:48
当被访问时,Personnels集合将通过惰性加载进行填充,从而从DB中检索所有字段。事情是这样的.
// retrieves data and builds the single Country entity (if not null result)
var Country = Countries.FirstOrDefault(o => o.Id == 100000581);
// Country.Personnels accessor will lazy load and construct all Personnel entity objects related to this country entity object
// hence loading all of the fields
var personnelIds = Country.Personnels.Select(p => p.Id).ToArray();
你想要更多像这样的东西:
// build base query projecting desired data
var personnelIdsQuery = dbContext.Countries
.Where( c => c.Id == 100000581 )
.Select( c => new
{
CountryId = c.Id,
PersonnelIds = c.Personnels.Select( p => p.Id )
}
// now do enumeration
// your example shows FirstOrDefault without OrderBy
// either use SingleOrDefault or specify an OrderBy prior to using FirstOrDefaul
var result = personnelIdsQuery.OrderBy( item => item.CountryId ).FirstOrDefault();
或者:
var result = personnelIdsQuery.SingleOrDefault();
如果不为空,则获取ID数组
if( null != result )
{
var personnelIds = result.PersonnelIds;
}
发布于 2013-03-29 04:26:04
Try还可以尝试将人员分组到单个查询中
var groups =
(from p in Personnel
group p by p.CountryId into g
select new
{
CountryId = g.Key
PersonnelIds = p.Select(x => x.Id)
});
var personnelIds = groups.FirstOrDefault(g => g.Key == 100000581);
https://stackoverflow.com/questions/15690756
复制相似问题