在EF Core 5中，您是否必须使用".Include"来加载子对象？

22 浏览
0 Comments

在EF Core 5中，您是否必须使用".Include"来加载子对象？

我习惯使用EF 6，对EF Core还不熟悉。我试图从一个具有一些外键关系的表中简单获取数据。

我从以下代码开始：

List myStatements = new List();
myStatements = db.Statements.Select(s => new StatementModel
{
    StatmentId = s.StatementId,
    EmployeeNumber = s.EmployeeNumber,
    FirstName = s.Employee.FirstName,
    LastName = s.Employee.LastName,
    PlanType = s.Employee.PlanType != null ? s.Employee.PlanType.PlanTypeName : "",
    FiscalPeriod = s.FiscalPeriod.StartDate.ToString("yyyy-MM-dd") + " - " + s.FiscalPeriod.EndDate.ToString("yyyy-MM-dd"),
    CostCenterId = s.Employee.CostCenterId,
    RVPName = s.Employee.CostCenter.Evp != null ? s.Employee.CostCenter.Evp.FirstName + " " + s.Employee.CostCenter.Evp.LastName : "",
    SVPName = s.Employee.CostCenter.Svp != null ? s.Employee.CostCenter.Svp.FirstName + " " + s.Employee.CostCenter.Svp.LastName : "",
    LOBMgrName = s.Employee.CostCenter.Lobmgr != null ? s.Employee.CostCenter.Lobmgr.FirstName + " " + s.Employee.CostCenter.Lobmgr.LastName : "",
    AdminApprovalStatus = s.AdminApprovalStatus.ApprovalStatusName,
    StatementStatus = s.StatementStatus.StatementStatusName,
    AmountDue = s.AmountDue
}).Where(s => s.StatementStatusId == "PAA").ToList();

但是运行时出错，提示无法将其转换为SQL。我认为问题出在.ToString()和日期格式化上。

所以我改成：

List myStatements = new List();
var statements = db.Statements.Where(s => s.StatementStatusId == "PAA").ToList();
foreach (var s in statements)
{
    StatementModel sm = new StatementModel();
    sm.StatmentId = s.StatementId;
    sm.EmployeeNumber = s.EmployeeNumber;
    sm.FirstName = s.Employee.FirstName;
    sm.LastName = s.Employee.LastName;
    sm.PlanType = s.Employee.PlanType != null ? s.Employee.PlanType.PlanTypeName : "";
    sm.FiscalPeriod = s.FiscalPeriod.StartDate.ToString("yyyy-MM-dd") + " - " + s.FiscalPeriod.EndDate.ToString("yyyy-MM-dd");
    sm.CostCenterId = s.Employee.CostCenterId;
    sm.RVPName = s.Employee.CostCenter.Evp != null ? s.Employee.CostCenter.Evp.FirstName + " " + s.Employee.CostCenter.Evp.LastName : "";
    sm.SVPName = s.Employee.CostCenter.Svp != null ? s.Employee.CostCenter.Svp.FirstName + " " + s.Employee.CostCenter.Svp.LastName : "";
    sm.LOBMgrName = s.Employee.CostCenter.Lobmgr != null ? s.Employee.CostCenter.Lobmgr.FirstName + " " + s.Employee.CostCenter.Lobmgr.LastName : "";
    sm.AdminApprovalStatus = s.AdminApprovalStatus.ApprovalStatusName;
    sm.StatementStatus = s.StatementStatus.StatementStatusName;
    sm.AmountDue = s.AmountDue;
    myStatements.Add(sm);
}

但是我得到了一堆空引用错误，例如Employee.CostCenter或FiscalPeriod.StartDate。

所以我又改成了：

var statements = db.Statements.Include("Employee.CostCenter.Evp")
              .Include("Employee.CostCenter.Svp")
              .Include("Employee.CostCenter.Lobmgr")
              .Include("FiscalPeriod")
              .Include("AdminApprovalStatus")
              .Include("StatementStatus").Where(s => s.StatementStatusId == "PAA").ToList();
foreach (var s in statements)
{
    StatementModel sm = new StatementModel();
    sm.StatmentId = s.StatementId;
    sm.EmployeeNumber = s.EmployeeNumber;
    sm.FirstName = s.Employee.FirstName;
    sm.LastName = s.Employee.LastName;
    sm.PlanType = s.Employee.PlanType != null ? s.Employee.PlanType.PlanTypeName : "";
    sm.FiscalPeriod = s.FiscalPeriod.StartDate.ToString("yyyy-MM-dd") + " - " + s.FiscalPeriod.EndDate.ToString("yyyy-MM-dd");
    sm.CostCenterId = s.Employee.CostCenterId;
    sm.RVPName = s.Employee.CostCenter.Evp != null ? s.Employee.CostCenter.Evp.FirstName + " " + s.Employee.CostCenter.Evp.LastName : "";
    sm.SVPName = s.Employee.CostCenter.Svp != null ? s.Employee.CostCenter.Svp.FirstName + " " + s.Employee.CostCenter.Svp.LastName : "";
    sm.LOBMgrName = s.Employee.CostCenter.Lobmgr != null ? s.Employee.CostCenter.Lobmgr.FirstName + " " + s.Employee.CostCenter.Lobmgr.LastName : "";
    sm.AdminApprovalStatus = s.AdminApprovalStatus.ApprovalStatusName;
    sm.StatementStatus = s.StatementStatus.StatementStatusName;
    sm.AmountDue = s.AmountDue;
    myStatements.Add(sm);
}

