Entity Framework Core关联显示属性且不加载完整子对象的最佳实践

背景 #

我们公司近期刚切换到EF Core，此前20余年一直采用存储过程作为ORM方案。我清楚EF Core会在数据库层面生成查询，而非在内存中枚举数据。

场景1：原有存储方案 #

以Membership_InsuranceExtension对象为例，它用于存储Membership与InsuranceExtension的关联关系，InsuranceExtension的静态数据存储在tbl_MembershipLevel_InsuranceExtension表中。

当需要获取大量对象列表（结果集可达数千行）时，我们会创建类似xxxx_SummaryItem的专用类，只包含StatusName、InsuranceExtensionName等展示所需的关联属性，作为精简版显示对象。这种方式高效且性能优异，但不确定EF Core是否有原生方式实现该效果，无需加载完整子对象。

示例存储过程如下：

CREATE PROCEDURE [dbo].[usp_Membership_InsuranceExtension_FetchSummaryList] 
      @MembershipID             uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON;

SELECT
    tbl_Membership_InsuranceExtension.MembershipInsuranceExtensionID
  , tbl_Membership_InsuranceExtension.Created
  , tbl_Membership_InsuranceExtension.LastUpdated

  , tbl_Membership_InsuranceExtension.MembershipID
  , tbl_Membership_InsuranceExtension.StatusID
  , tbl_Membership_InsuranceExtension_Status.[Name] AS StatusName

  , tbl_Membership_InsuranceExtension.InsuranceExtensionID
  , tbl_MembershipLevel_InsuranceExtension.[Name] AS InsuranceExtensionName
  , tbl_Membership_InsuranceExtension.ValidFrom
  , tbl_Membership_InsuranceExtension.ValidTo

FROM
    tbl_Membership_InsuranceExtension
    LEFT OUTER JOIN tbl_MembershipLevel_InsuranceExtension ON tbl_Membership_InsuranceExtension.InsuranceExtensionID = tbl_MembershipLevel_InsuranceExtension.InsuranceExtensionID 
    LEFT OUTER JOIN tbl_Membership_InsuranceExtension_Status ON tbl_Membership_InsuranceExtension.StatusID = tbl_Membership_InsuranceExtension_Status.InsuranceExtensionStatusID 


WHERE
    tbl_Membership_InsuranceExtension.MembershipID = @MembershipID

ORDER BY 
    tbl_Membership_InsuranceExtension.Created

场景2：EF Core实现疑问 #

在EF Core中，如何重现上述原有方式的效果，且无需为查询返回的每一行加载完整的子对象（如MembershipLevel_InsuranceExtension）？

实体示例 #

BaseEntity包含主键、Created/LastUpdated字段，当前子实体通过仓储中的.Include方法进行贪婪加载。

public class Membership : BaseEntity
{
    public Membership() : base()
    {
        ValidFrom = DateTime.Today.Date;
        ValidTo = ValidFrom.AddYears(1).AddDays(-1);
    }

    public virtual List<Membership_InsuranceExtension> InsuranceExtensions { get; set; }

    public DateTime ValidFrom { get; set; }
    public DateTime ValidTo { get; set; }
}

public class Membership_InsuranceExtension : BaseEntity
{
    public enum InsuranceExtensionStatusType
    {
        Unknown = 0,
        Unverified = 1,
        Verified = 2,
        Rejected = 3,
        Cancelled = 4,
        Deleted = 100
    }

    public Membership_InsuranceExtension(Membership membership) : base()
    {
        Status = InsuranceExtensionStatusType.Unverified;
        Membership = membership;
        ValidFrom = membership.ValidFrom;
        ValidTo = membership.ValidTo;
    }

    public Guid MembershipID { get; set; }
    public virtual Membership Membership { get; set; }

    [Column("StatusID")]
    public InsuranceExtensionStatusType Status { get; set; }

    public Guid InsuranceExtensionID { get; set; }
    public virtual MembershipLevel_InsuranceExtension InsuranceExtension { get; set; }

    public DateTime ValidFrom { get; set; }
    public DateTime ValidTo { get; set; }
}

public class MembershipLevel_InsuranceExtension : BaseEntity
{
    public MembershipLevel_InsuranceExtension() : base()
    {
        Name = String.Empty;
        Description = String.Empty;
    }

