none
C# with Entitty Framework and Many to Many Relationships RRS feed

  • Question

  • Hi,

    The EF only supports many-to-many relationships without payload (no additional columns except the two foreign key columns pointed to two tables with many-to-many relationship).

    However, I have run into a situation where I need the mapping table to have extra fields. This is the model:

    Tables

    Student
    -----------
    StudentID
    Name

    StudentCourse
    -------------------
    StudentID
    CourseID
    Grade
    Created

    Course
    ---------
    CourseID
    Name
    Credits
    DepartmentID

    Student--------> StudentCourse<---------Course

    Please, could someone shed some light on how I can INSERT, UPDATE, DELETE and SELECT from all tables:

    1 - what do I have to do to insert a new entity into StudentCourse. I would need to insert the primary keys of both tables and the extra fields.

    2 - How can I update the Grades field in the StudentCourse?

    3 - How do I delete a student from a course, etc...

    With true many to many relationships in the EF without the mapping table, I just need to work with the collections. something like this:

    company.Permissions.Add(perm);

    And the EF will take care of updating the mapping table.

    I would appreciate if you could provide some sample code.

    Cheers

    C

     

     

     

     

     

     

    • Edited by Claudio Pallone Wednesday, November 16, 2011 5:05 PM
    • Moved by CoolDadTx Thursday, November 17, 2011 2:33 PM EF related (From:Visual C# General)
    Wednesday, November 16, 2011 5:04 PM

Answers

  • Hi Claudio,

    Here is a simple example based on that ContosoUniversity demo app I linked earlier I hope it helps you:

    var student = db.Students.First();
    var course = db.Courses.First();
    
    var enrollment = new Enrollment { Course = course, Student = student, Grade = 50 };
    
    db.Enrollments.Add(enrollment);
    db.SaveChanges();
    


    Note that we use the navigation properties to set the Enrollment's Course and Student properties to actual entities.

    Here is the Model for enrollment:

    public class Enrollment
    {
        public int EnrollmentId { get; set; }
        public int CourseId { get; set; }
        public int StudentId { get; set; }
    
        public decimal? Grade { get; set; }
    
        public virtual Course Course { get; set; }
        public virtual Student Student { get; set; }
    }
    


    In Addition, both the Student & Course Models have:

    public virtual ICollection<Enrollment> Enrollments { get; set; }
    

    The virtual keyword permits lazy loading.

    If you need further assistance or explanation please ask.

    Thursday, November 17, 2011 12:24 PM
  • Hi Claudio,

    You would have to know both Id's.  Otherwise which course has the grade changed for?

    So your second example is correct.  It is worth noting that you do have a navigation Property on the Student Entity.  So if you already have your student Entity you can get to the course by walking the navigation tree.  For instance:

    var student = db.Students.First();
    var enrollment = student.Enrollments.First();
    var course = enrollment.Course;
    
    // You can also walk the other way:
    var course = db.Courses.First();
    var enrollment = course.Enrollments.First();
    var student = enrollment.Student;
    
    

    Intellisense works great with walking the navigation properties so you can easily see what is available to you as you just start typing in the IDE.

     

    As for your second question:  Generally we implement a pattern to create a separation.  For instance the Repository Pattern.  Your ASP.Net application would be accessing the Repository which in turn accesses the data (or in your case the service).  You can search MSDN for examples as there are many different versions.

    I have exactly 0 experience working with services, so I cannot guide you further on that.  As you have a new question now I would suggest creating a new thread with that as your question.  And closing this thread by marking the posts that best answer your question so others with a similar question can find answers easily.

    Here is a link to the Data Platform Development Category's Forum Root so you can ask your service-based question there.

    http://social.msdn.microsoft.com/Forums/en-US/category/dataplatformdev

     

    Thursday, November 17, 2011 2:22 PM
  • Hi Claudio,

    >Just for completeness could please explain to me why you use the First() method to get the first Enrollment property of the student.Enrollments collection?

    It was simply an example that would compile.  It wouldn't be very useful when you used it in the real world!  I should have explained that better.

    Normally you would need some logic to identify the Entity you want.  For instance, using LINQ:

    var enrollment = student.Enrollments.Where(x => x.CourseId == 5).FirstOrDefault();
    

    That will get the Enrollment for the Student Entity where the CourseId of the Enrollment is 5.  FirstOrDefault returns the Default value if the count is 0.  So if there was no enrollment for that student with the CourseId of 5, the enrollment variable would be null.

    Assuming that the variable isn't null you can then set the Grade property:

    enrollment.Grade = 100;
    

     

    >Would this return the right course? var course = enrollment.Course;

    In fact it will!  This is known as a Navigation Property.  It allows you to walk to another entity very easily.  This works here because an enrollment is only every associated to a single course.  It's also only ever associated to a single Student.  So you can get to the student in the same way:

    var student = enrollment.Student;
    


     

    Thursday, November 17, 2011 3:49 PM

All replies

  •  
    Msdn.en-US.adodotnetentityframework
     
    Wednesday, November 16, 2011 5:32 PM
  • Hi,

    What is this:

    Msdn.en-US.adodotnetentityframework
    Could you please clarify?
    Wednesday, November 16, 2011 6:08 PM
  • On 11/16/2011 1:08 PM, Claudio Pallone wrote:
    > Hi,
    >
    > What is this:
    >
    > Msdn.en-US.adodotnetentityframework
    > Could you please clarify?
     
    It's the MSDN fourm like the fourm you are posting to at this time
    'Msdn.en-US.csharpgeneral' is about.
    Msdn.en-US.adodotnetentityframework is all EF. That's what you are
    posting about is EF and 'Msdn.en-US.adodotnetentityframework' is most
    likely where you are going to get your answers.
     
    Wednesday, November 16, 2011 6:28 PM
  • Hi Claudio Pallone,

    Check out:

    http://www.asp.net/entity-framework/tutorials/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application

    About half way down the page (Search for the text "Enrollment" it will get you there).  The example is for an ASP.Net MVC site, however the Entity Framework stuff is exactly what you are doing.


    • Edited by Scotty_ Wednesday, November 16, 2011 7:03 PM Addressed to wrong person.
    Wednesday, November 16, 2011 7:01 PM
  • On 11/16/2011 2:01 PM, Scotty_ wrote:
    > Hi Darnold924,
    >
    > Check out:
    >
    >
    > About half way down the page (Search for the text "Enrollment" it will
    > get you there). The example is for an ASP.Net MVC site, however the
    > Entity Framework stuff is exactly what you are doing.
    >
     
    I have developed enterprise level Web applications using MVP Model View
    Presenter, which is more flexible to use than MVC. I consider MVC too
    rigit of a design pattern particularly so when MS started stepping in
    with some MVC framework. As far as EF, I have used it on more than a few
    occasions in solutions.
     
    My efforts are to point the OP in the right direction where the EF
    people are located, which is the EF forum.
     
    Wednesday, November 16, 2011 7:17 PM
  • I apologize for addressing the post to you, as it was meant to go towards the OP, that made it look like I was implying you were being unhelpful.  I have since edited my original post so it's properly addressed.

    I don't slight you in the least for attempting to point the OP to the EF forum :)

    Wednesday, November 16, 2011 7:26 PM
  • Hi Scotty,

    Thanks for the link. It was very helpful.

    However, it is only explaining how the relationship works. I already know that.

    What I really would like is some sample code of how to work with such many to many relationships where the mapping table is present in the EF model.

    Any ideas?

    Cheers

    C

     

     

    Thursday, November 17, 2011 10:21 AM
  • Hi Claudio,

    Here is a simple example based on that ContosoUniversity demo app I linked earlier I hope it helps you:

    var student = db.Students.First();
    var course = db.Courses.First();
    
    var enrollment = new Enrollment { Course = course, Student = student, Grade = 50 };
    
    db.Enrollments.Add(enrollment);
    db.SaveChanges();
    


    Note that we use the navigation properties to set the Enrollment's Course and Student properties to actual entities.

    Here is the Model for enrollment:

    public class Enrollment
    {
        public int EnrollmentId { get; set; }
        public int CourseId { get; set; }
        public int StudentId { get; set; }
    
        public decimal? Grade { get; set; }
    
        public virtual Course Course { get; set; }
        public virtual Student Student { get; set; }
    }
    


    In Addition, both the Student & Course Models have:

    public virtual ICollection<Enrollment> Enrollments { get; set; }
    

    The virtual keyword permits lazy loading.

    If you need further assistance or explanation please ask.

    Thursday, November 17, 2011 12:24 PM
  • Hi Scotty,

    Thanks a lot for your help. I think it is starting to sink in how to work with it.

    So, if I wanted to update the grade for a particular student, would that work?

    var student = db.Students.First();
    student.Enrollments.Grade = 100
    enrollment

    Or do I have to know both the student and course ids?

    var student = db.Students.First();
    var course = db.Courses.First();

    var enrollment = db.Enrollments.Where(s=>s.StudentID == student.StudentID).Where(c=>c.CourseID == couser.CourseID);
    enrollment.Grade = 100
    db.SaveChanges();

    Which one is right, if any of them is right? :-)

    Another question I have is regarding using an ADO.NET Data Service to fetch and update data. In these examples they do not use services. Where would I put the service in this structure? Basically, I would like my ASP.NET app not to access the EF directly. I would like it to use a service.

    Is this possible?

    Cheers

    C

     

     

    Thursday, November 17, 2011 1:57 PM
  • Hi Claudio,

    You would have to know both Id's.  Otherwise which course has the grade changed for?

    So your second example is correct.  It is worth noting that you do have a navigation Property on the Student Entity.  So if you already have your student Entity you can get to the course by walking the navigation tree.  For instance:

    var student = db.Students.First();
    var enrollment = student.Enrollments.First();
    var course = enrollment.Course;
    
    // You can also walk the other way:
    var course = db.Courses.First();
    var enrollment = course.Enrollments.First();
    var student = enrollment.Student;
    
    

    Intellisense works great with walking the navigation properties so you can easily see what is available to you as you just start typing in the IDE.

     

    As for your second question:  Generally we implement a pattern to create a separation.  For instance the Repository Pattern.  Your ASP.Net application would be accessing the Repository which in turn accesses the data (or in your case the service).  You can search MSDN for examples as there are many different versions.

    I have exactly 0 experience working with services, so I cannot guide you further on that.  As you have a new question now I would suggest creating a new thread with that as your question.  And closing this thread by marking the posts that best answer your question so others with a similar question can find answers easily.

    Here is a link to the Data Platform Development Category's Forum Root so you can ask your service-based question there.

    http://social.msdn.microsoft.com/Forums/en-US/category/dataplatformdev

     

    Thursday, November 17, 2011 2:22 PM
  • Hi Scotty,

    Thanks very much. I will ask the other question using the forum you suggested.

    Just for completeness could please explain to me why you use the First() method to get the first Enrollment property of the student.Enrollments collection?

    var
    enrollment = student.Enrollments.First();

    Since the Enrollments collection could have more than one entry for the same student, using first does not seem right to me. What am I missing here? Would this return the right course?

    var course = enrollment.Course;

    Cheers

    C

    Thursday, November 17, 2011 3:33 PM
  • Hi Claudio,

    >Just for completeness could please explain to me why you use the First() method to get the first Enrollment property of the student.Enrollments collection?

    It was simply an example that would compile.  It wouldn't be very useful when you used it in the real world!  I should have explained that better.

    Normally you would need some logic to identify the Entity you want.  For instance, using LINQ:

    var enrollment = student.Enrollments.Where(x => x.CourseId == 5).FirstOrDefault();
    

    That will get the Enrollment for the Student Entity where the CourseId of the Enrollment is 5.  FirstOrDefault returns the Default value if the count is 0.  So if there was no enrollment for that student with the CourseId of 5, the enrollment variable would be null.

    Assuming that the variable isn't null you can then set the Grade property:

    enrollment.Grade = 100;
    

     

    >Would this return the right course? var course = enrollment.Course;

    In fact it will!  This is known as a Navigation Property.  It allows you to walk to another entity very easily.  This works here because an enrollment is only every associated to a single course.  It's also only ever associated to a single Student.  So you can get to the student in the same way:

    var student = enrollment.Student;
    


     

    Thursday, November 17, 2011 3:49 PM
  • Thank you!!

    Thursday, November 17, 2011 4:32 PM