How to get yet-to-be-created ID in Linq/Entity framework

Question

I have two table like this:

**Complaint**
-Id
-CreatedBy
-CreatedDate
....

**Solution**
-Id
-ComplaintId

Sometimes, a complaint has an instant solution, which means, when it is created, a solution is also created. The Database is Oracle, and to insert new record into database, I set the StoredGeneratePattern to Identity and use trigger to insert a sequence's value.

here my code:    

using (var context = new Entities())
        {
        var complaint = new Complaint
                            {
                                Title = TitleTextBox.Text.Trim(),
                                CreatedBy = CurrentUser.UserID,
                                Description = DescriptionTextBox.Text.Trim(),
                                ServiceId = Convert.ToDecimal(ddlService2.Value),
                                Contact = ContactTextBox.Text.Trim(),
                                CreatedDate = DateTime.Now,
                                Customer = txtUserName.Text.Trim(),
                                ResellerId = CurrentUser.ResellerID,
                                Status = ComplaintStatus.GetStatusCode("New complaint")
                            };
        if (CompletedCheckBox.Checked)
        {
            complaint.Status = ComplaintStatus.GetStatusCode("Completed");
            var solution = new Solution
                               {
                                   CreatedBy = CurrentUser.UserID,
                                   CreatedDate = DateTime.Now,
                                   SolutionDesc = DescriptionTextBox.Text,
                                   ComplaintId = complaint.Id
                               };            
            context.Solutions.AddObject(solution);

        }

            context.Complaints.AddObject(complaint);
            if(context.SaveChanges() > 0)
            {
                ResetFrom();
                return true;
            }

        }

the problem is, I can't get the id of newly created complaint to set the field in the solution. How can I do that?

Thank you.

Answer 1

Could you not perform the first operation call SaveChanges() and then query your complaint object which should now have a complaintID.

Answer 2

Assuming you are using a trigger/sequence with Oracle, you will need to do a get after you save changes to get an object with the Id populated. If you are not using a trigger, you can set the Id manually on the new object by getting the next value from the sequence.

Answer 3

If you add the complaint and SaveChanges() before you create the solution the complaint object will have the Identity value, then after creating the solution add it to the context and call SaveChanges() a second time.

context.Complaints.AddObject(complaint);


if (CompletedCheckBox.Checked)
    {

        complaint.Status = ComplaintStatus.GetStatusCode("Completed");
        context.SaveChanges();

        var solution = new Solution
                           {
                               CreatedBy = CurrentUser.UserID,
                               CreatedDate = DateTime.Now,
                               SolutionDesc = DescriptionTextBox.Text,
                               ComplaintId = complaint.Id
                           };            
        context.Solutions.AddObject(solution);

    }


        if(context.SaveChanges() > 0)
        {
            ResetFrom();
            return true;
        }

Also if you were to add a Foreign Key Relationship Between the Solution and the Complaint, you would no set the ComplaintId, you would just set solution.Complaint = complaint and the Ids would be set correctly during the save.

Answer 4

The answer is actually easy. In this case I do not believe you need the ID at all (at least not just to add this relationship), but in case you do, do this:

Make sure you have the ID on the Complaint entity to refresh on Insert (We use DevArt, but I forget the exact setting name here, but if you select the ID on the entity you should see an UpdateMode or something like that that needs to be set to UpdateOnInsert i think), then

To just insert this relationship do this:

using (var context = new MyContext())
{
  var complaint = new Complaint {...};
  context.Complaints.AddObject(complaint);
  var solution = new Solution {..., Complaint = complaint};
  context.Solutions.AddObject(solution);
  context.SaveChanges();
}

You will not want to do SaveChanges twice as that requires a separate transactionscope. This way you don't need it.

Answer 5

You can add the Complaint to the Solutions "Complaint" navigation property.

So create your solution object like you are doing then do the following:

Soltion.Complaint = newCreatedComplaintObject;