在LINQ中仅选择一列
在LINQ中仅选择一列
提问于 2013-03-29 03:43:40
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
回答 4
Stack Overflow用户
发布于 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();试试这个，应该会更好。
Stack Overflow用户
发布于 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;
}Stack Overflow用户
发布于 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);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15690756
复制相关文章
相似问题