Why is Entity Framework Core eager loading Related Entities that aren't Included

user_10887592 0
Viewed 2477

I'm working on creating an ASP.NET Core Web API and have encountered a problem when querying the context.

The Web API connects to a database with 3 tables: Employees, Events and Event Responsibilities. An employee can create many events. Additionally, many employees can be assigned responsibilities many events in each of those events (Hence the linking Event Responsibility table).

Employee Table

+----+-----------------+
| Id |      Name       |
+----+-----------------+
|  1 | First Employee  |
|  2 | Second Employee |
+----+-----------------+

Event Table

+----+------------+---------------+
| Id | Creator_Id |  Event_Name   |
+----+------------+---------------+
|  1 |          1 | Random Event  |
|  2 |          1 | Another Event |
+----+------------+---------------+

Event Responsibility Table

+----+-------------+----------+
| Id | Employee_Id | Event_Id |
+----+-------------+----------+
|  1 |           1 |        1 |
+----+-------------+----------+

The models of these include...

Event Model

An event can only have an employee as a creator. An event can also have many employees that hold different responsibilities.

public int Id { get; set; }
public int? CreatorId { get; set; }
public string EventName { get; set; }

public Employee Creator { get; set; }
public ICollection<EventResponsibilities> EventResponsibilities { get; set; }

Employee Model

An employee can be the creator of many events. An employee can have responsibilities in many events.

public int Id { get; set; }
public string Name { get; set; }

public ICollection<Event> Event { get; set; }
public ICollection<EventResponsibilities> EventResponsibilities { get; set; }

Event_Responsibilities Model

A many to many linking table model.

public int Id { get; set; }
public int? EmployeeId { get; set; }
public int? EventId { get; set; }

public Employee Employee { get; set; }
public Event Event { get; set; }

I have run the following LINQ query in a controller method...

return _context.Event.Include(e => e.EventResponsibilities).ThenInclude(e => e.Employee);

I expect to get a JSON which has details of the Event, its Corresponding Event Responsibilities and associated Employee for that Responsibility.

What I actually get is the following json...

[
  {
    "Id": 1,
    "CreatorId": 1,
    "EventName": "Random Event",
    "Creator": {
      "Id": 1,
      "Name": "First Employee",
      "Event": [],
      "EventResponsibilities": [
        {
          "Id": 1,
          "EmployeeId": 1,
          "EventId": 1
        }
      ]
    },
    "EventResponsibilities": [
      {
        "Id": 1,
        "EmployeeId": 1,
        "EventId": 1,
        "Employee": {
          "Id": 1,
          "Name": "First Employee",
          "Event": [],
          "EventResponsibilities": []
        }
      }
    ]
  },
  {
    "Id": 2,
    "CreatorId": 1,
    "EventName": "Another Event",
    "Creator": {
      "Id": 1,
      "Name": "First Employee",
      "Event": [
        {
          "Id": 1,
          "CreatorId": 1,
          "EventName": "Random Event",
          "EventResponsibilities": [
            {
              "Id": 1,
              "EmployeeId": 1,
              "EventId": 1
            }
          ]
        }
      ],
      "EventResponsibilities": [
        {
          "Id": 1,
          "EmployeeId": 1,
          "EventId": 1,
          "Event": {
            "Id": 1,
            "CreatorId": 1,
            "EventName": "Random Event",
            "EventResponsibilities": []
          }
        }
      ]
    },
    "EventResponsibilities": []
  }
]

This is not what I wanted or expected... As you may see, for some reason the Creator navigation property for the Event model is being shown even though I have not requested it in the query as I have not used Include() for it.

Additionally, If you look at at the second returned Event in the JSON ("Id": 2), Even though there are no EventResponsibilities, there are two Creator Rows that are returned. I don't think this is a lazy loading problem as the navigation properties aren't virtual.

I would have expected the Creator object to be null or empty since I did not include it.

So my question is: Why is the Creator navigation property being included even if I did not load/Include it? In addition, I would also like to know how to not include the related Creator data and only display the Event Responsibilities data in the JSON.

asp.net-core ef-core-2.1 json.net c#
1 Answers

If you look at the documentation, there's this specific note that is actually the trap you fell into:

Entity Framework Core will automatically fix-up navigation properties to any other entities that were previously loaded into the context instance. So even if you don't explicitly include the data for a navigation property, the property may still be populated if some or all of the related entities were previously loaded.

By doing

.ThenInclude(e => e.Employee);

You request the EF to fetch this Employee thus causing it to be eager-loaded wherever it's referenced. And because in this specific case your Event.Creator is the same as EventResponsibilities.Employee you explicitly requested, after it's fetched into EventResponsibilities it's also fetched into your Event. If your Event.Creator was a different Employee than the EeventsResponsibilities.Employee you wouldn't experience this abnormality.

Such kind of unexpected behaviors and redundant data is one of the reasons people use separate DTOs for returning data from controller to make sure API returns just the right amount of data needed.

As a quick workaround, you could simply add JsonIgnoreAttribute on your Creator property:

[JsonIgnore]
public Employee Creator { get; set; }

But personally I don't think mixing up DTO and Entity in the same class is a very good idea as that will cause all sorts of troubles whenever any of entities will change.

user_41284010
Related