这样可以工作，但是非常冗长，我不记得在EF 6中需要明确地.Include东西，除非是优化问题。

这是在EF Core中唯一或最好的方法吗，还是我漏掉了什么？

0

1 答案

0 Comments

在EF Core 5中，是否必须使用`.Include`来加载子对象的原因是因为存在以下几个问题：

1. 初始尝试中的延迟SQL版本由于`DateTime.ToString()`语法错误而失败。

2. C#版本中的代码包含了大量的`.Include()`，混合了魔术字符串，而且非常低效。当将结果投影到DTO时，你将从网络中拉取可能有数百列的数据，然后忽略大部分列。

对于这样的问题，你可以考虑使用一种混合方法，即尊重并充分利用两个环境的优势。具体解决方法如下：

1. 将数据库元素按照逻辑需要进行投影，但保持原始格式，让EF和SQL一起优化输出。

List myStatements = new List();
var dbQuery = db.Statements.Select(s => new {
    s.StatementId,
    s.EmployeeNumber,
    s.Employee.FirstName,
    s.Employee.LastName,
    s.Employee.PlanType.PlanTypeName,
    s.FiscalPeriod.StartDate,
    s.FiscalPeriod.EndDate,
    s.Employee.CostCenterId,
    Evp = new {
        s.Employee.CostCenter.Evp.FirstName,
        s.Employee.CostCenter.Evp.LastName
    },
    Svp = new { 
        s.Employee.CostCenter.Svp.FirstName,
        s.Employee.CostCenter.Svp.LastName
    },
    LOBMgr = new { 
        s.Employee.CostCenter.Lobmgr.FirstName,
        s.Employee.CostCenter.Lobmgr.LastName
    },
    s.AdminApprovalStatus.ApprovalStatusName,
    s.StatementStatus.StatementStatusName,
    s.AmountDue
}).Where(s => s.StatementStatusId == "PAA");

2. 现在，在内存中将结果集投影到DTO中，以获得对类型转换和字符串格式化的完全C#控制。

myStatements = dbQuery.ToList()
                     .Select(s => new StatementModel
{
    StatmentId = s.StatementId,
    EmployeeNumber = s.EmployeeNumber,
    FirstName = s.FirstName,
    LastName = s.LastName,
    PlanType = s.PlanTypeName ?? "",
    FiscalPeriod = $"{s.StartDate:yyyy-MM-dd} - {s.EndDate:yyyy-MM-dd}",
    CostCenterId = s.CostCenterId,
    RVPName = $"{s.Evp.FirstName} {s.Evp.LastName}".Trim(),
    SVPName = $"{s.Svp.FirstName} {s.Svp.LastName}".Trim(),
    LOBMgrName = $"{s.LOBMgr.FirstName} {s.LOBMgr.LastName}".Trim(),
    AdminApprovalStatus = s.ApprovalStatusName,
    StatementStatus = s.StatementStatusName,
    AmountDue = s.AmountDue
}).ToList();

值得注意的是，代码中没有使用`Include`！我们用初始的投影替代了`Include`。个人觉得这段代码更加自然，`Include`可能会相当间接，不一定明确我们为什么需要它们，或者什么时候我们忘记添加它们。这段代码可能会让人感觉有些重复处理，但我们只获取需要的特定列，并且不会干扰干净的SQL，因为我们可以在C#中以最小的努力进行数据值的格式化。

避免在`Include`中使用魔术字符串是一个好的做法，而是使用Lambda表达式。这样可以让编译器在架构更改可能使查询无效时通知你或其他开发人员。

.Include(x => x.Employee.CostCenter.Evp)
.Include(x => x.Employee.CostCenter.Svp)
.Include(x => x.Employee.CostCenter.Lobmgr)
.Include(x => x.FiscalPeriod)
.Include(x => x.AdminApprovalStatus)
.Include(x => x.StatementStatus)

这样的解决方法不仅避免了使用`Include`，还使用初始的投影代替了`Include`。它提供了更大的灵活性和可控性，同时避免了不必要的数据传输和性能损耗。

0