From many to many relationships with the join table in the Entity Framework?

Question

I am trying to create many-to-many relationships in the Entity Framework (code first), according to the following message: Database design for a limited number of options in MVC and Entity Framework?

However, I cannot get it to work correctly, and I am sure that I am doing something very simply wrong. Here, the diagram I have not from my attempts:

The point of the transition table is that I need to have an additional property, level, in the relationship, so I can’t just go with the direct relationship between the Consultant and the Program. I manually added the ConsultantProgramLink object to the constructor, and then added the associations to the program and the consultant, respectively, choosing to add FK for each, and then made them primary keys. But when I do it like this, it does not work as I expected:

If I had a direct connection between the Consultant and the Program, I could refer, say, to the Consultant. Programs in my code. But now this does not work with the connection table. Is there a way to fix this, or do I always need to go through the connection property (Consultant.ConsultantProgramLink.Programs)? In any case, even if I try to go through the connection property, this will not help. I can do Consultant.ConsultantProgramLink in my code, but the other point does not give me the navigation properties of the programs (which for some reason also became just a program, why? Can I just rename them if I end up accessing them at all ?).

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

Answer 1

Once you build the join table as an entity, you really lose the direct many-to-many relationship between Consultant and Program . Here's how it works. You will have either a many-to-many direct relationship or additional properties in the connection table. Not both. If you want, you can try creating your own Programs property on Consultant and use the linq query to get related programs:

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

The example also explains your last problem. You cannot have the Program property on ConsultantProgramLink , because it is a collection of related objects, not a separate entity (it should be called ConsultantProgramLinks ). A property in a ConsultantProgramLink object is simply called a Program because it is a single entity, not a collection.

Edit:

If you need each Program be automatically associated with each Consultant , you must force it when you plan to create a new Program . Having a connection table open as a separate entity is likely to allow you to easily reach it:

 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 a new Consultant , you will have to create links to all programs the same way.

The disadvantage is that if you have, for example, 1000 consultants, this design will create 1001 database inserts, where each insert will be performed in a separate reverse direction to the database. To avoid this, the only option is to either use a stored procedure or trigger in the program table.