Insert new entity to context with identity primary key

Question

I want to insert a new record into my SQL table. I tried:

        public void CreateComment(int questionId, string comment)
        {
            QuestionComment questionComment = context.TableName.Create();//1*
            questionComment.propertyThatIsNotAConstraint= questionId;
            questionComment.body = comment;
            context.QuestionComments.Add(questionComment);
            context.SaveChanges();//ERROR...
        }

1* I'm surprised to see intellisense tell me: "Note that the new entity is not added or attached to the set"

Error Reads:

"Violation of PRIMARY KEY constraint 'PK_TableName'. Cannot insert duplicate key in object 'dbo.TableName'. The duplicate key value is (0).\r\nThe statement has been terminated."

The problem is that questionComment has its PK: questionComment.Id defaulted to 0. It needs to be the next available Identity or otherwise not populated and do a "normal" identity insert.

How does entity framework expect me to handle this scenerio?

As Requested:

//------------------------------------------------------------------------------
// <auto-generated>
//    This code was generated from a template.
//
//    Manual changes to this file may cause unexpected behavior in your application.
//    Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

    namespace Feedback.Models
    {
        using System;
        using System.Collections.Generic;

        public partial class QuestionComment
        {
            public int id { get; set; }
            public int questionId { get; set; }
            public string body { get; set; }
            public int commentIndex { get; set; }
        }
    }

After calling SaveChanges(), newCommentObject.questionId will contain the newly generated key by the database. — haim770
@haim770 - I updated the question with the exact error I am receiving and the location of the error. If there's some mechanism or ordering that can resolve this please post an answer. — P.Brian.Mackey
I typically do something like this: my IDENTITY in the database goes from 1 on up; my new entities in C# get "fake" PK values from -1 downwards - those will then be replaced when the actual insert into the table happens (but having separate, distinct "fake" PK values make sure you can work with the entities just fine, and even reference them from other entities!) — marc_s
What type of mapping are you using? Please post the mapping code as well as the QuestionComment class. — haim770
Also, what are you trying to achieve by creating the entity using context.TableName.Create()? it is not clear what type of entity you're actually adding, is it TableName or QuestionComment? — haim770

P.Brian.Mackey P.Brian.Mackey · Accepted Answer · 2013-05-02T16:23:03

I fixed it by:

Go to SQL and make sure to the Table has the "Identity Specification" > Is Identity > set to Yes. Then update the *.edmx file if you had to make a DB change.
Check the *.edmx > Entity properties > StoreGeneratedPattern for the identity to ensure that it is set to Identity

enter image description here

Insert new entity to context with identity primary key

2 Answers