How can I get the one to many relationship's many entity in nested response?
c# entity-framework entity-framework-core
Question
I have a class Movie
public class Movie
{
public int Id { get; set; }
public string Name { get; set; }
public Producer ProducerName { get; set; }
}
and a class Producer
public class Producer
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Movie> Movies { get; set; }
}
They have a one to many relationship from Producer to Movie i.e a producer can have multiple movies and a movie has only one producer.
I am getting the Movie details with below query.
var result = _context.Movies
.Include(m => m.ProducerName)
.ToList();
I get below Json:
{
"id": 1,
"name": "Venom",
"producerName": {
"id": 2,
"name": "Amy Pascal",
"movies": [
{
"id": 1,
"name": "Venom"
}
]
}
}
I want all the movies associated with the producer in the same query?
Like
{
"id": 1,
"name": "Venom",
"producerName": {
"id": 2,
"name": "Amy Pascal",
"movies": [
{
"id": 1,
"name": "Venom"
},
{
"id": 56,
"name": "something"
},
{
"id": 81,
"name": "else"
}
]
}
}
How can I do that?
EDIT: Using the query in one of the answer, I only receive a single movie of the producer(the current one) and not all of them. I see the database and find that even though it is a one to many relationship from producer to movie, there are multiple rows inserted for the same producer(with different primary keys). My question differs a little here. I am seeding the database using a json. The IDs(PK) are database generated.
JSON used to seed the database:
[
{
"Name": "Venom",
"ProducerName":
{
"Name": "Steven Spielberg"
}
},
{
"Name": "Kung Fu League",
"ProducerName":
{
"Name": "Steven Spielberg"
}
}
]
How can I constraint the EF Core to not create duplicate rows for the same producer and not a generate a different key for same producer as then I might receive all the movies of a single producer successfully?
Accepted Answer
You can chain includes. Start first from Movies then Include Producers and finally use ThenInclude to include the movies of the producers.
Update:
As pointed out by @Gert Arnold when projecting (not returning your entities), Includes are ignored by EF Core so, if going with my option of projecting into something else you can remove the Includes.
So:
// use .Include and .ThenInclude when returning your entities
var returningEntities = context.Movies
.Include(p => p.ProducerName)
.ThenInclude(m => m.Movies).ToList();
// No need for Include when use projection
var returningAnonymousObject = context.Movies
.Select(a => new
{
id = a.Id,
name = a.Name,
producer = new
{
id = a.ProducerName.Id,
name = a.ProducerName.Name,
movies = a.ProducerName.Movies.Select(m => new
{
id = m.Id,
name = m.Name
})
}
}).ToList();
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow