Select only a single column in LINQ

Question

The EntityModel is defined as: Personnel has a link to a 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 ?

Query 1 LINQ

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

Query 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

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

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

Answer 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.