3

I'm using the newest Entity Framework and ran into a problem with Many To Many Relationship when I want to create an extra column.

The issue is the same raised in this older post: EF Code First Additional column in join table for ordering purposes

Is it still the problem today that one can not add an extra column without loosing the many to many relation ship (link from object A to B as A.B because the mapping becomes and entity it self) ?

What are the work a rounds ?

  1. Look up the a of class A I need and then query for mapping table where(e=>e.A == a) to get my Bs? And when I need the extra colums i would do MappingTable.find(a,b) ?
  2. Are there other modeling options, linq to sql that would make it easier ?
Community
  • 1
  • 1
Poul K. Sørensen
  • 16,950
  • 21
  • 126
  • 283
  • Can't you add the column to either of the tables? Do you actually need to add to the join table? – Sidharth Mudgal Oct 01 '12 at 00:07
  • I think i do. Having A and B, A is a set of images, B is a set of questions. Each images can have multiply questions. And in the mapping table i would like to add the correct answer. I don't see how that can be added to either one of the tables. and creating a new table don't make sense as it really is a shared property for the specific relation. – Poul K. Sørensen Oct 01 '12 at 00:13
  • Isn't that a one to many relation? – Sidharth Mudgal Oct 01 '12 at 00:15
  • The same question can be used for multiply images. So one question have many images, and a image have multiply quests. I guess thats many to many? – Poul K. Sørensen Oct 01 '12 at 00:17

2 Answers2

0

As far as I know things haven't changed with EF 5. You would need to do it as the link says to. I like to stick with EF as its easy to use, but that's just my opinion...

Sidharth Mudgal
  • 4,234
  • 19
  • 25
  • sad panda! is the solution to do a normal many to many then and create one entry with foreign keys to the mapping table. (From a sql view this sounds stupid ) Would it be possible to create a normal many to many and then add the column without entity framework knows about it and model an extra entity mapping to the table without navigation link? – Poul K. Sørensen Oct 01 '12 at 01:08
  • @s093294 - give it a try and find out. Please report back with your findings. – Erik Funkenbusch Oct 01 '12 at 01:42
  • I did: http://stackoverflow.com/questions/12666608/how-do-i-assosiate-my-pico-class-with-a-specific-table-in-my-database-with-entit and the problem is that i dont know how to tell it to use the already existing table and not create it. Getting errors that it cant create it because its already there – Poul K. Sørensen Oct 01 '12 at 01:45
  • If you make any changes to the db manually, EF either recreates the db or ignores it. – Sidharth Mudgal Oct 01 '12 at 01:49
  • I didnt make em manually, i just to EF to create the column in its Up() script. Now the Many to Many works and it has the extra column, i just need a way to query for it. – Poul K. Sørensen Oct 01 '12 at 02:10
0

I had the same problem. What I did to work-around it was create another derivative DbContext specifically to handle joins. I.E.:

public class JoinContext : DbContext
{
    internal JoinContext() : base("name=SampleConnectionString")
    {
        PreventErrorIfDatabaseSchemaChanges();

        // Get the ObjectContext related to this DbContext
        var objectContext = (this as IObjectContextAdapter).ObjectContext;
    }

    public DbSet<StudentImage> StudentImages { get; set; }

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

        base.OnModelCreating(modelBuilder);
    }
    private static void PreventErrorIfDatabaseSchemaChanges()
    {
        Database.SetInitializer<JoinContext>(null);
    }
}

I left the other application context with the Student/Image many-to-many join mapping as-is. Don't forget to specify a compounded key for the join table (refer to HasKey method above), else EF bombs on databse initialization.

After you have your special join context, use a repository to access this context and get or set the desired fields from mapped join table:

public class StudentRepository
{
    public int GetImageSortOrder(int studentId, int imageId)
    {
        var joinContext = new JoinContext();

        var joinTuple = joinContext.StudentImages.Find(studentId, imageId);

        return joinTuple.SortOrder;
    }
}

Hope this helps!

Nautic20
  • 215
  • 4
  • 15