Entity Framework "Cannot insert explicit value for for identity column"

Question

I'm using .NET Core with EF and I've encountered a problem when trying to add to a database. I have a complex JSON object called Map which contains other entity types. The error arises with SourceField, which can be in RuleSourceFields and Conditions, which are themselves within Map. I've checked to make sure that my foreign and primary keys are correct, but that didn't fix it. Unless I'm mistaken, I believe that adding an entity with a virtual member shouldn't cause EF to attempt to add that subentity? Note that this error only happens on SaveChanges().

The error:

{System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'SourceFields' when IDENTITY_INSERT is set to OFF.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreResults(Boolean& moreResults)
   at System.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more)
   at System.Data.SqlClient.SqlDataReader.NextResult()
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(DbDataReader reader)
ClientConnectionId:3bc06159-276e-4a39-bbb9-7c66999e255f
Error Number:544,State:1,Class:16}

Example of JSON which I'm sending:

{  
   "description":"test",
   "effective_Date":"2016-10-23T18:59:49.3855195",
   "active":true,
   "transformations":[  
      {  
         "description":"sdfsdf",
         "rule":{  
            "rule_Value":"",
            "alt_Value":"",
            "rule_Operation":"sfield",
            "targetField":{  
               "targetFieldId":1,
               "name":"TEST",
               "datatype":"text",
               "active":true,
               "seqNum":1,
               "rules":[  

               ],
               "targetId":1,
               "target":null,
               "created_By":"Anonymous",
               "creation_Date":"2016-10-23T18:59:49.3855195",
               "date_Modified":"2016-10-23T18:59:49.3855195",
               "modified_By":"Anonymous"
            },
            "ruleSourceFields":[  

            ]
         },
         "conditions":[  
            {  
               "seqNum":1,
               "chain_Operation":"or",
               "left_Paren":"(",
               "operation":"!=",
               "cond_Value":"sdf",
               "right_Paren":")",
               "sourceField":{  
                  "sourceFieldId":28,
                  "name":"N/A",
                  "datatype":"text",
                  "active":true,
                  "seqNum":2,
                  "conditions":[  

                  ],
                  "ruleSourceFields":[  

                  ],
                  "sourceId":19,
                  "source":null,
                  "created_By":"Anonymous",
                  "creation_Date":"2016-10-12T04:51:03.3311291",
                  "date_Modified":"2016-10-12T04:51:03.3311291",
                  "modified_By":"Anonymous"
               }
            }
         ]
      }
   ]
}

Model class examples:

SourceField.cs

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int SourceFieldId { get; set; }
        public string Name { get; set; }
        public string Datatype { get; set; }
        public bool Active { get; set; }
        public int SeqNum { get; set; }

        [ForeignKey("SourceFieldId")]
        public virtual ICollection<Condition> Conditions { get; set; }
        [ForeignKey("SourceFieldId")]
        public virtual ICollection<RuleSourceField> RuleSourceFields { get; set; }

        public int SourceId { get; set; }
        public virtual Source Source { get; set; }

Condition.cs

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ConditionId { get; set; }
    public int SeqNum { get; set; }
    public string Chain_Operation { get; set; }
    public string Left_Paren { get; set; }
    public string Operation { get; set; }
    public string Cond_Value { get; set; }
    public string Right_Paren { get; set; }

    public int SourceFieldId { get; set; }
    public virtual SourceField SourceField { get; set; }

    public int TransformationId { get; set; }
    public virtual Transformation Transformation { get; set; }

Answer 1

I assume that the sourceField with sourceFieldId=28 that appears in your JSON already exists and you don't want to insert it, right? And your parent entity is not attached to the context because you just filled it from the JSON (you didn't fetch it from the database context).

It seems that the EF tracking state of your child SourceField entity is not correctly set as Unchanged or Modified, but as Added. This is the kind of problem that you get when dealing with entity graphs in disconnected scenarios. You can fix this by using GraphDiff, which goes through your children tree and makes sure the status of each children is correctly set. Or you could either implement this yourself and manually update the tracking states in your children.

More info about why this happens and how to fix it in this answer.

Also, if you are not intending to update the contents of the sourceField child, perhaps you would avoid this problem if your JSON would contain just the value of the property sourceFieldId instead of the whole navigation entity sourceField.

The purpose of using virtual navigation properties is to allow EF to use proxy entities when doing Lazy Loading. As far as I know they don't have any effect on allowing child entities to be added or modified.