Entity Framework projection behaviour

Question

First post on here and quite a simple one.

I've been looking into simplifying some complex queries in an application I'm developing and I'm scratching my head a bit on the below.

So say I have these two classes:

A domain entity "EmailRecipient" (used with EF code-first so expect a SQL table to be generated with the same column names).

public class EmailRecipient
{
    public Guid Id { get; set; }
    public string FriendlyName { get; set; }
    public string ExchangeName { get; set; }
    public string Surname { get; set; }
    public string Forename { get; set; }
    public string EmailAddress { get; set; }
    public string JobTitle { get; set; }

    public virtual List<SentEmail> SentEmails { get; set; }
}

and a simple class for JSON serialisation called "EmailLite" defined as

public class EmailLite
{
    public string EmailAddress { get; set; }
    public Guid Id { get; set; }
    public string FriendlyName { get; set; }
}

In my specialised EF6(.1.3) DbContext, I have a DbSet called EmailRecipients.

So naturally executing this linq expression against EmailRecipients

EmailRecipients.Select(x => new EmailLite
        {
            Id = x.Id,
            EmailAddress = x.EmailAddress,
            FriendlyName = x.FriendlyName
        });

the generated SQL is

SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[EmailAddress] AS [EmailAddress], 
    [Extent1].[FriendlyName] AS [FriendlyName]
    FROM [dbo].[EmailRecipients] AS [Extent1]

So why when I do:

Func<EmailRecipient, EmailLite> projectionFunction = x => new EmailLite
        {
            Id = x.Id,
            EmailAddress = x.EmailAddress,
            FriendlyName = x.FriendlyName
        };

EmailRecipients.Select(projectionFunction);

do I get the below (complete) SQL generated:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[FriendlyName] AS [FriendlyName], 
    [Extent1].[ExchangeName] AS [ExchangeName], 
    [Extent1].[Surname] AS [Surname], 
    [Extent1].[Forename] AS [Forename], 
    [Extent1].[EmailAddress] AS [EmailAddress], 
    [Extent1].[JobTitle] AS [JobTitle], 
    [Extent1].[SubscribedOn] AS [SubscribedOn]
    FROM [dbo].[EmailRecipients] AS [Extent1]

Any help would be most appreciated!

Cheers, Sat

Answer 1

IQueryable<T>.Select() takes an Expression<Func<T,TOut>> as parameter, the function you're actually using is IEnumerable<T>.Select() which takes a delegate. Because of this, you are telling EF that from that moment on, you are using IEnumerable rather than IQueryable and the rest of the query will be executed in memory => you're fetching all the columns.

EmailRecipients   <-- in memory from here on --> .Select(projectionFunction);

All you need to do is change projectionFunction into an expression and it will work:

Expression<Func<EmailRecipient, EmailLite>> projectionFunction = x => new EmailLite
{
    Id = x.Id,
    EmailAddress = x.EmailAddress,
    FriendlyName = x.FriendlyName
};