Can't get the expected result in many to many relationship using LINQ in Asp.net C#, MVC, Codefirst application

Question

I'm trying to develop an application using Asp.net C#, MVC, Codefirst approach.

I have two classes as Actors and Movies, which has many to many relationships. (An actor can have many movies, a movie can have many actors)

Now i'm facing a problem, I can retrieve data from both tables but not as expected, I believe something is going wrong at somewhere with the logic of my code.

For instance, I can retrieve a list of movies from database and when i click on each movie it redirects me to the page that belongs to that particular movie which makes sense up to this point, but now i want to retrieve the list of actors that has played role in this particular movie and here i fail. (I can retrieve the whole list of actors that exist in database but not the particular actors that has played role in a particular movie).

These are my models:

This is the Actor Class:

public class Actors
{
    public int Id { get; set; }
    public string actor_name { get; set; }
    public string country_of_birth { get; set; }    
}

And this is the movies class:

public class Movies
{
   public int Id { get; set; }
   public string movie_title { get; set; }
   public string genre { get; set; }
}

This is the controller:

 public ActionResult Index()
 {
     var mv = _context.mvz;
     if (mv==null)
     {
         return Content("Nothing found in database");
     }
        return View(mv);
 }

 public ActionResult Details(int? Id)
 {
     var dtl = _context.mvz.SingleOrDefault(a=> a.Id == Id);
     var vm = new MoviesViewModel()
     {
         mvz = dtl,
     };
     if (vm==null)
     {
         return Content("Nothing Found!");
     }
        return View(vm);
  }

This is the Index View which shows the list of Movies and has a link for the details of each movie individually:

@model IEnumerable<ManyToMany_FinalProject.Models.Movies>
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout.cshtml";
}

<h5>List of Movies</h5>

<table class="table table-bordered table-hover table-responsive">
<thead>
    <tr>
        <th>Id</th>
        <th>Title</th>
    </tr>
</thead>
@foreach (var movie in Model)
{
    <tbody>
        <tr>
            <td>@movie.Id</td>
            <td>@Html.ActionLink(movie.movie_title,"Details","Movies",new { Id = @movie.Id},null)</td>      
        </tr>
    </tbody>
}

This is the the details view that has to shows the detailed information of of each movie, very particularly It has to show the list of Actors that has played role in that particular movie.

@model ManyToMany_FinalProject.ViewModel.MoviesViewModel
@{
ViewBag.Title = "Details";
Layout = "~/Views/Shared/_Layout.cshtml";
}

<h5>Details of @Model.mvz.movie_title</h5>

<table class="table table-bordered table-hover table-responsive">
<thead>
    <tr>
        <th>Id</th>
        <th>Title</th>
        <th>Genre</th>
    </tr>
</thead>

<tbody>
    <tr>
        <td>@Model.mvz.Id</td>
        <td>@Model.mvz.movie_title</td>
        <td>@Model.mvz.genre</td>
    </tr>
</tbody>

<h5>Star cast of @Model.mvz.movie_title</h5>

<table class="table table-bordered table-hover table-responsive">
<thead>
    <tr>
        <th>Id</th>
        <th>Name</th>
        <th>Country</th>
    </tr>
    </thead>
   @foreach (var actor in Model.act)
  {
    <tbody>
        <tr>
            <td>@actor.Id</td>
            <td>@actor.actor_name</td>
            <td>@actor.country_of_birth</td>
        </tr>
    </tbody>
  }
</table>

And Finally this is the View Model:

 public class MoviesViewModel
{
    public Movies mvz { get; set; }
    public List<Actors> act { get; set; }
    public MoviesViewModel()
    {
        mvz = new Movies();
        act = new List<Actors>();
    }
}

I know that something is wrong with the LINQ logic that I'm writing to retrieve the list of actors in the controller class.

If anyone having expertise in this regard is reading this post, kindly guide me with the correct logic that i should use for LINQ or any other approach that helps me get the result.

Answer 1

1) You have to configure many-to-may relationship in between your both model Actors and Movies

You will follow steps to Configure Many-To-Many Relationship in Code First.

After you configure relationship your models look like.

public class Actors
{
    public Actors()
    {
        this.Movies = new HashSet<Movies>();
    }

    public int Id { get; set; }
    public string actor_name { get; set; }
    public string country_of_birth { get; set; }
    public virtual ICollection<Movies> Movies { get; set; }
}

public class Movies
{
    public Movies()
    {
        this.Actors = new HashSet<Actors>();
    }

    public int Id { get; set; }
    public string movie_title { get; set; }
    public string genre { get; set; }
    public virtual ICollection<Actors> Actors { get; set; }
}

And your context class will be look like

public class YourDBContext : DBContext
{
    public YourDBContext() : base("YourDbConnectionString")
    {
    }

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

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //If you face any problem that contains cascade delete then add below line.
        modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

        base.OnModelCreating(modelBuilder);
    }
}

2) Change your MoviesViewModel according to below.

public class MoviesViewModel
{
    public Movies mvz { get; set; }
    public List<Actors> actrs { get; set; }
}

3) And then you will get your movie and its actors by below action method.

public ActionResult Details(int? Id)
{
    //This will return single movie with your parameter id
    var mvz = _context.mvz.SingleOrDefault(a => a.Id == Id);

    //This will gives you actors of above movie. 
    var actrs = mvz.Actors.ToList();

    var vm = new MoviesViewModel()
    {
        mvz = mvz,
        actrs = actrs
    };

    if (vm == null)
    {
        return Content("Nothing Found!");
    }

    //This view model return your both of movie and its actors.
    //Capture both in your model and render in your view.
    return View(vm);
}

Try once may it help you :)