grouping and flatening 1:M:M:1 related data in Entity Framework Core/LINQ

Question

In a small ASP.NET Core Web API using Entity Framework and LINQ I want to achieve a method which queries to which rooms a person needs access to. The desired result for a single person looks like that

{
  "id": 1,
  "firstname": "First1",
  "lastname": "Last1",
  "rooms": [
      {
        "id": 1,
        "name": "test room 1"
      },
      {
        "id": 2,
        "name": "test room 2"
      },
      {
        "id": 3,
        "name": "test room 3"
      }
    ]
}

Now the rooms are not directly assigned to a person but a person has multiple tasks (each task can have only one person), each task requieres access to multiple rooms (so there is a M:N between tasks and rooms). And the rooms table stores the room id and name.

So I have 4 tables:

1. Person (id, firstname, lastname)
2. Task (id, personId, name)
3. TaskRoom (id, roomId, taskId)
4. Room (id, name) -> the data that should be displayed

The connections between the tables are:

• Person 1:M Task
• Task 1:M TaskRoom
• TaskRoom N:1 Room

My current methods looks like that

[HttpGet("RoomAccess/{personId:int}")]
public IActionResult RoomAccess(int personId)
{
    _logger.LogDebug(string.Format("{0}.RoomAccess(person id: {1})", GetType().Name, personId));

    var result = _dbContext.Person
        .Include(p => p.Task).ThenInclude(t => t.TaskRoom).ThenInclude(tr => tr.Room)
        .Where(p => p.Id == personId)
        .Select(person => new
        {
            person.Id,
            person.Firstname,
            person.Lastname,

            // how to do that line?
            rooms = person.Task.Select(ta => ta.TaskRoom.Select(tr => new {id = tr.Room.Id, name = tr.Room.Name} ) )
        }
    )
    .FirstOrDefault();

    if (result == null) 
    {
        return NotFound(string.Format("person id: {0}", personId));
    }

    return Ok(result);
}

And the result so far:

{
  "id": 1,
  "firstname": "First1",
  "lastname": "Last1",
  "rooms": [
    [
      {
        "id": 1,
        "name": "test room 1"
      }
    ],
    [
      {
        "id": 2,
        "name": "test room 2"
      },
      {
        "id": 3,
        "name": "test room 3"
      }
    ],
    [
      {
        "id": 3,
        "name": "test room 3"
      }
    ],
    []
  ]
}

Here are also empty brackets [] at the end for a task to which no room is assigned (so far). And test room 3 occurs multiple times since two different tasks require access to this room.

How do I get a grouped list of related rooms holding only the id and name of the room (like in the example for the desired result)

Any help is welcome.

---

Now there is an open issue on EF Core Github

---

Answer

Well the working code is:

[HttpGet("RoomAccess/{personId:int}")]
        public IActionResult RoomAccess(int personId)
        {
            _logger.LogDebug(string.Format("{0}.RoomAccess(person id: {1})", GetType().Name, personId));

            var result = _dbContext.Person
                .Include(p => p.Task).ThenInclude(t => t.TaskRoom).ThenInclude(tr => tr.Room)
                .Where(p => p.Id == personId)
                .ToList()
                .Select(person => new
                    {
                        person.Id,
                        person.Firstname,
                        person.Lastname,

                        rooms = person.Task.SelectMany(ta => ta.TaskRoom.Select(
                            tr => new { id = tr.Room.Id, name = tr.Room.Name })
                        )
                        .Distinct()
                        .OrderBy(adt => adt.name)
                    }
                )
                .FirstOrDefault();


            if (result == null) 
            {
                return NotFound(string.Format("person id: {0}", personId));
            }

            return Ok(result);
        }

In addition to the answer of Nkosi it is important to use Include() and ThenInclude() as well as .ToList() after the Where().

Answer 1

Use the SelectMany extension to flatten the lists.

//...
rooms = person.Task
    .SelectMany(ta => ta.TaskRoom.Select(tr => new {id = tr.Room.Id, name = tr.Room.Name}))
    .Distinct()
//...

Not sure how it will get transposed in EF though and any performance impact.

Note the use of Distinct to remove any duplicates.