How to insert rows in tables having many-to-many relation in linqtosql

Question

I am new to linqtosql. I have a database schema where,

An employee can belong to more than one team and team can belong to more than one employee.

So actually I have a,

Employee table: EmpID(PK),EmpName, etc..

EmployeesTeam table: EmpID(FK),TeamID(FK) (These two make composite PK)

Team table: TeamID(PK),TeamName,etc

I added rows to Employee and Team tables, but I dont know how can I add rows to the EmployeesTeam table.

Answer 1

Unlike EF, LINQ-to-SQL is very much a direct layer on top of your tables. You should have an EmployeeTeams entry on the data-context, so you can just add objects to that:

ctx.EmployeeTeams.InsertOnSubmit(
      new EmployeeTeam { Employee = emp, Team = team });

Alternatively, you may be able to use the navigation properties:

// this should alternatively be able to use the keys, instead of the objects
emp.Teams.Add(new EmployeeTeam {Employee = emp, Team = team});

In EF, you can hide these bridging tables (i.e. emp.Teams.Add(team)); but with LINQ-to-SQL you have to deal with them normally

Answer 2

somebody else solved this problem already:

Read these MSDN blog entries: Part 1, Part 2

HTH