CODEWITHSUNDEEP
linqentity-frameworkselect
Stef Heyenrath
Stef Heyenrath

Reputation: 9820

Select only a single column in LINQ

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

Upvotes: 32

Views: 110023

Answers (4)

Moho
Moho

Reputation: 16498

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

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

Upvotes: 4

Chris Moschini
Chris Moschini

Reputation: 37947

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:

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

Upvotes: 0

p.s.w.g
p.s.w.g

Reputation: 148980

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

Upvotes: 1

AD.Net
AD.Net

Reputation: 13399

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

Upvotes: 48

Related Questions