How to make this linq to sql tranaction success

Question

how to add tranasction in this linq to sql code

MyDBDataContext DB = new MyDBDataContext();
            SurveyClient objMaster = new SurveyClient();
            objMaster.SurveyID = int.Parse(dtQuestions.Rows[0]["SurveyID"].ToString());
            MembershipUser myObject = Membership.GetUser();
            myObject.ProviderUserKey.ToString();
            objMaster.UserID = Guid.Parse(myObject.ProviderUserKey.ToString());  //Guid.Parse("993a109d-a0c7-4946-a8da-99fb594f3ce2");// current userID
            objMaster.SurveyDate = DateTime.Now;
            DB.SurveyClients.InsertOnSubmit(objMaster);
           // DB.SubmitChanges();

            foreach (DataRow dr in dtQuestions.Rows)
            {
                int currQueScore = GetAnswerScore(dr["AnswerType"].ToString().Trim(), dr["ClientAnswerValue"].ToString().Trim());
                dr["ClientAnswerScore"] = currQueScore;
                myScore += currQueScore;
                SurveyClientAnswer objDetail = new SurveyClientAnswer();
                objDetail.SurveyClientID = objMaster.SurveyClientID;
                objDetail.QuestionID = int.Parse(dr["QuestionID"].ToString());
                objDetail.Answer = dr["ClientAnswerValue"].ToString();
                objDetail.Score = int.Parse(dr["ClientAnswerScore"].ToString());
                DB.SurveyClientAnswers.InsertOnSubmit(objDetail);
               // DB.SubmitChanges();
            }
            objMaster.FinalScore = myScore;
            DB.SubmitChanges();

when i comment the two DB.SubmitChanges() it throws error

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SurveyClientAnswers_SurveyClientAnswers". The conflict occurred in database "NEXLEV", table "dbo.SurveyClient", column 'SurveyClientID'.
The statement has been terminated.

Answer 1

You were on the right path, but thinking too much "database oriented".

Indeed the objMaster does not have an ID yet, and that is causing the problem. But in Linq you should not assign a master-ID to a detail instead you assingn the master to the detail entity classes instead (or the other way around):

Sohe problem is this line:

objDetail.SurveyClientID = objMaster.SurveyClientID; 

it should be changed to

objDetail.SurveyClient = objMaster; 

In that case, there is no need for transaction management because it will all work in one SubmitChanges() and Linq will add the transaction for you and it is also smart enough to manage the setting of the ID's the correct way

[assumption: you have your foreign key constrains set up in the DB]

Answer 2

You do not have assigned the objMaster.SurveyClientID because it is not in the database and does not have a Id when you assign it to your SurveyClientAnwser. The first (commented) DB.SubmitChanges() does send the changes to the database, and the objMaster.SurveyClientID will be assigned (auto assign in your db). Then the objDetail.SurveyClientID = objMaster.SurveyClientID; line does assign a real id to you SurveyClientAnwser.

To make one transaction you have to make a transaction scope:

  MyDBDataContext DB = new MyDBDataContext();
  using (TransactionScope ts = new TransactionScope())
  {
      SurveyClient objMaster = new SurveyClient();
      objMaster.SurveyID = int.Parse(dtQuestions.Rows[0]["SurveyID"].ToString());
      MembershipUser myObject = Membership.GetUser();
      myObject.ProviderUserKey.ToString();
      objMaster.UserID = Guid.Parse(myObject.ProviderUserKey.ToString());  //Guid.Parse("993a109d-a0c7-4946-a8da-99fb594f3ce2");// current userID                  
      objMaster.SurveyDate = DateTime.Now;                  DB.SurveyClients.InsertOnSubmit(objMaster);
      DB.SubmitChanges();
      foreach (DataRow dr in dtQuestions.Rows)                 
      {
            int currQueScore = GetAnswerScore(dr["AnswerType"].ToString().Trim(), dr["ClientAnswerValue"].ToString().Trim()); 
            dr["ClientAnswerScore"] = currQueScore;
            myScore += currQueScore;  
            SurveyClientAnswer objDetail = new SurveyClientAnswer();                      
            objDetail.SurveyClientID = objMaster.SurveyClientID;   
            objDetail.QuestionID = int.Parse(dr["QuestionID"].ToString()); 
            objDetail.Answer = dr["ClientAnswerValue"].ToString(); 
            objDetail.Score = int.Parse(dr["ClientAnswerScore"].ToString());       
            DB.SurveyClientAnswers.InsertOnSubmit(objDetail);
            // DB.SubmitChanges(); //no need for this one
      }
      objMaster.FinalScore = myScore;
      DB.SubmitChanges();      

      ts.Complete();
  }