Many to many relationship with junction table in Entity Framework?

Question

I'm trying to create a many-to-many relationship in Entity Framework (code first), according to the following post: Database design for limited number of choices in MVC and Entity Framework?

However, I can't get it to work properly, and I'm sure I'm doing something very simple the wrong way. Here's the diagram I have no from my attempts:

The point of the junction table is that I need to have an extra property, Level, in the relationship, so I can't just go with a direct relationship between Consultant and Program. I added the ConsultantProgramLink entity manually in the designer, and then added associations to Program and Consultant respectively, selecting to add a FK for each, and then made them both primary keys. But when I do it like this it doesn't work as I expected:

If I had done a direct association between Consultant and Program, I would have been able to refer to, say, Consultant.Programs in my code. But that doesn't work now with the junction table. Is there any way to remedy this, or do I always have to go through the junction property (Consultant.ConsultantProgramLink.Programs)? In any case, even if I do try to go through the junction property it doesn't help. I can do Consultant.ConsultantProgramLink in my code, but another dot doesn't give me the navigation property Programs (which for some reason also became simply Program, why? Can I just rename them if I eventually get access to them at all?).

So what am I doing wrong? Why can't I access the properties through dot notation in my code?

Lili

Answer 1

Once you model a junction table as an entity you indeed lose direct many-to-many relation between Consultant and Program. That is how it works. You will either have direct many-to-many relation or additional properties in the junction table. Not both. If you want both you can try creating custom Programs property on Consultant and use linq query to get related programs:

public IEnumerable<Program> Programs
{
    get
    {
        return this.ConsultantProgramLinks.Select(l => l.Program);   
    }
}

The example is also the explanation of your last problem. You can't have Program property on ConsultantProgramLink because it is a collection of related entities, not single entity (it should be called ConsultantProgramLinks). The property in ConsultantProgramLink entity is called simply Programbecause it represents single entity not collection.

Edit:

If you need each Program to be automatically associated with each Consultant you must enforce it when you are going to create new Program. Having junction table exposed as separate entity will probably allow you achieving it easily:

var program = new Program();
...
context.Programs.AddObject(program);

var ids = from c in context.Consultants
          select c.Id;

foreach (var id in ids)
{
    var link = new ConsultantProgramLink
        {
            ConsultantId = id,
            Program = program
        };
    context.ConsultantProgramLinks.AddObject(link);
}

context.SaveChanges();

If you add new Consultant you will have to create links to all programs in the same way.

The disadvantage is that if you have for example 1000 consultants this construct will create 1001 database inserts where each insert will be executed in separate roundtrip to the database. To avoid it the only option is either use stored procedur or trigger on Program table.

Unspent

Ok, thanks, that makes sense. But I'm wondering if I'm thinking about this all wrong? I can't figure out how to actually do what I want with this: – Lili

Say I create a new Consultant. I want that consultant to have all Programs in its Programs property. And then I want the user to be able to set the level of proficiency for each of these. But 1: how do I assign all Programs in the Program table to each Consultant? And 2: what if I add new Programs to the Programs table (which I want to be able to do)? Do you see what I need? A list of programs such as Adobe Illustrator, MS Word, etc, and this list should be extensible. But each Consultant should always "have" all Programs in their Programs property, and only set the level on editing profile... – Lili

Thank you so much, I appreciate that, I can't grasp it...! – Lili