    public string Name { get; set; }
    public string Description { get; set; }
}

解决方案 #

EF Core完全支持这种精简查询的需求，有几种直接的实现方式，和你原有存储过程的逻辑对齐，且不会加载完整子对象：

1. 使用匿名类型直接投影（快速实现，无需额外类） #

如果只是临时用一次查询，不需要复用，可以直接在查询里投影成匿名类型，EF Core会自动生成和你存储过程一致的SQL：

var summaryList = await dbContext.Membership_InsuranceExtension
    .Where(mie => mie.MembershipID == membershipId)
    .LeftJoin(dbContext.MembershipLevel_InsuranceExtension,
        mie => mie.InsuranceExtensionID,
        mieStatic => mieStatic.Id,
        (mie, mieStatic) => new { mie, mieStatic })
    .LeftJoin(dbContext.Membership_InsuranceExtension_Status,
        combined => (int)combined.mie.Status,
        status => status.InsuranceExtensionStatusID,
        (combined, status) => new 
        {
            combined.mie.MembershipInsuranceExtensionID,
            combined.mie.Created,
            combined.mie.LastUpdated,
            combined.mie.MembershipID,
            StatusID = (int)combined.mie.Status,
            StatusName = status.Name,
            combined.mie.InsuranceExtensionID,
            InsuranceExtensionName = combined.mieStatic.Name,
            combined.mie.ValidFrom,
            combined.mie.ValidTo
        })
    .OrderBy(mie => mie.Created)
    .ToListAsync();

注：这里假设Membership_InsuranceExtension_Status是对应的状态表实体，需根据实际模型调整关联条件。

2. 定义专用的Summary类（和你原有xxxx_SummaryItem一致） #

如果需要复用这个查询结果类型，就定义一个和存储过程返回字段匹配的类，比如MembershipInsuranceExtensionSummary：

public class MembershipInsuranceExtensionSummary
{
    public Guid MembershipInsuranceExtensionID { get; set; }
    public DateTime Created { get; set; }
    public DateTime LastUpdated { get; set; }
    public Guid MembershipID { get; set; }
    public int StatusID { get; set; }
    public string StatusName { get; set; }
    public Guid InsuranceExtensionID { get; set; }
    public string InsuranceExtensionName { get; set; }
    public DateTime ValidFrom { get; set; }
    public DateTime ValidTo { get; set; }
}

然后在查询中投影到这个类：

var summaryList = await dbContext.Membership_InsuranceExtension
    .Where(mie => mie.MembershipID == membershipId)
    .Select(mie => new MembershipInsuranceExtensionSummary
    {
        MembershipInsuranceExtensionID = mie.Id,
        Created = mie.Created,
        LastUpdated = mie.LastUpdated,
        MembershipID = mie.MembershipID,
        StatusID = (int)mie.Status,
        StatusName = dbContext.Membership_InsuranceExtension_Status
            .Where(s => s.InsuranceExtensionStatusID == (int)mie.Status)
            .Select(s => s.Name)
            .FirstOrDefault(),
        InsuranceExtensionID = mie.InsuranceExtensionID,
        InsuranceExtensionName = mie.InsuranceExtension.Name,
        ValidFrom = mie.ValidFrom,
        ValidTo = mie.ValidTo
    })
    .OrderBy(mie => mie.Created)
    .ToListAsync();

EF Core会自动把这些关联查询转化为SQL里的JOIN，不会加载完整的MembershipLevel_InsuranceExtension或Membership_InsuranceExtension_Status实体，只取需要的字段。

3. 使用无键实体（EF Core 3.0+可用，适合只读场景） #

如果这个精简查询是高频使用的只读场景，可以把MembershipInsuranceExtensionSummary定义为无键实体，专门用于映射查询结果：

// 在DbContext中配置
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MembershipInsuranceExtensionSummary>().HasNoKey();
}

查询方式和第二种一致，EF Core会针对只读场景优化查询性能，适配大数据量需求。

关键注意点 #

• 不要用.Include，因为.Include会加载完整的子实体，而我们用Select投影只取需要的字段，EF Core会生成更高效的SQL，和你原有存储过程的执行逻辑完全一致。
• 所有关联逻辑都会被EF Core转化为数据库层面的JOIN，不会在内存中做过滤或关联，保证性能。

内容的提问来源于stack exchange，提问作者Ripmax