Many to Many in database first approach mvc crud operation

Question

How to use many to many relationship in mvc database first approach. I am having following table structure in sql

Table 1:- studentMaster Table 2:- Subjects

My Model1.edmx and sql table structure pic is here

http://geeksprogrammings.blogspot.com/2014/04/blog-post.html

I know how to perform CRUD operation using Entity Framework using DAtabase First approach but how to perform crud operation when table have many to many relationship . I have also put my question on codeproject but no proper help is given

which code ......actually i don't know how to perform crud operation when there is many2many relationship between tables in database — Heemanshu Bhalla, Apr 14 '14 at 08:11
A many-to-many relationship needs to be projected to two many-to-one relationships. StudentMaster(StudentId) Subjects(SubjectId) StudentMaster_RELATIONSHIP_Subject(StudentId, SubjectId, POSSIBLE META DATA) — Zache, Apr 14 '14 at 08:22
Liam, my question is just how to insert,delete,edit,update data in tables when tables are having many 2 many relationship ......I am using Entity framework database first approach — Heemanshu Bhalla, Apr 14 '14 at 08:35
Follow the Blog for MVC Crud Operation with Interfaces and Repository Pattern with ADO.Net https://code2night.com/Blog/MyBlog/MVC-Crud-Operation-with-Interfaces-and-Repository-Pattern-with-ADONet — Shubham, Sep 12 '20 at 10:53

thsorens · Answer 1 · 2014-04-14T14:16:55.120

2

if not using any kind of IRepository pattern:

Create

Adding new student with subjects

using(var dbContext = new Model1Entitites()){
  var student = new Student{details..};
  student.Add(new Subject{});
  dbContext.Students.Add(student);
  dbContext.SaveChanges();
}

Update Adding subjects to an existing student

using(var dbContext = new Model1Entities()){
    objectToAddTo = dbContext.studentMaster.SingleOrDefault(x=> x.id == id);
    objectToAddTo.Subjects.Add(new Subject{});
    dbContext.SaveChanges();
}

Adding existing subjects to an existing student

var student = db.Students.FirstOrDefault(x=> x.Id == id);
var subjectToAdd = db.Subjects.FirstOrDefault(x=> x.id == SubjectId);

if(student!= null){
    if(subjectToAdd != null){
       student.Subjects.Add(subjectToAdd);
    }
}

db.SaveChanges();

Remove subject from student

using(var dbContext = new Model1Entities()){
    objectToRemoveFrom = dbContext.studentMaster.SingleOrDefault(x=> x.id == id);
    var subjectToRemove = dbContext.Subjects.SingleOrDefault(x=> x.subjectid==subjectid);
    objectToRemoveFrom.Subjects.Remove(subject);
    dbContext.SaveChanges();
}

Select

using(var dbContext = new Model1Entities()){
    return dbContext.Students.Include(x=> x.Subjects);
}

For adding multiple connections the other way around, do the same with the other object.

Returning something to view when dealing with connections

Since razor is not always happy with getting entities containing connection entitites, you should probably create a new model for the view instead of using the entity proxy.

Controller

var model = new StudentModel();
var student = db.Students.Include(x=> x.Subjects);
//here you can use example AutoMapper, or you can do it manually
model.Id = student.id;
model.Name = student.Name;
model.Subjects = db.Students.Subjects.Select(x=> new SubjectModel{Id = x.Id, Name = x.Name}).ToList();
return View(model);

ViewModel

public class StudentModel{
   public int Id{get;set;}
   public string Name{get;set;}
   public List<SubjectModel> Subjects{get;set;}
}
public class SubjectModel{
    public int Id{get;set;}
    public string Name{get;set;}
}

Razor

@model StudentModel;

<span>Student name @Model.Name</span>

<ul>

    @foreach(var subject in Model.Subjects){
       <li id="@subject.Id">@subject.Name</li>
    }
<ul>

edited Apr 14 '14 at 14:16

answered Apr 14 '14 at 08:23

thsorens

1,308
12
21

