How can I get the one to many relationship's many entity in nested response?

Question

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?

Code_maniac

View more on Stack Overflow

jpgrassi · Accepted Answer · 12/14/2018 10:22:33 AM

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

How can I get the one to many relationship's many entity in nested response?

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related