Sql – Linq to sql causing foreign key constraint errors

foreign-key-relationshipforeign-keyslinq-to-sql

Here is the revelant code.

       TournamentTeam newTeam = new TournamentTeam();
        TournamentTeams.InsertOnSubmit(newTeam);
        SubmitChanges();

        TournamentParticipant newSignup = new TournamentParticipant
        {
            CheckedIn = false,
            TournamentID = tournamentId,
            UserID = participant.UserID,
            TeamID = newTeam.TeamId
        };

        TournamentParticipants.InsertOnSubmit(newSignup);
        SubmitChanges();

TournamentParticipants.TeamId has a fk relationship on TournamentTeam.TeamID, TeamID is an identity column

What I don't understand is that when TournamentTeam gets inserts, it grabs the new identity value. Even when I debug the code new Signup is recieving the new team id. But when it comes to generating the insert, it avoids this value completely and does the insert before the select statement where it grabs the new identity column.

exec sp_executesql N'INSERT INTO [dbo].[TournamentParticipants]([UserID], [TournamentID], [CheckedIn]) VALUES (@p0, @p1, @p2)

SELECT [t0].[TeamID] FROM [dbo].[TournamentParticipants] AS [t0] WHERE ([t0].[UserID] = @p3) AND ([t0].[TournamentID] = @p4)',N'@p0 int,@p1 int,@p2 bit,@p3 int,@p4 int',@p0=29805,@p1=247,@p2=0,@p3=29805,@p4=247

How can I either make linq to sql use the value for team id that I have specified or make the select statement be generated before the insert statement?

Best Answer

Instead of setting the TeamID, set the Team entity to the one you just created. Delay the SubmitChanges to insert both, then it will fix up the ids when the insert is done.

TournamentTeam newTeam = new TournamentTeam();
TournamentTeams.InsertOnSubmit(newTeam);

TournamentParticipant newSignup = new TournamentParticipant
{
    CheckedIn = false,
    TournamentID = tournamentId,
    UserID = participant.UserID,
    Team = newTeam
};

TournamentParticipants.InsertOnSubmit(newSignup);
SubmitChanges();
Related Topic