Can not access the composite key table through a view, in order to enter data or assign the correct primary key in that table?

Question

I'm trying to develop an ASP.NET MVC application using C# and EF with a code-first approach.

I have two classes Actors and Movies which have many-to-many relationships (an Actor can be in many Movies, a Movie can have many Actors).

Everything is working properly, but I can not access the intermediate (MoviesActors) table through a view (this table is generated automatically as a result of the many-to-many relationship between Actors and Movies). I want to access this table so that I could assign the correct primary keys to determine things like "How many movies does a particular actor have?" and "How many actors have played a role in a particular movie?".

These are my models:

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

    public int Id { get; set; }
    public string actor_name { get; set; }
    public string country { get; set; }

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

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

    public int Id { get; set; }
    public string Movie_title { get; set; }
    public string genre { get; set; }

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

I have two models as above and three tables in database.

Now from a view, I can access the actors model and the movies model but when it comes to the intermediate table (MoviesActors), I can not access its model because it does not exist, which means I can not reference MoviesActors in a view and can't enter data through a form.

I would like to ask if my approach is correct or not, I mean in a real world application do we have to access the composite key table and do the data entry, or is there any better approach to solve this issue?

By the way this is the controller:

    public ActionResult DataEntry()
    {
        return View();
    }

    [HttpPost]
    public ActionResult Add(??? mva)
    {
        _context.act.Add(mva.act);
        _context.mvz.Add(mva.mvz);
        _context.SaveChanges();
        return RedirectToAction("Index","Actors");
    }

And this is the view:

  @model Many_To_Many_Relationship_Latest.?.?
  @{
      ViewBag.Title = "DataEntry";
      Layout = "~/Views/Shared/_Layout.cshtml";
  }

At the top, I should be able to access the MoviesActors table via Model or ViewModel (Check the two places with question marks) but I'm not able to do so.

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

Many thanks in advance.

This is my new razor view

    @model Many_To_Many_Relationship_Latest.ViewModel.MoviesActorsViewModel

    <script src="https://code.jquery.com/jquery-3.3.1.min.js"
    integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8="
    crossorigin="anonymous"></script>



         @{
         ViewBag.Title = "DataEntry";
         Layout = "~/Views/Shared/_Layout.cshtml";
         }

         <h5>Enter Data in Movies_Actors Table</h5>

         @using (Html.BeginForm("Add","MoviesActors"))
         {
       <div class="form-group">
       @Html.LabelFor(a=> a.mvz.Movie_title)
    @Html.TextBoxFor(a=> a.mvz.Movie_title, new { @class="form-control"})
</div>
<div class="form-group">
    @Html.LabelFor(a => a.mvz.genre)
    @Html.TextBoxFor(a => a.mvz.genre, new { @class = "form-control" })
</div>



foreach (var item in Model.act)
{
    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <div class="checkbox control-group">
                <label>
                    <!-- SEE id property here, I've made it dynamic -->
                    <input type="checkbox" id="cb_@item.Id" value="@item.Id" class="checkBoxClass" />
                    <label>@item.actor_name</label>
                </label>
            </div>
        </div>
    </div>
}

<div>
    <input type="hidden" name="Ids" id="Ids" />
</div>


<button type="submit" class="btn btn-primary">Save</button>
    }




         <div>
@Html.ActionLink("Back to List", "Index")
        </div>

     <script>
$(function () {

    var ids = [];

    $(".checkBoxClass").change(function () {
        if (this.checked) {
            ids.push(this.value);
        }
        else {
            var index = ids.indexOf(this.value);
            ids.splice(index, 1);
        }
        $('#ids').val(ids);
    });


     });
     </script>

