CODEWITHSUNDEEP
c#vb.netentity-framework
Kalin Krastev
Kalin Krastev

Reputation: 648

Entity Framework DB First generate many to many intermediate table WITHOUT having extra columns in it

It is clear to me that if I have a many to many relationship without other than the foreign key columns in the intermediate table, then EF will generate entities only for the main tables placing virtual collections in each to represent the relationship. And when I have a many to many relationship containing extra columns EF will generate an entity for the intermediate table in order to give access to those extra columns. I need to know is there a way to have the intermediate table entity generated without having any extra columns in the table.

In other words:

Let's assume we have the following relationship: Schools-StudentSchool-Students. If I know the SchoolId how am I supposed to get all the students that DON'T study in that school without having the intermediate table. (In the object model having only a collection of the students currently studying in the school). What am I missing in the whole picture?

Upvotes: 0

Views: 466

Answers (1)

Lyubomir Dimov
Lyubomir Dimov

Reputation: 141

I believe that EF will remain with two tables if and only if the many-to-many relation is Total participation from both sides.

In such case, SQL:

Select *
From Students
Where StudentId Not IN( Select StudentId
                        From   Schools                    
                        Where  schoolId = x)

or lambda

var SchoolsRequested = Schools.Where(x=>x.schoolId = x);
var notWantedStudentIds = SchoolsRequested.Select(x=>x.StudentId).ToList();
Students.Where(x=> !notWantedStudents.Contains(x));

Upvotes: 1

Related Questions