locked
Add a field to a join-table RRS feed

  • Question

  • I would like to add a field to a join table using EF code first. If I'm looking at this sample there is no POCO for the join table since the EF automagically creates it. I would like to add a pass/fail boolean field to the join table so i could easily tell if a student passed or failed a course. 

    In searching the web, it seems I have to manually create another class, probably "PersonCourse" with an Id, the two foreign keys, and a pass/fail boolean. What would this class look like or is there another way of doing this?

    Here is my best guess on the class

        public class PersonCourse
        {
            public int PersonCourseId { get; set; }
            public int CourseId { get; set; }
            public int StudentId { get; set; }
            public bool Passed { get; set; }
        }
    

    Now would I have to utilize any fluent api mappging to get this to work properly and is the structure of the "PersonCourse" class correct? Thanks in advance.

    Tuesday, April 10, 2012 6:39 PM

Answers

  • Hi BBauer42,

    You're on the right track. If you were to add a DBSet<PersonCourse> to the SchoolContext as defined in that example you'd end up with two tables in the DB, one named PersonCourse with the two foreign keys and one named PersonCourse1 with the fields you've defined.

    In this situation the Person and Course entities will no longer have Navigation Properties pointing to one another, they'll now both have a collection of PersonCourses, which will in turn have Navigation Properties to both its related Person and Course. The following is the code adapted from the example:

        public class SchoolContext : DbContext
        {
    
            public DbSet<Course> Courses { get; set; }
            public DbSet<Person> People { get; set; }
            public DbSet<PersonCourse> PersonCourses { get; set; }
    
            public SchoolContext() : base("MyDB")
            {
            }
        }
    
        public class PersonCourse
        {
            public int PersonCourseId { get; set; }
            public Course Course { get; set; }
            public Person Person { get; set; }
            public bool Passed { get; set; }
        }
    
        public class Person
        {
            public int PersonId { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
    
            public ICollection<PersonCourse> CoursesAttending { get; set; }
    
            public Person()
            {
                CoursesAttending = new HashSet<PersonCourse>();
            }
        }
    
        public class Course
        {
            public int CourseId { get; set; }
            public string Title { get; set; }
    
            public ICollection<PersonCourse> Students { get; set; }
    
            public Course()
            {
                Students = new HashSet<PersonCourse>();
            }
        }

    You shouldn't need to use Fluent API to accomplish this, unless you're wanting to change any of the default field or table names generated/mapped in the DB.

    Regards,

    Tyler

    • Proposed as answer by Alan_chen Wednesday, April 11, 2012 2:26 AM
    • Marked as answer by BBauer42 Thursday, April 12, 2012 12:31 PM
    Wednesday, April 11, 2012 12:40 AM

All replies

  • Hi BBauer42,

    You're on the right track. If you were to add a DBSet<PersonCourse> to the SchoolContext as defined in that example you'd end up with two tables in the DB, one named PersonCourse with the two foreign keys and one named PersonCourse1 with the fields you've defined.

    In this situation the Person and Course entities will no longer have Navigation Properties pointing to one another, they'll now both have a collection of PersonCourses, which will in turn have Navigation Properties to both its related Person and Course. The following is the code adapted from the example:

        public class SchoolContext : DbContext
        {
    
            public DbSet<Course> Courses { get; set; }
            public DbSet<Person> People { get; set; }
            public DbSet<PersonCourse> PersonCourses { get; set; }
    
            public SchoolContext() : base("MyDB")
            {
            }
        }
    
        public class PersonCourse
        {
            public int PersonCourseId { get; set; }
            public Course Course { get; set; }
            public Person Person { get; set; }
            public bool Passed { get; set; }
        }
    
        public class Person
        {
            public int PersonId { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
    
            public ICollection<PersonCourse> CoursesAttending { get; set; }
    
            public Person()
            {
                CoursesAttending = new HashSet<PersonCourse>();
            }
        }
    
        public class Course
        {
            public int CourseId { get; set; }
            public string Title { get; set; }
    
            public ICollection<PersonCourse> Students { get; set; }
    
            public Course()
            {
                Students = new HashSet<PersonCourse>();
            }
        }

    You shouldn't need to use Fluent API to accomplish this, unless you're wanting to change any of the default field or table names generated/mapped in the DB.

    Regards,

    Tyler

    • Proposed as answer by Alan_chen Wednesday, April 11, 2012 2:26 AM
    • Marked as answer by BBauer42 Thursday, April 12, 2012 12:31 PM
    Wednesday, April 11, 2012 12:40 AM
  • Thanks, this works great. However, now I am having trouble loading seed data correctly. This is what I have tried.

    Person johnSmith = new Person { LastName = "Smith", FirstName = "John" };
    Person marySmith = new Person { LastName = "Smith", FirstName = "Mary" };
    
    Course physics = new Course { Title = "Physics" };
    Course math = new Course { Title = "Math" };
    
    PersonCourse johnPhysics = new PersonCourse { Course = physics, Person = johnSmith, Passed = true };
    PersonCourse johnMath = new PersonCourse { Course = math, Person = johnSmith, Passed = true };
    PersonCource maryPhysics = new PersonCourse { Course = physics, Person = marySmith, Passed = false };
    
    johnSmith.CoursesAttending = new List<PersonCourse> { johnPhysics, johnMath };
    marySmith.CoursesAttending = new List<PersonCourse> { maryPhysics };
    
    physics.Students = new List<PersonCourse> { johnPhysics, maryPhysics };
    math.Students = new List<PersonCourse> { johnMath };
    

    I create the person, then the course, then the PersonCourse relationship, then populate the ICollection<PersonCourse> on each of the Person and Course objects. However, I don't seem to have navigation when running the project, but my databases seem to have been created correctly. What am I missing? 

    Thanks for the help!

    Wednesday, April 11, 2012 1:08 PM
  • Hi BBauer42,

    I modified your code in seed method and it works here:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.Entity;
    
    namespace JoinTable
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db= new SchoolContext())
                {
                  var test=  db.People.ToList();
                }
            }
        }
        public class SchoolContext : DbContext
        {
            static SchoolContext()
            {
                Database.SetInitializer(new SchoolContextInitializer());
            }
            public DbSet<Course> Courses { get; set; }
            public DbSet<Person> People { get; set; }
            public DbSet<PersonCourse> PersonCourses { get; set; }
    
            public SchoolContext()
                : base("MyDB")
            {
            }
        }
    
        public class PersonCourse
        {
            public int PersonCourseId { get; set; }
            public Course Course { get; set; }
            public Person Person { get; set; }
            public bool Passed { get; set; }
        }
    
        public class Person
        {
            public int PersonId { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
    
            public ICollection<PersonCourse> CoursesAttending { get; set; }
    
            public Person()
            {
                CoursesAttending = new HashSet<PersonCourse>();
            }
        }
    
        public class Course
        {
            public int CourseId { get; set; }
            public string Title { get; set; }
    
            public ICollection<PersonCourse> Students { get; set; }
    
            public Course()
            {
                Students = new HashSet<PersonCourse>();
            }
        }
        public class SchoolContextInitializer :
    
                     DropCreateDatabaseIfModelChanges<SchoolContext>
        {
    
            protected override void Seed(SchoolContext context)
            {
    
                Person johnSmith = new Person { LastName = "Smith", FirstName = "John" };
                Person marySmith = new Person { LastName = "Smith", FirstName = "Mary" };
    
                Course physics = new Course { Title = "Physics" };
                Course math = new Course { Title = "Math" };
    
                PersonCourse johnPhysics = new PersonCourse { Course = physics, Person = johnSmith, Passed = true };
                PersonCourse johnMath = new PersonCourse { Course = math, Person = johnSmith, Passed = true };
                PersonCourse maryPhysics = new PersonCourse { Course = physics, Person = marySmith, Passed = false };
    
                context.PersonCourses.Add(johnPhysics);
                context.PersonCourses.Add(johnMath);
                context.PersonCourses.Add(maryPhysics);
                context.SaveChanges();      
            }
        }
    }
    

    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Alan_chen Thursday, April 12, 2012 9:19 AM
    Thursday, April 12, 2012 9:03 AM