Entity Framework Core: Include many-to-many related objects in WebAPI

Question

I'm not too familiar with the .NET framework but decided to try out ASP.NET Core and EF Core. I want to make a pretty simple Web API backend but I'm having trouble working with many-to-many relationships.

I understand that I need to make a relationship table for the two entities, as in the example from this post: How to create a many to many relationship with latest nightly builds of EF Core?

I also have my model builder creating the relationship as described here http://ef.readthedocs.io/en/latest/modeling/relationships.html#many-to-many

My code looks like this:

in DBContext:

protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<MovieActor>().HasKey(x => new { x.MovieId, x.ActorId});

        builder.Entity<MovieActor>()
            .HasOne(m => m.Movie)
            .WithMany(ma => ma.MovieActors)
            .HasForeignKey(m => m.MovieId);

        builder.Entity<MovieActor>()
            .HasOne(m => m.Actor)
            .WithMany(ma => ma.MovieActors)
            .HasForeignKey(a => a.ActorId);

    }

    public DbSet<Movie> Movies { get; set; }

    public DbSet<Actor> Actors { get; set; }


}

Models:

namespace Test.Models
{
    public class Movie
    {
        public int MovieId { get; set; }
        public string Title { get; set; }
        public DateTime ReleaseDate { get; set; }
        public string Code { get; set; }
        public string Path { get; set; }

        public ICollection<MovieActor> MovieActors { get; set; }
    }

    public class Actor
    {
        public int ActorId { get; set; }
        public string Name { get; set; }
        public ICollection<MovieActor> MovieActors { get; set; }
    }

    public class MovieActor
    {
        public int MovieId { get; set; }
        public Movie Movie { get; set; }

        public int ActorId { get; set; }
        public Actor Actor { get; set; }
    }
}

This seems to be working for the most part. I can create new MovieActor objects and add them to the database. One thing that seems odd is the Movie and Actor models now have a MovieActors property but it's always null, despite the join table having entries.

The thing I can't figure out is how to retrieve these related objects in my WebAPI controllers. My Linq skills are very weak, so maybe I just need to brush up on that, but I don't know where to start. When I get a movie by ID, or get a list of all movies, I'd like the result to have any related Actors in an array on the object. The same goes in the other direction, when getting an Actor I want to get an array of their movies.

Here is one of my controller methods trying to make use of this relationship:

        // POST: api/Movie
    [HttpPost]
    public IActionResult PostMovie([FromBody] MovieBundle Moviebundle)
    {


        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        var _movie = _context.Movies.FirstOrDefault(m => m.Code == Moviebundle.Movie.Code);
        if(_movie == null)
        {
            //Doesn't exist! add a new one
            _movie = Moviebundle.Movie;
            _context.Movies.Add(_movie);
            _context.SaveChanges();

            foreach (Actor actor in Moviebundle.actors)
            {
                //Try to look up by name
                Actor _actor = _context.Actors.FirstOrDefault(a => a.Name == actor.Name);

                //If they don't exist, add them to the DB 
                if (_actor == null)
                {
                    _context.Actors.Add(actor);
                    _context.SaveChanges();
                    _actor = actor;
                }

                //This is what I'm using to create the M-t-M relationship
                var link = new MovieActor { Actor = _actor, Movie = _movie };
                _context.MovieActor.Add(link); //This line gives an error


            }


        } else
        {
            return new StatusCodeResult(StatusCodes.Status409Conflict);
        }




        try
        {
            _context.SaveChanges();
        }
        catch (DbUpdateException)
        {
            if (MovieExists(_movie.MovieID))
            {
                return new StatusCodeResult(StatusCodes.Status409Conflict);
            }
            else
            {
                throw;
            }
        }

        return CreatedAtAction("GetMovie", new { id = _movie.MovieID }, _movie);
    }

Sampath · Answer 1 · 2016-09-24T19:52:45.180

7

You have to change it as shown below.

Models :

namespace Test.Models
{
    public class Movie
    {
        public int MovieId { get; set; }
        public string Title { get; set; }
        public DateTime ReleaseDate { get; set; }
        public string Code { get; set; }
        public string Path { get; set; }

        public ICollection<MovieActor> MovieActors { get; set; }
    }

    public class Actor
    {
        public int ActorId { get; set; }
        public string Name { get; set; }
        public ICollection<MovieActor> MovieActors { get; set; }
    }

    public class MovieActor
    {
        public int MovieId { get; set; }
        public Movie Movie { get; set; }

        public int ActorId { get; set; }
        public Actor Actor { get; set; }
    }
}

Context :

protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<MovieActor>().HasKey(x => new { x.MovieId, x.ActorId});

        builder.Entity<MovieActor>()
            .HasOne(m => m.Movie)
            .WithMany(ma => ma.MovieActors)
            .HasForeignKey(m => m.MovieId);

        builder.Entity<MovieActor>()
            .HasOne(m => m.Actor)
            .WithMany(ma => ma.MovieActors)
            .HasForeignKey(a => a.ActorId);

    }

    public DbSet<Movie> Movies { get; set; }

    public DbSet<Actor> Actors { get; set; }

    public DbSet<MovieActor> MovieActors { get; set; }

 }

You can navigate as shown below :

var actor = context.Actors
                   .Include(r => r.MovieActors)
                   .FirstOrDefault(a=>a.ActorId = 1)

var movies = actor.MovieActors.Select(c => c.Movie);

