Do you have to use .Include to load child objects in EF Core 5?

Question

I'm used to EF 6, I'm new to EF Core. I'm trying to do a simple fetch from a table which has some foreign key relationships to lookup tables.

I started with this:

List<StatementModel> myStatements = new List<StatementModel>();

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

But it gave me a runtime error saying it couldn't translate this into SQL. I think it's a problem with .ToString() and the date formatting.

So I changed it to:

List<StatementModel> myStatements = new List<StatementModel>();
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);
}

But then I got a bunch of null reference errors for any of the child objects like Employee.CostCenter or FiscalPeriod.StartDate.

So then I changed it to:

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

This works, but it's very verbose and I don't recall ever having to explicitly .Include things in EF 6 unless it was an optimization issue.

Is this the only or best way to do it in EF Core or am I missing something?

`This works, but it's very verbose and I don't recall ever having to explicitly .Include things in EF 6 unless it was an optimization issue` That is because Lazy Loading was enabled by default on a DbContext instance in EF6. If you disabled this then you would also have to use .Include. — Igor, Jun 17 '21 at 13:03
There are much simpler ways to do this. You should have stayed with the projection version, then Includes aren't an issue, just like you expect. There is also an include version that is much less verbose however, now that this is closed I can't post my solution. — Chris Schaller, Jun 17 '21 at 13:20
I would re-post this question without the `.Include()` work around, you will get a better overall solution — Chris Schaller, Jun 17 '21 at 13:22
@ChrisSchaller - if you still want to post a solution to the very first exception received with projection let me know, I will reopen the question. — Igor, Jun 17 '21 at 14:00
@Legion - See also [canonical functions](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/canonical-functions) which can help you figure out what is and is not supported when writing code that needs to be translated into sql by linq to entities. — Igor, Jun 17 '21 at 14:02
None of the dupes cover projections and specifically 2 stage SQL then local projections which provide a very elegant solution to this very specific issue which I think would be useful, I'm happy to acknowledge the existing dup references but I think there is more we can add instead of just doing things from first principals. — Chris Schaller, Jun 17 '21 at 14:06
Without the original Exception I am going to guess that the line that is causing problems is "FiscalPeriod = s.FiscalPeriod.StartDate.ToString("yyyy-MM-dd") + " - " + s.FiscalPeriod.EndDate.ToString("yyyy-MM-dd")," This is because you can't call ToString with a format on the DateTime. To fix this I would add 2 properties of type DateTime to your model named FiscalStart and FiscalEnd and assign each the corresponding values. Then add a new property of type `string` that returns the in memory string of what you tried to do earlier. The value will no longer be dependent on projection. — Igor, Jun 17 '21 at 14:21
There might also be additional issues with the code but I would start there and see if that fixes it. — Igor, Jun 17 '21 at 14:22
@Igor we can use an anonymous type to project into first, that way OP doesn't have to add any properties to the DTO model, check out my answer, I think we're on the same page. — Chris Schaller, Jun 17 '21 at 14:56
@ChrisSchaller there are many ways to skin a cat. Your answer is also perfectly acceptable. For the OP: projection can lead to a better query over using includes but I would always check using Sql Profiler and note the number of reads and duration as well as the query itself. It might be OK on your machine but slow on production due to any number of reasons like volume of data. This is just a side note though. — Igor, Jun 17 '21 at 14:59

score 2 · Accepted Answer · answered Jun 17 '21 at 14:53

There are a few separate issues here, however the overall problem is that you are stuck trying to pick either the database (SQL) to manage the DTO projection OR the local C# runtime.

Your deferred SQL version (the initial attempt) failed due to the incorrect DateTime.ToString() syntax.
The C# version is verbose in terms of .Include(), mixed with magic strings, but also very inefficient, you are pulling back potentially hundreds of columns across the wire, only to ignore most of the columns when you project the results into the DTO

For assistance specifically on .Include please review these previous posts:

Lazy Loading not working in EntityFramework Core

Entity Framework - what's the difference between using Include/eager loading and lazy loading?

.Include() vs .Load() performance in EntityFramework

Lazy Loading vs Eager Loading

For problems like this you should consider a Hybrid approach, one that respects and exploits the strengths of both environments.

Project the DB elements that your logic needs, but keep them in the raw format, let EF and SQL work together to optimise the output.

NOTE: There is no need to formalise this projection model, instead we can simply use an anonymous type!

 List<StatementModel> myStatements = new List<StatementModel>();

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

It is not necessary to nest the Evp,Svp,LOBMgr projections, you could flatten the entire resultset, it would actually be ever so slightly more efficient, but this shows the possibilities

Now project the result set into DTOs in memory, in this way you get total C# control over the type casting and string formatting.

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

Notice there are NO includes! we relace the Includes with the initial Projection. IMO I find this code is more natural, includes can be quite indirect, its not always obvious why we need them or when we've forgotten to add them at all. This code feels like double handling a bit, but we only bring back the specific columns we need and don't have to get in the way of clean SQL by trying to get the database to format the data values when we can do that with minimal effort in C#.

Date formatting is trivial, but this technique can be very powerful if you need to perform complex formatting or other processing logic on the results that you already have in c# without replicating that logic into SQL friendly Linq.

Avoid Magic strings in Includes

If you are going to use Includes, you should try to avoid the string variant of include and instead use the lambdas. This will allow the compiler to notify you or later devs when the schema changes might invalidate your query:

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

Do you have to use .Include to load child objects in EF Core 5?

1 Answers1

Avoid Magic strings in Includes