Linq2Sql Insert Records To Related Tables

Question

Hi There,

Similar situation to : http://stackoverflow.com/questions/630320/how-to-add-several-dependent-records-with-linq2sql (this seems logical, but it doesn't work for me)

ASP.NET MVC + Linq2SQL

I have 2 tables called Challenges and Participants.

• Challenge { challengeId, ChallengeDesc, applicantId, respondantId }
• Participants { participantId, FirstName, LastName }

There are 1-many relationships between participants and Challenges - 1 for each key (applicantId, RespondantId).

I have an input form that collects all fields to create a new applicant and respondant and the challenge. The Databinder binds all the fields to Challenge and it's child participants correctly, however, the participant objects' names don't match the key names in the challenge object when browsing the structure created by Linq2Sql (applicantId matches with Participant object and respondantId matches with Participant1 object).

So when i try to SubmitChanges() after InsertOnSubmit(aChallenge) i get a foreign_key constraint validation message back from Linq2Sql. In SQL Server Profiler, I can see that the participants are being created properly, but when the challenge is being saved, the IDs of these newly inserted participants are not being set to the challenge object so the system is throwing a foreign key violation message.

How do i get past this?

Answer 1

You might want to edit you data objects (normally by using the DBML designer) and rename the Participant-typed properties to Applicant and Respondent respectively. It'll be easier to work with than having Participant and Participant1. You can do this in the association properties (the lines that connect the tables).

When you want to assign the foreign keys in Challenge, you have two choices. If you have the Participant objects themselves, you can assign them to the (newly renamed) Applicant and Respondent properties (and LINQ to SQL will update ApplicantID or RespondentID accordingly). Or if you have the ParticipantIDs, you can assign them to ApplicantID or RespondentID directly.

Answer 2

You have to write it this way I think (assuming you have classes Participant and Challenge):

Participant applicant = new Participant();
Participant respondant = new Participant();
//update your participants here

Challenge insertedChallenge = new Challenge();
//update your challenge values here

applicant.Challenges.add(insertedChallenge);
respondant.Challenges1.add(insertedChallenge);

submitChanges();

Linq-to-SQL should automatically assign these properties (Challenges and Challenges) so it can set the key values for you.

Hope it helps.