How to add a record :

var actor = new Actor { ... };
context.Actors.Add(actor);

var movie = new Movie { ... };
context.Movies.Add(movie);

var movieActor = new MovieActor { Actor = actor , Movie = movie };
context.MovieActors.Add(movieActor);

context.SaveChanges();

edited Sep 24 '16 at 19:52

answered Sep 24 '16 at 05:08

Sampath

63,341
64
307
441

I've made these changes but now my controllers are unhappy. I can no longer create a new MovieActor entity and add it to the context, it says `'DbContext' does not contain a definition for 'MovieActor'`. How would I go about adding a relation here? What I have now creates a new Movie, then creates a new Actor, then creates a MovieActor entity with those objects. – yalibaba Sep 24 '16 at 15:02
Sure thing, I just added an example method from the controller. – yalibaba Sep 24 '16 at 16:03
try to find the Actor and add it to the list of actors in the corresponding movie – Kasparov92 Sep 24 '16 at 16:07
it seems you haven't implemented the changes which I have mentioned.that way we cannot find out the solution.b'cos if you see the `EF core` doc, they also has showed the same way like my post.if you unable to change according to that,hope you'll not have a solution for this issue. – Sampath Sep 24 '16 at 16:12
one example is this : `_context.Movie.FirstOrDefault(m => m.Code == Moviebundle.Movie.Code)`.this must be like this `_context.Movies.FirstOrDefault(m => m.Code == Moviebundle.Movie.Code)`.and so many others... – Sampath Sep 24 '16 at 16:13
I see, those were typos from changing the code, what I had worked fine. I fixed them in the pasted version. I have already changed the model and context as you suggested, but my question is how do I establish that relationship when something posts a movie object and a list of Actors? – yalibaba Sep 24 '16 at 16:30
can you update this section also ? `in DBContext:` section on your post ? – Sampath Sep 24 '16 at 16:32
please see this section **You can navigate as shown below** – Sampath Sep 24 '16 at 16:48
That's useful for navigation, but I'm specifically interested in creating the relationship. These lines in my original code throw an error now: `var link = new MovieActor { Actor = _actor, Movie = _movie }; _context.MovieActor.Add(link);` – yalibaba Sep 24 '16 at 17:02
you cannot do that.B'cos there is no `MovieActor` on the `context`. – Sampath Sep 24 '16 at 17:16
So what do I do instead? I had a MovieActor on the context before but you suggested I remove it, so I assume it's not the right way to do it. – yalibaba Sep 24 '16 at 17:43
Not me. suggested by `Microsoft`.did you see the `EF doc` ? can you tell me what is your requirement exactly ? – Sampath Sep 24 '16 at 17:44
I want to post a movie to my Web API. I want to know which actors are in a movie, and which movies an actor is in. I want a many-to-many relationship between my Movie and Actor tables. Let's assume the Microsoft suggestion sets up the data model as I need it. How do I actually use it? I can create a Movie, and I can create an Actor, but there's no actual relationship between the two. If MovieActors isn't in the context, then I can't create new entries directly to that table. My Movie and Actor entites have MovieActors properties but they're always null. How do I record this relationship? – yalibaba Sep 24 '16 at 17:48
on your `controller` ,can you tell me up to this line (`var link = new MovieActor { Actor = _actor, Movie = _movie };` ) all are working fine or what ? can you insert data to the both `Movies` and `Actors` tables ? – Sampath Sep 24 '16 at 17:57
Yes I can create a new Actor and I can create a new Movie, but I have nothing to populate the MovieActors field with. Everything works fine except for creating a new MovieActor, because it's not in the context anymore. If I remove that line it will run without errors and I'll get a Movie and a bunch of Actors that don't know about each other. – yalibaba Sep 24 '16 at 17:59
can you tell me what are the records now on your `MovieActors` table after inserting the data to the other 2 tables ? – Sampath Sep 24 '16 at 18:01
MovieActors table is empty. I don't understand how it could have anything in it, I created a Movie entity and one or more Actor entities. How could they know about each other? – yalibaba Sep 24 '16 at 18:15
please see the **How to add a record** section. – Sampath Sep 24 '16 at 18:46
you have to add `public DbSet MovieActors { get; set; }` to the `context` too :D – Sampath Sep 24 '16 at 18:50
That's fine and all, but it's just the way I had it set up when I first asked the question.... This creates entries in the MovieActors table, but Movie and Actor objects still have null MovieActors properties, so I can't navigate the way you suggested. Should I write some query to find all entries with a given movie's ID in the join table, get the corresponding actor IDs, and then return those Actor objects as a list? – yalibaba Sep 24 '16 at 19:28
updated.please see **You can navigate as shown below** section. – Sampath Sep 24 '16 at 19:53
hope this is working now.please `accept the solution`.you can see how to do that here.just scroll down it :http://stackoverflow.com/tour – Sampath Sep 26 '16 at 05:38
I'm using Asp.Net Core 1.1.2 and the "navigate" section doesn't work for me. In particular `var movies = actor.MovieActors.Select(c => c.Movie);` returns a set of null objects. What worked for me was: `var movies = context.MovieActors.Include(x => x.Movie).Include(x => x.Actor).Where(x => x.ActorId == actor.ActorId).Select(x => x.Movie);` – Samo Aug 23 '17 at 03:03

Entity Framework Core: Include many-to-many related objects in WebAPI

1 Answers1

Linked