Deleting records in many to many relation

65 views Asked by At

When I delete movie i get error about foreign foreign key. And my question is How to delete all records from the table MovieActor with ID movie that I want to remove? And at the end movie from Movie table? its impossible to write code that would do this automatically when I remove the film?

Actors.cs

[Table("Actor")]
    public partial class Actor
    {
        public Actor()
        {
            Movie = new HashSet<Movie>();
        }

        public int ID { get; set; }

        [Required]
        [StringLength(50)]
        public string FirstName { get; set; }

        [Required]
        [StringLength(50)]
        public string LastName { get; set; }

        public virtual ICollection<Movie> Movie { get; set; }
    }

Movie.cs

[Table("Movie")]
    public partial class Movie
    {
        public Movie()
        {
            Actors = new HashSet<Actor>();
            Countries = new HashSet<Country>();
            Directors = new HashSet<Director>();
            Genres = new HashSet<Genre>();
            Writers = new HashSet<Writer>();
        }

        public int ID { get; set; }

        [Required]
        [StringLength(50)]
        public string Title { get; set; }

        [StringLength(50)]
        public string Title2 { get; set; }

        public int Year { get; set; }

        [StringLength(255)]
        public string Description { get; set; }

        public int Lenght { get; set; }

        public string Poster { get; set; }

        public DateTime? DateAdded { get; set; }

        public long? BoxOffice { get; set; }

        public string TrailerLink { get; set; }

        public virtual ICollection<Actor> Actors { get; set; }

    }

Context.cs

 public partial class Context : DbContext
    {

         public Context()
            : base("name=DbModel")
        {
        }


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


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Actor>()
                .HasMany(e => e.Movie)
                .WithMany(e => e.Actors)
                .Map(m => m.ToTable("MovieActor").MapLeftKey("ActorID").MapRightKey("MovieID"));
    }
2

There are 2 answers

5
Oleg On BEST ANSWER

Try this

using(var context = new Context())
{   
    var movie = context.Movies.Single(x=>x.ID == movieID);

    var actors = context.Actors.Where(x=>x.Movie.Any(y=>y.ID == movie.ID));

    foreach(var actor in actors)
    {
        actor.Movies.Remove(movie);
    }

    context.SaveChanges();
}
0
Kvatras On

As far as I know, this is a problem which comes when you make a many-to-many relationship, because you can't delete anything because both are referencing each other.

A solution would be to make another table (let's say Roles, in your case) which would be a one-to-many relationship with both Actor and Movie. Check many-to-many relationship in database design for more details.