在LINQ中只选择一列

The EntityModel is defined as: Personnel has a link to a Country

EntityModel定义为：Personnel具有指向Country的链接

When executing this code in LinqPad, I see that the SQL which is generated is not optimized (all fields are returned) in the first query ? What am I missing here or doing wrong ?

在LinqPad中执行此代码时，我发现第一个查询中生成的SQL未优化（返回所有字段）？我在这里错过了什么或做错了什么？

Query 1 LINQ

查询1 LINQ

var Country = Countries.FirstOrDefault(o => o.Id == 100000581);
var personnelIds = Country.Personnels.Select(p => p.Id).ToArray();
personnelIds.Dump();

Query 1 SQL

查询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

Query 2 LINQ

查询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();

Query 2 SQL

查询2 SQL

exec sp_executesql N'SELECT [t0].[Id] FROM [Personnel] AS [t0] WHERE [t0].[Country_Id] = @p0',N'@p0 bigint',@p0=100000581

The database used is SQL Express 2008. And LinqPad version is 4.43.06

使用的数据库是SQL Express 2008.而LinqPad版本是4.43.06

4 个解决方案

#1

//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();

Try this, it should be better.

试试这个，应该会更好。

#2

Personnels collection will be populated via lazy loading when accessed, hence retrieving all of the fields from the DB. Here's what's happening...

人员集合将在访问时通过延迟加载填充，从而从数据库中检索所有字段。这是发生了什么......

// 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();

You want something more like this:

你想要更像这样的东西：

// 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();

OR:

要么：

var result = personnelIdsQuery.SingleOrDefault();

Then get the array of IDs if not null

然后获取ID数组，如果不为null

if( null != result )
{
    var personnelIds = result.PersonnelIds;
}

#3

Try can also try grouping personnel into a single query

尝试也可以尝试将人员分组到单个查询中

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);

#4

Do you have the ForeignKey explicitly defined in your POCO for Personnel? It's common to leave it out in EF, but adding it would massively simplify both this code and the resulting SQL:

您是否在POCO for Personnel中明确定义了ForeignKey？将其保留在EF中是很常见的，但添加它会大大简化此代码和生成的SQL：

public class Personnel
{
    public Country Country { get; set; }

    [ForeignKey("Country")]
    public int CountryId { get; set; }

    . . .
}

> update-database -f -verbose

var ids = db.Personnel.Where(p => p.CountryId == 100000581).Select(p => p.Id).ToArray();

#1

//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();