How to create a new record in MVC using Entity Framework having Many to Many relationship

I have started learning ASP.Net in Visual Studio 2012 where I am using MVC with Entity Framework. The database of my application has multiple Many to Many relationships. For example, I have to assign multiple Petitioners, Respondents, Advocates and Senior Advocates to a given Court Case. As multiple petitioners, respondents, advocates and sr. advocates can be assigned to a given Court Case, a petitioner or a respondent or an advocate can be engaged in multiple case and thus forms Many to Many relationship.

I have the following tables:

1. tblCase (fldCaseId, fldCaseNo)
2. tblParty (fldPartyId, fldName)
3. tblAdvocate (fldAdvocateId, fldName)

to establish Many to Many relationship I have the following Link tables:

1. tblPetitioner (fldCaseId, fldPartyId)
2. tblRespondent (fldCaseId, fldPartyId)
3. tblEngagedAdvocate (fldCaseId, fldAdvocateId)
4. tblEngagedSrAdvocate (fldCaseId, fldAdvocateId)

Here is the screenshot of my edmx file:
Edmx.png
Here is the Model:
namespace CCIS.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class tblCase
    {
        public tblCase()
        {
            this.Advocates = new HashSet<tblAdvocate>();
            this.SrAdvocates = new HashSet<tblAdvocate>();
            this.Petitioners = new HashSet<tblParty>();
            this.Respondents = new HashSet<tblParty>();
        }
    
        public int fldCaseId { get; set; }
        public int fldCaseTypeId { get; set; }
        public int fldCourtId { get; set; }
        public int fldDepartmentId { get; set; }
        public int fldContactId { get; set; }
        public int fldCaseNo { get; set; }
        public string fldYear { get; set; }
        public Nullable<int> fldConnectedCase { get; set; }
        public string fldSubject { get; set; }
        public string fldBriefStatus { get; set; }
        public Nullable<decimal> fldStake { get; set; }
    
        public virtual tblCase ConnectedCase { get; set; }
        public virtual tblCaseType tblCaseType { get; set; }
        public virtual tblContact tblContact { get; set; }
        public virtual tblCourt tblCourt { get; set; }
        public virtual tblDepartment tblDepartment { get; set; }
        public virtual ICollection<tblAdvocate> Advocates { get; set; }
        public virtual ICollection<tblAdvocate> SrAdvocates { get; set; }
        public virtual ICollection<tblParty> Petitioners { get; set; }
        public virtual ICollection<tblParty> Respondents { get; set; }
    }
}

Open in new window


namespace CCIS.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class tblParty
    {
        public tblParty()
        {
            this.tblCases = new HashSet<tblCase>();
            this.tblCases1 = new HashSet<tblCase>();
        }
    
        public int fldPartyId { get; set; }
        public string fldName { get; set; }
    
        public virtual ICollection<tblCase> tblCases { get; set; }
        public virtual ICollection<tblCase> tblCases1 { get; set; }
    }
}

Open in new window


namespace CCIS.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class tblAdvocate
    {
        public tblAdvocate()
        {
            this.tblCases = new HashSet<tblCase>();
            this.tblCases1 = new HashSet<tblCase>();
        }
    
        public int fldAdvocateId { get; set; }
        public string fldName { get; set; }
    
        public virtual ICollection<tblCase> tblCases { get; set; }
        public virtual ICollection<tblCase> tblCases1 { get; set; }
    }
}

Open in new window


Here is the ViewModel:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CCIS.Models;

namespace CCIS.ViewModel
{
    public class vmCourtCase
    {
        public tblCase CourtCase { get; set; }
        public IEnumerable<SelectListItem> AllPetitioners { get; set; }
        public IEnumerable<SelectListItem> AllRespondents { get; set; }

        private List<int> _selectedPetitioners;
        public List<int> SelectedPetitioners
        {
            get
            {
                if (_selectedPetitioners == null)
                {
                    _selectedPetitioners = CourtCase.Petitioners.Select(m => m.fldPartyId).ToList();
                }
                return _selectedPetitioners;
            }
            set { _selectedPetitioners = value; }
        }

        private List<int> _selectedRespondents;
        public List<int> SelectedRespondents
        {
            get
            {
                if (_selectedRespondents == null)
                {
                    _selectedRespondents = CourtCase.Respondents.Select(m => m.fldPartyId).ToList();
                }
                return _selectedRespondents;
            }
            set { _selectedRespondents = value; }
        }
    }
}