You don't **need** to be able to access that intermediate table! "How many movies has an actor played in?" --> check `thatActor.mvz.Count`. "How many actors played in this movie` --> check `thatMovie.actz.Count` - there's absolutely **no need** to have access to that intermediate table (and therefore it's hidden by Entity Framework, for good reasons!) — marc_s, Sep 01 '18 at 16:47
But if we dont access the intermediate table how would we assign the primary keys, The count is not an issue, the issue is that in future if you want to edit the actors of a movie, how would you do that ? or if you assign more actors to a particular movie? — Prince Walizada, Sep 02 '18 at 11:59
See the answers - you need to create one movie and add all its actors (or the other way around) - EF will handle the details of the junction table automagically — marc_s, Sep 02 '18 at 15:04

score 0 · Answer 1 · edited Sep 01 '18 at 16:48

@Prince_Walizada as you said, you have a third table which is called a Junction Table and by using EF there is no need to access to this table. because you can directly add any number of the other entity to the current record.

For example (this is pseudo code):

 var Movie = MyContext.Movies.Where(P=>Title.Contains("Star war")).FirstOrDefault();
 Movie.Actors.Add(new Actor() { Name = "Mike" , ...}
 Movie.Actors.Add(new Actor() { Name = "Joe" , ...}
 Movie.Actors.Add(new Actor() { Name = "Daniel" , ...}

and you could do this also in reverse:

 var Actor = MyContext.Movies.Where(P=>Name.Contains("Mike")).FirstOrDefault();
 Actor.Movies.Add(new Movie() { Title = "World of War" , ...}
 Actor.Movies.Add(new Movie() { Title = "hobbits" , ...}
 Actor.Movies.Add(new Movie() { Title = "toy story" , ...}

As you see, you don't need to know about that third table.

er-sho · Accepted Answer · 2018-09-03T11:15:21.920

The intermediate table is hidden by Entity framework but don't worry mvc have fabulous feature that is ViewModel to do your job that means you can to create view for intermediate table like MoviesActors

So for,

1) You have to create one view model for your intermediate table like

public class MoviesActorsViewModel
{
    public MoviesActorsViewModel()
    {
        mvz = new Movies();
        act = new Actors();
    }

    public Movies mvz { get; set; }
    public Actors act { get; set; }
}

2) Add view for above view model like

public ActionResult GetView()
{
    MoviesActorsViewModel mcvm = new MoviesActorsViewModel();

    return View(mcvm);
}

3) Then your view for above action method like.

The below razor is for your understanding purpose only. your actual razor may differ from this.

@model WebApplication2.Controllers.MoviesActorsViewModel

@{
    ViewBag.Title = "GetView";
}

<h2>GetView</h2>

@using (Html.BeginForm("Create", "Default", FormMethod.Post)) 
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>MoviesActorsViewModel</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })

        //mvz fields below

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                @Html.TextBoxFor(model => model.mvz.movie_title, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                @Html.TextBoxFor(model => model.mvz.genre, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        //act fields below

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                @Html.TextBoxFor(model => model.act.actor_name, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                @Html.TextBoxFor(model => model.act.country_of_birth, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

4) After you submit your form then your post method will add your mvz and act like

[HttpPost]
public ActionResult Create(MoviesActorsViewModel mcvm)
{
    _context.mvz.Add(mcvm.mvz);
    _context.actrs.Add(mcvm.act);
    return RedirectToAction("GetView", "Default");
}

Note: The above code snippet is for one movie and one actor. So if you want to add multiple actors to single movie then change your act property in MoviesActorsViewModel to List Like public List<Actors> act { get; set; } and add multiple textbox in view and after posting your form to action method Create add those multiple actors like _context.actrs.AddRange(mcvm.act);

Edit:

I have to register a new actor too. But I want to select actors from the actors table and assign them to the new movies that gets registered.

If you have to assign multiple actors to movie while adding new movie then

1) You view model is

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

    public Movies mvz { get; set; }
    public List<Actors> act { get; set; }
    public string[] ids { get; set; }
}

2) Populate your actors from db to view model's act list from your get view action method

public ActionResult GetView()
{
    MoviesActorsViewModel mcvm = new MoviesActorsViewModel();
    mcvm.act = _context.actrs.ToList();
    return View(mcvm);
}

3) In razor view you have to enter one movie and choose multiple actors for it then simply take checkbox for each of actors and add its respective id values to array and then pass this array with form submit.

@model WebApplicationMVC1.Controllers.MoviesActorsViewModel

<script src="https://code.jquery.com/jquery-3.3.1.min.js"
        integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8="
        crossorigin="anonymous"></script>

@{
    ViewBag.Title = "GetView";
}

<h2>GetView</h2>

@using (Html.BeginForm("Create", "Default1", FormMethod.Post))
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>MoviesActorsViewModel</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })

        //mvz fields below

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                @Html.TextBoxFor(model => model.mvz.movie_title, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                @Html.TextBoxFor(model => model.mvz.genre, new { htmlAttributes = new { @class = "form-control" } })
            </div>
        </div>

        //act fields below from list of actors

        @foreach (var item in Model.act)
        {
            <div class="form-group">
                <div class="col-md-offset-2 col-md-10">
                    <div class="checkbox control-group">
                        <label>
                            <!-- SEE id property here, I've made it dynamic -->
                            <input type="checkbox" id="cb_@item.Id" value="@item.Id" class="checkBoxClass" />
                            <label>@item.actor_name</label>
                        </label>
                    </div>
                </div>
            </div>
        }

        <div>
            <input type="hidden" name="ids" id="ids" />
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

<script>
    $(function () {

        var ids = [];

        $(".checkBoxClass").change(function () {      
            if (this.checked) {                
                ids.push(this.value);
            }
            else {
                var index = ids.indexOf(this.value);
                ids.splice(index, 1);
            }
            $('#ids').val(ids);
        });


    });
</script>

4) After submit your form to below action you can loop through each of id of actor and save it to database with movie

[HttpPost]
public ActionResult Create(MoviesActorsViewModel mcvm)
{
    _context.mvz.Add(mcvm.mvz);

    foreach (var id in mcvm.ids)
    {
        int id1 = Convert.ToInt32(id);
        Actors act = _context.actrs.Find(x => x.Id == id1);
        _context.actrs.Add(act);
    }


    return RedirectToAction("GetView", "Default");
}

Can not access the composite key table through a view, in order to enter data or assign the correct primary key in that table?

This is my new razor view

2 Answers2

Linked