c# - Entity Framework 投影行为

Question

这里的第一篇文章非常简单。

我一直在研究如何简化我正在开发的应用程序中的一些复杂查询，但我对下面的内容有些摸不着头脑。

假设我有这两个类:

域实体“EmailRecipient”(首先与 EF 代码一起使用，因此期望生成具有相同列名的 SQL 表)。

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

还有一个名为“EmailLite”的用于 JSON 序列化的简单类，定义为

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

在我的专用 EF6(.1.3) DbContext 中，我有一个名为 EmailRecipients 的 DbSet。

因此很自然地针对 EmailRecipients 执行此 linq 表达式

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

生成的SQL是

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

那么为什么当我这样做时:

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

EmailRecipients.Select(projectionFunction);

我是否生成了以下(完整的)SQL:

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]

如有任何帮助，我们将不胜感激!

干杯，星期六

Answer 1

IQueryable<T>.Select() 需要 Expression<Func<T,TOut>>作为参数，您实际使用的函数是 IEnumerable<T>.Select() 这需要一个代表。因此，您告诉 EF 从那一刻起，您正在使用 IEnumerable而不是 IQueryable查询的其余部分将在内存中执行 => 您正在获取所有列。

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

您需要做的就是更改 projectionFunction变成一个表达式，它将起作用:

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