Entity Framework Core and including child collections

Question

Essentially what I am trying to do is query child collections of parent entities. According to the EF Core documentation, I can use the Include and ThenInclude methods to do that (https://learn.microsoft.com/en-us/ef/core/querying/related-data). So, I have this code in my repository to get the data I want:

public object GetMatchupByVideoId(int id)
{
    var videoMatchup = _DBContext.Matchups
        .Where(m => m.VideoID == id)
        .Include(m => m.Players).ThenInclude(p => p.Character)
        .Include(m => m.Players).ThenInclude(p => p.User);

    return videoMatchup;
}

This code essentially looks for the Matchups entity that matches the id passed. It then goes out and includes the Players collection, which is a navigation property on the model. The data returned looks like this:

[
  {
    "id": 1,
    "players": [
      {
        "id": 1,
        "user": {
          "id": 1,
          "displayName": "Player 1"
        },
        "character": {
          "id": 40,
          "name": "Superman"
        },
        "outcome": 0
      },
      {
        "id": 2,
        "user": {
          "id": 2,
          "displayName": "Player 2"
        },
        "character": {
          "id": 43,
          "name": "Batman"
        },
        "outcome": 1
      }
    ]
  }
]

This is all well and good, but it gives me more data than what I am really interested in. For instance, I would rather have my user property to just have a value of the displayName property. Other than that, this is the data structure I want, I just want to change some of the properties.

Looking around for the answer I have found that some people recommend using SelectMany. So, when I rearrange my code to this:

public object GetMatchupByVideoId(int id)
{
    var videoMatchup = _DBContext.Matchups
        .Where(m => m.VideoID == id)
        .SelectMany(m => m.Players, (parent, child) => new { parent, child })
        .Select(pc => new {
            players = pc.child
        });

    return videoMatchup;
}

The data returned looks like this:

[
  {
    "players": {
      "id": 1,
      "user": null,
      "character": null,
      "outcome": 0
    }
  },
  {
    "players": {
      "id": 2,
      "user": null,
      "character": null,
      "outcome": 1
    }
  }
]

This has a few downsides. The first is that none of the related data is loaded and their are two separate objects for players when it should just be in a collection.

So, I have a couple of questions which I believe are all related to the same answer:

• Is there a way that I can query for child data without using Include? I find it hard to believe that this is the only way.
• How do I reconcile my objects into one list with the SelectMany method like the first data structure returned?
• How do I get more granular control over changing properties when loading child collections in this way?
• What if I have more than one child entity to load?

Answer 1

All of the questions that I posed were in relation to one single answer as I suspected. After searching around and working through it, here is how you could accomplish this:

public object GetMatchupByVideoId(int id)
{
    var videoMatchup = _DBContext.Matchups
        .Where(m => m.VideoID == id)
        .Select(m => new {
            ID = m.VideoID,
            Players = m.Players.Select(p => new {
                ID = p.PlayerID,
                User = p.User,
                Character = p.Character,
                Outcome = p.Outcome
            })
        });

    return videoMatchup;
}

SelectMany is not the right approach here. What you want is to use Select and use projection off of your child list. This answers the following questions I posed:

• Is there a way that I can query for child data without using Include? I find it hard to believe that this is the only way.

Just use the Select method to project off of your child list into your navigation property.

• How do I reconcile my objects into one list with the SelectMany method like the first data structure returned?

In this case you wouldn't because SelectMany is used to flatten a list of lists.

• How do I get more granular control over changing properties when loading child collections in this way?

Same as the first answer.

• What if I have more than one child entity to load?

Same as the first answer.