Open in new window


Here is the Controller:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using CCIS.Models;
using CCIS.ViewModel;

namespace CCIS.Controllers
{
    public class CourtCaseController : Controller
    {
        private CCISEntities db = new CCISEntities();

        // GET: /CourtCase/
        public ActionResult Index()
        {
            var tblcases = db.tblCases.Include(t => t.ConnectedCase).Include(t => t.tblCaseType).Include(t => t.tblContact).Include(t => t.tblCourt).Include(t => t.tblDepartment);
            return View(tblcases.ToList());
        }

        // GET: /CourtCase/Details/5
        public ActionResult Details(int? id)
        {
            
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }


            var courtCaseVM = new vmCourtCase
            {
              CourtCase = db.tblCases.Include(i => i.Petitioners).Include(i => i.Respondents).First(i => i.fldCaseId == id)
            };

            if (courtCaseVM.CourtCase == null)
                return HttpNotFound();
            var allPetitionersList = db.tblParties.ToList();
            var allRespondentsList = db.tblParties.ToList();
          
            courtCaseVM.AllPetitioners = allPetitionersList.Select(o => new SelectListItem
            {
               Text = o.fldName,
              Value = o.fldPartyId.ToString()
            });

            courtCaseVM.AllRespondents = allRespondentsList.Select(o => new SelectListItem
            {
                Text = o.fldName,
                Value = o.fldPartyId.ToString()
            });

            tblCase tblcase = db.tblCases.Find(id);
            if (tblcase == null)
            {
                return HttpNotFound();
            }
            //
            //return View(tblcase);
            ViewBag.fldConnectedCase = new SelectList(db.tblCases, "fldCaseId", "fldCaseNo", courtCaseVM.CourtCase.fldConnectedCase);
            ViewBag.fldCaseTypeId = new SelectList(db.tblCaseTypes, "fldCaseTypeId", "fldCaseType", courtCaseVM.CourtCase.fldCaseTypeId);
            ViewBag.fldContactId = new SelectList(db.tblContacts, "fldContactId", "fldName", courtCaseVM.CourtCase.fldContactId);
            ViewBag.fldCourtId = new SelectList(db.tblCourts, "fldCourtId", "fldName", courtCaseVM.CourtCase.fldCourtId);
            ViewBag.fldDepartmentId = new SelectList(db.tblDepartments, "fldDeptId", "fldName", courtCaseVM.CourtCase.fldDepartmentId);
            return View(courtCaseVM);
        }

        // GET: /CourtCase/Create
        public ActionResult Create()
        {
            

            ViewBag.fldConnectedCase = new SelectList(db.tblCases, "fldCaseId", "fldCaseNo");
            ViewBag.fldCaseTypeId = new SelectList(db.tblCaseTypes, "fldCaseTypeId", "fldCaseType");
            ViewBag.fldContactId = new SelectList(db.tblContacts, "fldContactId", "fldName");
            ViewBag.fldCourtId = new SelectList(db.tblCourts, "fldCourtId", "fldName");
            ViewBag.fldDepartmentId = new SelectList(db.tblDepartments, "fldDeptId", "fldName");
            ViewBag.Petitioners = new MultiSelectList(db.tblParties, "fldPartyId", "fldName");
            ViewBag.Respondents = new MultiSelectList(db.tblParties, "fldPartyId", "fldName");
            return View();
        }

        // POST: /CourtCase/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create ([Bind(Include = "fldCaseId,fldCaseTypeId,fldCourtId,fldDepartmentId,fldContactId,fldCaseNo,fldYear,fldConnectedCase,fldSubject,fldBriefStatus,fldStake")] tblCase tblcase)
        {
            if (ModelState.IsValid)
            {
                db.tblCases.Add(tblcase);

               
               
                

                ViewBag.fldConnectedCase = new SelectList(db.tblCases, "fldCaseId", "fldCaseNo", "fldConnectedCase");
                ViewBag.fldCaseTypeId = new SelectList(db.tblCaseTypes, "fldCaseTypeId", "fldCaseType", "fldCaseTypeId");
                ViewBag.fldContactId = new SelectList(db.tblContacts, "fldContactId", "fldName", "fldContactId");
                ViewBag.fldCourtId = new SelectList(db.tblCourts, "fldCourtId", "fldName", "fldCourtId");
                ViewBag.fldDepartmentId = new SelectList(db.tblDepartments, "fldDeptId", "fldName", "fldDepartmentId");
               
                return View();
            }
        }

