Entity Framework also Linq advice needed

Question

i have got on my DB 3 tables movies, workers, workermovies ( this is the Relationship table )

public class Movie
{
    public Movie()
    {
        Genres = new List<Genre>();
        Formats = new List<Format>();
        ProductionCompanies = new List<ProductionCompany>();
        Workers = new List<Worker>();
    }

    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime ReleaseDate { get; set; }
    public string StoryLine { get; set; }
    public int RunTime { get; set; }

    [ForeignKey("MPAARateId")]
    public MPAARate MPAARate { get; set; }
    public int MPAARateId { get; set; }

    public byte[] ImageData { get; set; }
    public string ImageMimeType { get; set; }
    public DateTime CreatedDate { get; set; }
    public string OfficialSite { get; set; }
    public int Budget { get; set; }
    public int StatusId { get; set; }

    public virtual ICollection<Genre> Genres { get; set; }
    public virtual ICollection<Format> Formats { get; set; }
    public virtual ICollection<ProductionCompany> ProductionCompanies { get; set; }
    public virtual ICollection<Worker> Workers { get; set; }
}


public class Worker
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }
    public DateTime Birthday { get; set; } 
    public string Biography { get; set; } 
    public string BornName { get; set; } 
    public double Height { get; set; } 
    public DateTime? Died { get; set; } 
    public byte[] ImageData { get; set; } 
    public string ImageMimeType { get; set; } 
    public bool IsActor { get; set; }
    public bool IsDirector { get; set; } 
    public bool IsWriter { get; set; } 
    public bool IsProducer { get; set; }
    public bool IsStar { get; set; }

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

in this Relation i got the movieId and the workerId but i also got some more fields if the person acted or writen or producer etc.

how do i define the relation entity class if needed
and when i want to get just the ppl that acted in the movie how do i wrote such a linq query

A tip on the side: If you wan't to get just the ppl that acted in the movie, you'll need to change your schema. Because not everyone who is an actor, has acted in all movies they've worked with. (Like Mel Gibson didn't star in Apocalypto, but he directed it...) — Arjan Einbu, Oct 02 '11 at 10:05

score 0 · Answer 1 · edited May 23 '17 at 11:55

You need to introduce an additional entity in your model WorkerMovie and convert the many-to-many relationship between Worker and Movie into two one-to-many relationships - one between Worker and WorkerMovie and the other between Movie and WorkerMovie. A sketch:

public class WorkerMovie
{
    [Key, Column(Order = 0)]
    public int WorkerId { get; set; }
    [Key, Column(Order = 1)]
    public int MovieId { get; set; }

    public Worker Worker { get; set; }
    public Movie Movie { get; set; }

    public bool WorkedAsActor { get; set; }
    public bool WorkedAsWriter { get; set; }
    public bool WorkedAsProducer { get; set; }
    // etc.
}

public class Movie
{
    // ...
    public virtual ICollection<WorkerMovie> WorkerMovies { get; set; }
    // remove ICollection<Worker> Workers

}

public class Worker
{
    // ...
    public virtual ICollection<WorkerMovie> WorkerMovies { get; set; }
    // remove ICollection<Movie> Movies
}

If you want only to find the workers who were actors in a particular movie with a movieId you can write:

var workersAsActors = context.WorkerMovies
    .Where(wm => wm.MovieId == movieId && wm.WorkedAsActor)
    .Select(wm => wm.Worker)
    .ToList();

Here is another answer to a very similar question with many more examples of possible queries: Create code first, many to many, with additional fields in association table

Entity Framework also Linq advice needed

1 Answers1