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?
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();