EF 6 and many to many relationship

Question

i m working on EF 6 (mapping with many to many relationship) ,see

https://www.codeproject.com/Articles/234606/Creating-a-Many-To-Many-Mapping-Using-Code-First

where it created "PersonCourses"  as middle table ,now i have two problem with that In a many-to-many relationship EF manages the join table internally and hidden. It's a table without an Entity class in your model. 

so what if i need to access "PersonCourses"   in my code (project) and what if i  need to add certain columns with it ??

Answer 1

EF can auto-manage the joining table so long as it just contains the two FKs as a composite PK. If you want to add to that then you need to declare the joining table as an entity with a one-to-many from each side.

So instead of:

[Table("Persons")]
public class Person
{
    // ...
    public virtual ICollection<Course> Courses { get; set; } = new List<Course>();
}

[Table("Courses")]
public class Course
{
    // ...
    public virtual ICollection<Person> People { get; set; } = new List<Person>();
}

modelBuilder.Entity<Person>()
    .HasMany(x => x.Courses)
    .WithMany(x => x.People);

you would have:

[Table("Persons")]
public class Person
{
    // ...
    public virtual ICollection<PersonCourse> PersonCourses { get; set; } = new List<PersonCourse>();
}

[Table("Courses")]
public class Course
{
    // ...
    public virtual ICollection<PersonCourse> PersonCourses { get; set; } = new List<PersonCourse>();
}

[Table("PersonCourses")]
public class PersonCourse
{   
    [Key, Column(Order=0), ForeignKey("Person")]
    public int PersonId { get; set; }
    [Key, Column(Order=1), ForeignKey("Course")]
    public int CourseId { get; set; }

    // ... any additional properties for the entity.

    public virtual Person Person { get; set; }
    public virtual Course Course { get; set; }
}


modelBuilder.Entity<Person>()
    .HasMany(x => x.PersonCourses)
    .WithRequired(x => x.Person);
modelBuilder.Entity<Course>()
    .HasMany(x => x.PersonCourses)
    .WithRequired(x => x.Course);

The main disadvantage of this is that Person no longer has a collection of Courses, but of PersonCourses so you have to dive the extra level in your projections every time you want to get details about course names. It might be tempting to leave the PersonCourses collection on Person named as "Courses" but I found that this can get misleading as you can end up with collections on some objects called Persons being Person vs. PersonCourse or Courses being Course vs. PersonCourse. It's generally less confusing when the collection name reflects the type.

So instead of:

var courses = context.Persons
    .Where(x => x.PersonId == personId)
    .SelectMany(x => x.Courses)
    .ToList();

You need to change that to:

var courses = context.Persons
    .Where(x => x.PersonId == personId)
    .SelectMany(x => x.PersonCourses.Select(pc => pc.Course))
    .ToList();

Update: To have an Id column on PersonCourse:

[Table("PersonCourses")]
public class PersonCourse
{   
    [Key]
    public int Id { get; set; }
    [ForeignKey("Person")]
    public int PersonId { get; set; }
    [ForeignKey("Course")]
    public int CourseId { get; set; }

    // ... any additional properties for the entity.

    public virtual Person Person { get; set; }
    public virtual Course Course { get; set; }
}

... or better, do away with the FK fields in the entity and map them via configuration:

[Table("PersonCourses")]
public class PersonCourse
{   
    [Key]
    public int Id { get; set; }

    // ... any additional properties for the entity.

    public virtual Person Person { get; set; }
    public virtual Course Course { get; set; }
}

EF6 may map these automatically by convention, but explicitly you can use:

modelBuilder.Entity<Person>()
    .HasMany(x => x.PersonCourses)
    .WithRequired(x => x.Person)
    .Map(x => x.Mpakey("PersonId");
modelBuilder.Entity<Course>()
    .HasMany(x => x.PersonCourses)
    .WithRequired(x => x.Course)
    .Map(x => x.Mpakey("CourseId");

I recommend this approach, like Shadow Properties with EF Core to avoid having 2 sources of truth for the Person ID or Course ID.

I.e. PersonCourse.PersonId vs. PersonCourse.Person.Id

When updating entities with navigation properties, you should update references via the navigation property, (personCourse.Course = newCourse) not via a FK property. (personCourse.CourseId = newCourseId) Doing so, or intermixing the source of truth for the FK can lead to weird results depending on what the DbContext is tracking at the time.