Insert into many-to-many relationship tables

Question

Simple scenario

[ClientTable]: ClientId, ClientName, Phone, Age
[CityTable]: CityID, CityName, Country
[ClientCityTable]: ClientCityID, ClientID, CityID

Client client = new Client("John", 123456789, 40);
City city = new City("NY", USA);
ClientCity clientCity = new ClientCity(client, city);

Should I make InsertOnSubmit on every object(table) or only on clientCity? Or it doesn't matter? Where's the difference?

EDIT

I'ms asking if should I make

DatabaseDC dc = new DatabaseDC(connectionString);
dc.Client.InsertOnSubmit(client);
dc.City.InsertOnSubmit(city);
dc.ClientCity.InsertOnSubmit(clientCity);
dc.SubmitChanges();

or only

DatabaseDC dc = new DatabaseDC(connectionString);
dc.ClientCity.InsertOnSubmit(clientCity);//because this object has references to client and city
dc.SubmitChanges();

?

EDIT 2

I made a few attempts and even of I use InsertOnSubmit only on client, entries are inserted also to City and ClientCity. How should I do it correctly?

Answer 1

Typically you have to insure that the linked tables exist before the insert or you may get an error (depends if you have constrained your sql tables).

You can also create custom stored procedure calls for the inserts and updates, these procedures could insure the linked tables are correct.

IMHO linq-to-sql is good for making complicated selections but not so easy to use for updating the database. (Often I've seen it create serious performance bottlenecks.)

Answer 2

From the result, it really does not matter. Both ways are correct.

Advantages of the second (only one insertonsubmit)

• cleaner
• less code

Advantage of the first one

• for people who are new to Linq2sql easier to read/understand
• also if there are no correct references, an insert will be done (or is this a disadvantage?, up to you)

As you already found out, the result is the same.