i am unable to get how to use this code do you have any example – Heemanshu Bhalla Apr 14 '14 at 08:59
I though i gave you an example;) Handling many to many relationships is no different than all other crud operations. Which operations do you want example code for? – thsorens Apr 14 '14 at 09:08
i want to code for view student with course – Heemanshu Bhalla Apr 14 '14 at 10:22
That's code to show student details with subjects which model class should i add to view . If i add only student class then subjects are not shown and i want students with their subjects public ActionResult Index() { return View(db.StudentMasters .Include(x => x.Subjects )); } – Heemanshu Bhalla Apr 14 '14 at 10:48
Ah, see your point. Ill post an example – thsorens Apr 14 '14 at 10:55
yes, my problem is how i can pass multiple model to view or is it possible – Heemanshu Bhalla Apr 14 '14 at 10:59
like in code below if I add view with scaffolding list then it only shows student data not subjects return View(db.StudentMasters .Include(x => x.Subjects )); – Heemanshu Bhalla Apr 14 '14 at 11:04
In below line there is error and model does not have anything like Students---------- model.Students = db.Students.Subjects.Select(x=> new StudentModel{Id = x.Id, Name = x.Name}).ToList(); – Heemanshu Bhalla Apr 14 '14 at 11:41
My bad, should be model.Subjects. Typo – thsorens Apr 14 '14 at 11:46
yes, but still it gives error at these lines ------- – Heemanshu Bhalla Apr 14 '14 at 11:50
model.Id = student.id;----- error at id – Heemanshu Bhalla Apr 14 '14 at 11:50
error at name------- model.Name = student.Name; – Heemanshu Bhalla Apr 14 '14 at 11:51
error at subjects in db.StudentMasters .Subjects--------------------------- model.Subjects = db.StudentMasters .Subjects.Select(x => new StudentModel { Id = x.Id, Name = x.Name }).ToList(); – Heemanshu Bhalla Apr 14 '14 at 11:51
Since i have no idea what your entity names are, i just assumed that Id and Name was in there. You of course like always have to adjust the code to fit your needs. – thsorens Apr 14 '14 at 12:06
but no entity names comes when i pres dot(.) after model – Heemanshu Bhalla Apr 14 '14 at 12:17
I have adjusted code like this Is it right ------------------ var model = new StudentModel();--------------- var student = db.StudentMasters .Include(x => x.Subjects);-------------------- //here you can use example AutoMapper, or you can do it manually model.Id = 2;------------------ model.Name = "HB";----------------- model.Subjects = db.Subjects.Select(x => new -----------SubjectModel { Id = x.SubjectID , Name = x.SubjectName }).ToList();--------------------------- return View(model);----------------- – Heemanshu Bhalla Apr 14 '14 at 12:18
Remember that studentMasters is containing more than one entry. db.StudentMasters.Include(x=> x.Subjects).FirstOrDefault() will fetch the first record. db.StudentMasters.Include(x=> x.Subjects).FirstOrDefault(x=> x.Id == 1); will fetch the first record that has the Id == 1 – thsorens Apr 14 '14 at 12:20
Is my code correct.............I have not added view untill as your view code gives errors – Heemanshu Bhalla Apr 14 '14 at 12:24
printing model in razor should be Model, fixed it in my solution. – thsorens Apr 14 '14 at 12:28
Correct Controller code :- var model = new viewmodel.StudentModel();---------------------- var student = db.StudentMasters.Include(x => x.Subjects);------------------------- //here you can use example AutoMapper, or you can do it manually model.Id = 2;---------------------- model.Name = "HB";---------------------------- model.Subjects = db.Subjects.Select(x => new viewmodel . SubjectModel { Id = x.SubjectID , Name = x.SubjectName }).ToList();------------------------------------ return View(model);-------------------- – Heemanshu Bhalla Apr 14 '14 at 12:39
could you plz explain this line -------------especially -----Id = x.SubjectID , Name = x.SubjectName--------------- model.Subjects = db.Subjects.Select(x => new viewmodel . SubjectModel { Id = x.SubjectID , Name = x.SubjectName }).ToList(); – Heemanshu Bhalla Apr 14 '14 at 12:42
new viewmodel should be New SubjectModel – thsorens Apr 14 '14 at 12:46
But my viewmodel structure is like studentmodel and subjectmodel class are under viewmodl class and are placed on controller not separate – Heemanshu Bhalla Apr 14 '14 at 12:47
If you use StudentModel as the viewmodel, and applies the SubjectModels like in my example. Its gonna work just fine. – thsorens Apr 14 '14 at 12:50
now it works .......but it shows student name with all subject names in subject table. But it should show only subject assigned to that student – Heemanshu Bhalla Apr 14 '14 at 13:07
There is problem in you update code when we use update code it add new subject to subject everytime but I have to use existing subjects – Heemanshu Bhalla Apr 14 '14 at 13:15
You have to fetch the existing subjects from db and add them to the student object when doing the update. – thsorens Apr 14 '14 at 13:38
IF i want to staically give id of subject to add to student's subjects – Heemanshu Bhalla Apr 14 '14 at 14:04
My update works but every time it adds new subject-------------------------------------------------------------------------------------- My insert works student works but everytime it adds new subject ------------------------------------------------------------- My select works but it retrieve all subjects instead of only subjects related to specific student – Heemanshu Bhalla Apr 14 '14 at 14:16
You have to provide code if something doesnt work. Please create a new thread to get more help. I cant write your entire application for you. – thsorens Apr 14 '14 at 14:20
ok, thanks for great help – Heemanshu Bhalla Apr 14 '14 at 14:23
Follow the Blog for MVC Crud Operation with Interfaces and Repository Pattern with ADO.Net https://code2night.com/Blog/MyBlog/MVC-Crud-Operation-with-Interfaces-and-Repository-Pattern-with-ADONet – Shubham Sep 12 '20 at 10:53

Many to Many in database first approach mvc crud operation

1 Answers1