        // GET: /CourtCase/Edit/5
        public ActionResult Edit(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }

            var courtCaseVM = new vmCourtCase
            {
                CourtCase = db.tblCases.Include(i => i.Petitioners).Include(i => i.Respondents).First(i => i.fldCaseId == id)
            };

            if (courtCaseVM.CourtCase == null)
                return HttpNotFound();
            var allPetitionersList = db.tblParties.ToList();
            var allRespondentsList = db.tblParties.ToList();

            courtCaseVM.AllPetitioners = allPetitionersList.Select(o => new SelectListItem
            {
                Text = o.fldName,
                Value = o.fldPartyId.ToString()
            });

            courtCaseVM.AllRespondents = allRespondentsList.Select(o => new SelectListItem
            {
                Text = o.fldName,
                Value = o.fldPartyId.ToString()
            });

            tblCase tblcase = db.tblCases.Find(id);
            if (tblcase == null)
            {
                return HttpNotFound();
            }
            ViewBag.fldConnectedCase = new SelectList(db.tblCases, "fldCaseId", "fldCaseNo", courtCaseVM.CourtCase.fldConnectedCase);
            ViewBag.fldCaseTypeId = new SelectList(db.tblCaseTypes, "fldCaseTypeId", "fldCaseType", courtCaseVM.CourtCase.fldCaseTypeId);
            ViewBag.fldContactId = new SelectList(db.tblContacts, "fldContactId", "fldName", courtCaseVM.CourtCase.fldContactId);
            ViewBag.fldCourtId = new SelectList(db.tblCourts, "fldCourtId", "fldName", courtCaseVM.CourtCase.fldCourtId);
            ViewBag.fldDepartmentId = new SelectList(db.tblDepartments, "fldDeptId", "fldName", courtCaseVM.CourtCase.fldDepartmentId);
            return View(courtCaseVM);
        }

        // POST: /CourtCase/Edit/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        // public ActionResult Edit([Bind(Include="fldCaseId,fldCaseTypeId,fldCourtId,fldDepartmentId,fldContactId,fldCaseNo,fldYear,fldConnectedCase,fldSubject,fldBriefStatus,fldStake")] tblCase tblcase)
        public ActionResult Edit(vmCourtCase courtCaseView)
        {
            if (courtCaseView == null) return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            if (ModelState.IsValid)
            {
                var jobToUpdate = db.tblCases
                    .Include(i => i.Petitioners).Include(i => i.Respondents).First(i => i.fldCaseId == courtCaseView.CourtCase.fldCaseId);

                if (TryUpdateModel(jobToUpdate, "tblCase", new string[] { "fldCaseId", "fldCaseTypeId", "fldCourtId", "fldDepartmentId", "fldContactId", "fldCaseNo", "fldYear", "fldConnectedCase", "fldSubject", "fldBriefStatus", "fldStake" }))
                {
                    var newPetitioners = db.tblParties.Where(
                        m => courtCaseView.SelectedPetitioners.Contains(m.fldPartyId)).ToList();

                    var newRespondents = db.tblParties.Where(
                        m => courtCaseView.SelectedRespondents.Contains(m.fldPartyId)).ToList();

                    var updatedPetitioners = new HashSet<int>(courtCaseView.SelectedPetitioners);
                    var updatedRespondents = new HashSet<int>(courtCaseView.SelectedRespondents);

                    foreach (tblParty petitioner in db.tblParties)
                    {
                        if (!updatedPetitioners.Contains(petitioner.fldPartyId))
                        {
                            jobToUpdate.Petitioners.Remove(petitioner);
                        }
                        else
                        {
                            jobToUpdate.Petitioners.Add((petitioner));
                        }
                    }

                    foreach (tblParty respondent in db.tblParties)
                    {
                        if (!updatedRespondents.Contains(respondent.fldPartyId))
                        {
                            jobToUpdate.Respondents.Remove(respondent);
                        }
                        else
                        {
                            jobToUpdate.Respondents.Add((respondent));
                        }
                    }

                    db.Entry(jobToUpdate).State = EntityState.Modified;
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
                ViewBag.fldConnectedCase = new SelectList(db.tblCases, "fldCaseId", "fldCaseNo", courtCaseView.CourtCase.fldConnectedCase);
                ViewBag.fldCaseTypeId = new SelectList(db.tblCaseTypes, "fldCaseTypeId", "fldCaseType", courtCaseView.CourtCase.fldCaseTypeId);
                ViewBag.fldContactId = new SelectList(db.tblContacts, "fldContactId", "fldName", courtCaseView.CourtCase.fldContactId);
                ViewBag.fldCourtId = new SelectList(db.tblCourts, "fldCourtId", "fldName", courtCaseView.CourtCase.fldCourtId);
                ViewBag.fldDepartmentId = new SelectList(db.tblDepartments, "fldDeptId", "fldName", courtCaseView.CourtCase.fldDepartmentId);
                return View(courtCaseView);
            }
            return null;
        }

        // GET: /CourtCase/Delete/5
        public ActionResult Delete(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            tblCase tblcase = db.tblCases.Find(id);
            if (tblcase == null)
            {
                return HttpNotFound();
            }
            return View(tblcase);
        }

        // POST: /CourtCase/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(int id)
        {
            tblCase tblcase = db.tblCases.Find(id);
            db.tblCases.Remove(tblcase);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}

Open in new window


The Edit part is working fine as I have followed Antonio Ripa's article available at Code Project. But I am unable to make necessary changes to the Create part so that multiple petitioners, respondents, advocates and Sr Advocates after selecting from the listbox can be saved to the database.

Please help solve the problem.
ArunKhatriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArunKhatriAuthor Commented:
I am extremely disappointed to learn that at Experts Exchange it takes quite a long time to get the questions answered. It has been three days since I posted the question, which even after placing "Request for Attention" has not been answered yet.

Well it doesn't matter because after doing hard work I have myself solved the problem and would like to contribute here:

Edit the Create in the Controller as below:

 public ActionResult Create()
        {
            

            ViewBag.fldConnectedCase = new SelectList(db.tblCases, "fldCaseId", "fldCaseNo");
            ViewBag.fldCaseTypeId = new SelectList(db.tblCaseTypes, "fldCaseTypeId", "fldCaseType");
            ViewBag.fldContactId = new SelectList(db.tblContacts, "fldContactId", "fldName");
            ViewBag.fldCourtId = new SelectList(db.tblCourts, "fldCourtId", "fldName");
            ViewBag.fldDepartmentId = new SelectList(db.tblDepartments, "fldDeptId", "fldName");
            ViewBag.Petitioners = new MultiSelectList(db.tblParties, "fldPartyId", "fldName");
            ViewBag.Respondents = new MultiSelectList(db.tblParties, "fldPartyId", "fldName");
            return View();
        }

        // POST: /CourtCase/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for 
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create ([Bind(Include = "fldCaseId,fldCaseTypeId,fldCourtId,fldDepartmentId,fldContactId,fldCaseNo,fldYear,fldConnectedCase,fldSubject,fldBriefStatus,fldStake")] tblCase tblcase, string[] Petitioners, string[] Respondents)
        
        {
            if (Petitioners != null)
            {
                tblcase.Petitioners = new List<tblParty>();
                foreach (var petitioner in Petitioners)
                {
                    var petitionerToAdd = db.tblParties.Find(int.Parse(petitioner));
                    tblcase.Petitioners.Add(petitionerToAdd);
                }
            }

            if (Respondents != null)
            {
                tblcase.Respondents = new List<tblParty>();
                foreach (var respondent in Respondents)
                {
                    var respondentToAdd = db.tblParties.Find(int.Parse(respondent));
                    tblcase.Respondents.Add(respondentToAdd);
                }
            }

            if (ModelState.IsValid)
            {
                db.tblCases.Add(tblcase);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

                ViewBag.fldConnectedCase = new SelectList(db.tblCases, "fldCaseId", "fldCaseNo", tblcase.fldConnectedCase);
                ViewBag.fldCaseTypeId = new SelectList(db.tblCaseTypes, "fldCaseTypeId", "fldCaseType", tblcase.fldCaseTypeId);
                ViewBag.fldContactId = new SelectList(db.tblContacts, "fldContactId", "fldName", tblcase.fldContactId);
                ViewBag.fldCourtId = new SelectList(db.tblCourts, "fldCourtId", "fldName", tblcase.fldCourtId);
                ViewBag.fldDepartmentId = new SelectList(db.tblDepartments, "fldDeptId", "fldName", tblcase.fldDepartmentId);
               
                return View(tblcase);
            
        }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.