EF Code First Additional column in join table for ordering purposes

Question

I have two entities that have a relationship for which I create a join table

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Image> Images { get; set; }
}


public class Image
{
    public int Id { get; set; }
    public string Filename { get; set; }

    public virtual ICollection<Student> Students { get; set; }
}


protected override void OnModelCreating(DbModelBuilder modelBuilder)
{

        modelBuilder.Entity<Student>()
            .HasMany(i => i.Images)
            .WithMany(s => s.Students)
            .Map(m => m.ToTable("StudentImages"));
}

I would like to add an additional column to allow chronological ordering of the StudentImages.

Where should I add insert the relevant code?

score 24 · Accepted Answer · edited Feb 14 '13 at 20:58

24

Do you want to use that new column in your application? In such case you cannot do that with your model. Many-to-many relation works only if junction table doesn't contain anything else than foreign keys to main tables. Once you add additional column exposed to your application, the junction table becomes entity as any other = you need third class. Your model should look like:

public class StudentImage 
{
    public int StudentId { get; set; }
    public int ImageId { get; set; }
    public int Order { get; set; }
    public virtual Student Student { get; set; }
    public virtual Image Image { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<StudentImage> Images { get; set; }
}


public class Image
{
    public int Id { get; set; }
    public string Filename { get; set; }
    public virtual ICollection<StudentImage> Students { get; set; }
}

And your mapping must change as well:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<StudentImages>().HasKey(si => new { si.StudentId, si.ImageId });

    // The rest should not be needed - it should be done by conventions
    modelBuilder.Entity<Student>()
                .HasMany(s => s.Images)
                .WithRequired(si => si.Student)
                .HasForeignKey(si => si.StudentId); 
    modelBuilder.Entity<Image>()
                .HasMany(s => s.Students)
                .WithRequired(si => si.Image)
                .HasForeignKey(si => si.ImageId); 
}

edited Feb 14 '13 at 20:58

Gert Arnold

105,341
31
202
291

answered Sep 02 '11 at 21:14

Ladislav Mrnka

360,892
59
660
670

hmmm, this means I going to lose access to Student.Images etc. I wonder can I keep what I have at the moment and create a separate StudentImagesOrder entity and let code first create this and store this information there? – Nicholas Murray Sep 03 '11 at 12:36
Having the same problem one year later and this being one of the top hits on google when searching for it - i wonder if its still the the current implementation that when adding the extra column i loose the many to many relationship. (in this case student.images ?) – Poul K. Sørensen Sep 30 '12 at 23:50
@s093294: Yes the situation is same and it will not change. If you add a new column to junction table you need a new entity instead of direct many to many relation to have access to the new column. – Ladislav Mrnka Oct 01 '12 at 07:13
1

I added the extra column without telling EF and implementet a few stored procedures for when i need it. – Poul K. Sørensen Oct 01 '12 at 09:04
@LadislavMrnka it creates an additional table in my db with the keys from both sides. Similar to junction table without additional columns. Any idea? – Geethanga Feb 14 '13 at 14:21
@Geethanga: Are you sure that you have everything correctly configured? That should not happen. – Ladislav Mrnka Feb 14 '13 at 16:50
@LadislavMrnka I ran into another issue. Everything seems to be working fine until I needed to override a foreignkey to prevent cascade deletes. I did it by adding WillCascadeOnDelete(false). Then it gave me an error saying that the foreignkey already exist in the database. It seemed that adding "public virtual Student Student { get; set; }" automatically create the foreignkey when I commented out that part it worked without an error. Could you please check this. I'm using EF 5.0. Thanks in advance. – Geethanga Feb 18 '13 at 08:08
@Geethanga you should ask this in a new question. – user247702 Feb 19 '13 at 07:54

EF Code First Additional column in join table for ordering purposes

1 Answers1

Linked