locked
Need help with MVC Create/Edit form for many to many relationships. RRS feed

  • Question

  • User1901201124 posted

    Hello, I'm new to MVC and I was wondering if someone could please help me in creating a simple form that inserts/updates a many to many relationship model.

    Everything in this Entity should be working now with the help of the people below (Much appreciated for taking the time!).

    The only thing that is missing now is when it does the Create it creates the data for the Company and Contractor table but it did not insert the data for the join table CompanyContractor with fields Company_ID and Contractor_ID (A join table EF created automatically for me).

    How do I "Cross reference both objects" so I can insert their ID into the Join table CompanyContractor? Thanks!

    public class Company
    {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int ID { get; set; }
    
            [Required]
            [Display(Name = "Company Name")]
            public string CompanyName { get; set; }	
    
    public int ProvinceId { get; set; } //ProvinceId is a foreign key that corresponds to Province
    [ForeignKey("ProvinceId")]
    public Province Province { get; set; }
    public virtual ICollection<Contractor> Contractors { get; set; } } public class Contractor { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int ID { get; set; } [Required] [Display(Name = "Contractor Name")] public string ContractorName { get; set; } [Required] [Display(Name = "Job Code")] public string JobCode { get; set; } public virtual ICollection<Company> Companies { get; set; } }


        public class Province
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int ID { get; set; }
    
            [Display(Name = "Province")]
            [StringLength(100, ErrorMessage = "Province cannot be longer than 100 characters.")]
            public string Name { get; set; }
        }

    the Context as so

        public class ProjectContext : DbContext
        {
            public ProjectContext() : base("ProjectContext")
            {
            }
            public DbSet<Company> Companies{ get; set; }
            public DbSet<Contractor> Contractor { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {   
                modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            }
         }

    My CombViewModel

            public Company Company { get; set; }
            public Contractor Contractor { get; set; }

    The crate view

    @model Project.ViewModel.CombViewModel
    
    @{
        ViewBag.Title = "Create";
    }
    @using (Html.BeginForm())
    {
        @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Testing</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
    
    
        <div class="form-group">
            @Html.LabelFor(model => model.CompanName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.CompanyName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.CompanyName, "", new { @class = "text-danger" })
            </div>
        </div>
    
    <div class="form-group">
    @Html.LabelFor(model => model.ContractorName, htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
    @Html.EditorFor(model => model.ContractorName, new { htmlAttributes = new { @class = "form-control" } })
    @Html.ValidationMessageFor(model => model.ContractorName, "", new { @class = "text-danger" })
    </div>
    </div>

    <div class="form-group">
    @Html.LabelFor(model => model.Company.ProvinceId, "Province", htmlAttributes: new { @class = "control-label col-md-2" })
    <div class="col-md-10">
    @Html.DropDownList("ProvinceId", null, htmlAttributes: new { @class = "form-control", Name = "Company.ProvinceId" })
    @Html.ValidationMessageFor(model => model.Company.ProvinceId, "", new { @class = "text-danger" })
    </div>
    </div> ......

    My Controller for Create

            public ActionResult Create()
            {
                ViewBag.ProvinceId= new SelectList(db.Province, "ID", "Name");
            }
    
            [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Create(ViewModels.ComboViewModel myViewModel)
            {
                if (ModelState.IsValid)
                {
                    db.Company.Add(myViewModel.Company);
                    db.Contractor.Add(myViewModel.Contractor);
                    db.SaveChanges();
    
                    return RedirectToAction("Index");
                }Thanks!</div> <div></div>
    Wednesday, February 13, 2019 3:29 AM

All replies

  • User-2054057000 posted

    You should create Many-to-Many relationships using Fluent APIs - Configure Many-to-Many relationship using Fluent API in Entity Framework Core

    Then you need to insert the records - Insert Records in Entity Framework Core

    Thanks & Regards

    Wednesday, February 13, 2019 12:07 PM
  • User1901201124 posted

    I thought the only benefit to using hte Fluent API is to customize your join table fields? EF 6 should handle everything for me?

    Looking to know how to do this using APS.NET MVC with EF6 

    Wednesday, February 13, 2019 1:36 PM
  • User1120430333 posted

    For many to many relationships do we need to create a ViewModel and then user that view model in the Create and Edit form or can we just use the domain model?

    You shouldn't confuse the persistence model with the view model or domain model.

    https://blog.sapiensworks.com/post/2012/04/07/Just-Stop-It!-The-Domain-Model-Is-Not-The-Persistence-Model.aspx

    https://en.wikipedia.org/wiki/Separation_of_concerns

    http://trainitsolutions.com/2014/10/06/advantages-of-viewmodel-in-mvcmodel-view-controller-2/

    https://www.tutlane.com/tutorial/aspnet-mvc/how-to-use-viewmodel-in-asp-net-mvc-with-example

    I'm a bit confused how MVC inserts this type of relationship so if you could provide me with some sample code for the above model that would be much appreciated.

    It is you that has to write the code for CRUD operations with the database.. MVC is just a UI design pattern that implements seperation of duty and seperation of concerns.

    https://www.codeproject.com/Articles/228214/Understanding-Basics-of-UI-Design-Pattern-MVC-MVP

    https://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

    <copied>

    An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic. For example, if you are using the Microsoft Entity Framework to access your database, then you would create your Entity Framework classes (your .edmx file) in the Models folder.

    A view should contain only logic related to generating the user interface. A controller should only contain the bare minimum of logic required to return the right view or redirect the user to another action (flow control). Everything else should be contained in the model.

    In general, you should strive for fat models and skinny controllers. Your controller methods should contain only a few lines of code. If a controller action gets too fat, then you should consider moving the logic out to a new class in the Models folder.

    <end>

    You need a single view for create,  and you need a single view for edit. You don't combine them into one view, becuase you would have to put business logic into the view to determine when you are doing and edit or create that breaks MVC principles.

    Wednesday, February 13, 2019 2:15 PM
  • User1901201124 posted

    I understand I need a single view for Create and Edit. But I need the Create chtml to include fields I can bind to, to the Company and Contractor class. When ever I try to do the above using a View model or just the "Company" model i can't get to the other table.

    I went through some of the links to try to understand farther. Do you mind looking at my code and outline the changes and additions i would need to accomplish the example? Much appreciated.

    Wednesday, February 13, 2019 2:45 PM
  • User475983607 posted

    I understand I need a single view for Create and Edit.

    I recommend two different Views.

    But I need the Create chtml to include fields I can bind to, to the Company and Contractor class. When ever I try to do the above using a View model or just the "Company" model i can't get to the other table.

    Representing one-to-many in a View where the many side has several input requires indexes and input names that represent the relationship.  The model binder needs this information to deserialize and populate the complex type. 

    https://www.learnrazorpages.com/razor-pages/model-binding#binding-complex-collections

    I went through some of the links to try to understand farther. Do you mind looking at my code and outline the changes and additions i would need to accomplish the example? Much appreciated.

    Your entities look fine.  Create a ViewModel that allows you to generate the required HTML from the View.  Write code in the POST action method to populate the Entities from the ViewModel.  Then save the entity.  I realize the conversion seems like an extra step but you have to remember Entities are a representation of the data store while a ViewModel defines the UI data.   The two types, Entity and ViewModel, have different jobs and are often slightly different anyway.

    I use AutoMapper to do the conversion between ViewModels and Entities of you are interested that type of API.

    Wednesday, February 13, 2019 3:20 PM
  • User1901201124 posted

    Yeah I ended up deleting the declaration @model and re-typing it and VS stopped complaining.

    I needed to add a drop down list province - updated the model in the question. But when I hit the Create everything but the Province ID is getting inserted. It's null and 0.

    Any ideas why? Thanks!

    Wednesday, February 13, 2019 3:52 PM
  • User475983607 posted

    Yeah I ended up deleting the declaration @model and re-typing it and VS stopped complaining.

    I needed to add a drop down list province - updated the model in the question. But when I hit the Create everything but the Province ID is getting inserted. It's null and 0.

    Any ideas why? Thanks!

    Not without the source code.  The usual cause is a missing or incorrect name attribute in the select element.

    Wednesday, February 13, 2019 4:46 PM
  • User1901201124 posted

    I updated the code in my original question to what i was doing.

    Wednesday, February 13, 2019 4:47 PM
  • User475983607 posted

    I updated the code in my original question to what i was doing.

    The select name, ProvinceId, does not match the ViewModel property name of Company.ProvinceId. 

    @Html.DropDownList("ProvinceId", null, htmlAttributes: new { @class = "form-control", Name = "Company.ProvinceId" })

    The browser has a feature called Developer Tools which allows you to view the HTML.  

    Wednesday, February 13, 2019 5:14 PM
  • User1901201124 posted

    OK hot damn. that worked! 

    It inserted into Company and Contractor table. But it never created the entry for the Join table CompanyContractor with the Company_ID and Contractor_ID  to associate the record.

    Wednesday, February 13, 2019 5:19 PM
  • User475983607 posted

    OK hot damn. that worked! 

    It inserted into Company and Contractor table. But it never created the entry for the Join table CompanyContractor with the Company_ID and Contractor_ID  to associate the record.

    The ViewModel does not express this relationship.  As a matter of fact the ViewModel is not a good representation of a ViewModel since it is really two Entities.  A many-to-many relationship View would have a list of Company and a list of Contractor.   Or the View would select the a Company and allow the user to select many Contractor items from a list or checkboxes.  Or the View would select the Contractor and allow the user to select many Company items from a list or checkboxes.

    See the Getting started with data tutorial step 8/9 but you might want to start from the beginning as the tutorial builds as you go.

    Step 7

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/updating-related-data-with-the-entity-framework-in-an-asp-net-mvc-application

    Wednesday, February 13, 2019 6:43 PM
  • User1901201124 posted

    I get it. It's not a traditional many to many like it is outlined in your reply. 

    But I just need to make this happen. Is there a way to insert into the join table CompanyContractor that EF created manually to associate these 2 separate entities?

    Wednesday, February 13, 2019 6:51 PM
  • User475983607 posted

    I get it. It's not a traditional many to many like it is outlined in your reply. 

    But I just need to make this happen. Is there a way to insert into the join table CompanyContractor that EF created manually to associate these 2 separate entities?

    Your situation is not unique.  The most direct UI is a two column table; Company and Contractor.  Each row relates the Company to a Contractor.  How complex you make the UI is up to you.  The simplest approach is a company and contractor dropdown in each row cell.  

    Otherwise the UI needs to approach the relationship as a one-to-many.  You can pick the one side either company or contractor.  However, the child records must have an index in the input name as explained in my first post.  See the link.

    Wednesday, February 13, 2019 7:25 PM
  • User1901201124 posted

    Thanks for the response. 

    I don't want to associate the entities with a drop down list  as I reduced some of the fields  in Company and Contractor to try to keep the code as short as possible. I just need a way to associate the 2 entities in a join table.

    There must be some code that says add company, add contractor and then add the IDs into the Join table. 

    Wednesday, February 13, 2019 7:28 PM
  • User475983607 posted

    Thanks for the response. 

    I don't want to associate the entities with a drop down list  as I reduced some of the fields  in Company and Contractor to try to keep the code as short as possible. I just need a way to associate the 2 entities in a join table.

    There must be some code that says add company, add contractor and then add the IDs into the Join table. 

    There is and it's extremely simple.  Either populate the CompanyContractor directly (many-to-many) or through the navigation property (one-to-many). The tutorial illustrates the one-to-many side quite well.

    I recommend writing a method that implements the insert.  This will remove the UI complexity.  Once the method is working as expected then work on the UI to get the ViewModel formatted so it interfaces with the working insert method.

    Wednesday, February 13, 2019 7:54 PM
  • User1901201124 posted

    Probably it would be easiest for me to Populate the CompanyContractor directly. 

    I found the below from here. But i'm not sure how to apply this same logic to adding the instance to navigation property to my ViewModel.
    Is this something i can do even?

    Thanks!

    	using (ManyToManyEntities conn = new ManyToManyEntities())
    	{
    		//add instances to context
    		conn.Product.Add(prod);
    		conn.Supplier.Add(sup);
    
    		// add instance to navigation property
    		prod.Supplier.Add(sup);
    
    		//call SaveChanges from context to confirm inserts
    		conn.SaveChanges();
    	}

    Wednesday, February 13, 2019 11:27 PM
  • User1520731567 posted

    Hi MVCNewbi3v,

    MVCNewbi3v

    using (ManyToManyEntities conn = new ManyToManyEntities())
    	{
    		//add instances to context
    		conn.Product.Add(prod);
    		conn.Supplier.Add(sup);
    
    		// add instance to navigation property
    		prod.Supplier.Add(sup);
    
    		//call SaveChanges from context to confirm inserts
    		conn.SaveChanges();
    	}

    Yes,it's right.

    when data do not exist in tables, add instances to context, add an instance to navigation property and call SaveChanges method from context.

    That is possible because Entity Framework, at the time of insert, puts primary key value (if Identity, AutoIncrement) in correspondent entity’s property inserted.

    The other situations is:

    data already exists in tables and it's necessary to relate them, pass the primary key to two tables/entity,

    add and attach to context object,

    add instance to entity navigation property and finally call SaveChanges method.

    using (ManyToManyEntities conn = new ManyToManyEntities())
    	{
    
    		/*
    			* this steps follow to both entities
    			* 
    			* 1 - create instance of entity with relative primary key
    			* 
    			* 2 - add instance to context
    			* 
    			* 3 - attach instance to context
    			*/
    
    		// 1
    		Product p = new Product { ProductID = productID };
    		// 2
    		conn.Product.Add(p);
    		// 3
    		conn.Product.Attach(p);
    
    		// 1
    		Supplier s = new Supplier { SupplierID = supplierID };
    		// 2
    		conn.Supplier.Add(s);
    		// 3
    		conn.Supplier.Attach(s);
    
    		// like previous method add instance to navigation property
    		p.Supplier.Add(s);
    
    		// call SaveChanges
    		conn.SaveChanges();
    	}

    More details,you could refer to this article:

    https://www.codeproject.com/Tips/893609/CRUD-Many-to-Many-Entity-Framework

    Best Regards.

    Yuki Tao

    Monday, February 18, 2019 10:11 AM