EntityFramework Core select M:N related data

Question

I seem to have a big misunderstanding of how EF Core / Linq / Navigation Properties work.

I tried to extend my example from a previous question adding a m:n relationship.

Database tables:

1. Person {Id (in), Firstname (nvarchar), Lastname (nvarchar) }
2. Group {Id (int), Name (string) }
3. GroupAssignment {Id (int), PersonId (int), GroupId (int) }

Database data: The person with Id 1 is assigned to the groups 1 and 3.

My query returns as expected the linked GroupAssignments:

var result = from person in _dbContext.Person
            select new
            {
                id = person.Id,
                firstname = person.Firstname,
                lastname = person.Lastname,
                groupAssignments = person.GroupAssignment  
            };

return Ok(result);

But I want to get a list with the fields of the N table (Groups). The result I am looking for is

[
{
    "id": 1,
    "firstname": "First1",
    "lastname": "Last1",
    "groupAssignments": 
    [
         {
         "id": 1,
         "name": "test group 1"
         },
         {
         "id": 3,
         "name": "test group 3"
        }
    ]
}
]

---

By the way: I would be really happy if you post some good reading links about EF (core) and linq into the comments. It seems I have a lot of beginner problems.

Answer 1

You might have a Group navigation property in GroupAssigment entity . If that is the case use Select extension method:

var result = from person in _dbContext.Person
            select new
            {
                id = person.Id,
                firstname = person.Firstname,
                lastname = person.Lastname,
                groupAssignments= person.GroupAssignment.Select(ga=>ga.Group)  
            };

return Ok(result);

About documentation, you can start here.

Update

To achieve what you commented below you can use an anonymous type to project only those two properties:

groupAssignments= person.GroupAssignment.Select(ga=>new{id=ga.Group.Id,name=ga.Group.Name})