Can Entity Framework handle many to many relationship without an intersection object?

Question

Using database first model: Let's say we have the classic tables Student, Course and StudentCourse (the latter obviously having FKs to Student and Course).

If you import this model to EF, you will get an object generated for each of them. The Student and Course classes will each have a collection of StudentCourses, from which you need to jump another relationship to get to the Course or Student, respectively.

I would like to have the code generated in such a way that the underlying intersection table is invisible, i.e. Student has a collection of Courses, and Course has a collection of Students. I have seen this done in other ORM software (specifically, TopLink). Can it be done in EF?

Ballard

So just exempt the table1table2 model? – Typhoon

It's certainly possible with code-first, so I would assume it's possible with database-first. – Hotheaded

Does your StudentCourses table have any additional columns? This article seems to suggest you'll get the desired behaviour if it doesn't: learnentityframework.com/LearnEntityFramework/tutorials/… – Hotheaded

@RichardDeeming - Hm, I don't have any other meaningful columns, but I do have a separate autonumbered ID field. Strictly speaking I could make a composite PK and drop the ID field, if that's necessary... – Ballard

From what I've read, dropping that column would work. A composite PK is usually a better choice for this sort of table anyway. – Hotheaded

@RichardDeeming - you're right, it works. Please put this as an answer so I can give you credit. Thanks! – Ballard

Answer 1

According to this tutorial, you'll get the desired behaviour if your StudentCourse table only contains the foreign-key columns. If it contains any other columns, EF will generate an intermediate entity to represent the join.

In this case, dropping the surrogate key from the StudentCourse table and replacing it with a composite primary key should work.

Hotheaded

Basically, anytime you have an extra "attribute" on the relationship, you will get the extra entity in EF. You just have to think of the surrogate key as an "attribute". The other side of the coin is that if you want attributes, this extra entity is a musthave. (Think of Employee to JobTitle relationship, but the ~relationship has a "StartedOnDate" attribute. Thus you must have this extra entity. – Boneyard

Answer 2

You can do it in EF Code First using ICollections. For example:

public class Student 
{ 
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Course> Courses { get; set; }

    public Student()
    {
        Courses = New HashSet<Course>();
    }
}

Repeat for Course and swap it all over. This will create three tables in your database (Student, Course and StudentCourse) with a m-to-m relationship. Most importantly StudentCourse will be an invisible linking table that has no Entity in your model.

Dominic

There's a load of scuttlebutt on the web about it. The most useful article I found was this one by Prashant Brall. – Dominic

Correct, as long as the ~relationship does not have any extra attributes. See my comment on the richard-deeming answer. – Boneyard