Cannot insert explicit value for identity column in table 'ActivityInstances' when IDENTITY_INSERT is set to OFF

Question

I am trying to insert a new ActivityInstance, I am not setting the ID, but entity framework is attempting to insert an ID anyway.

The model creation and save command:

        var ai = new ActivityInstance
        {
            ActivityId = 1,
            LocationId = 1,
            BeginDate = DateTime.Now,
            ExpirationDate = DateTime.Now
        };
        DbContext.ActivityInstances.Add(activityInstance);
        DbContext.SaveChanges();

The Model

public class ActivityInstance : IBusinessObject, IEntity
{
    public int Id { get; set; }
    public int LocationId { get; set; }
    public string TargetId { get; set; }
    public int ActivityId { get; set; }
    public DateTime? BeginDate { get; set; }
    public DateTime? ExpirationDate { get; set; }
    public Guid? DurableInstanceWorkflowId { get; set; }
    public int? TriggeredById { get; set; }
    public virtual Activity Activity { get; set; }
    public virtual Location Location { get; set; }
    public virtual MsdsDataValidation MsdsDataValidation { get; set; }
    public virtual ICollection<ResultsTransmittalQuestionAnswer> ActivityQuestionAnswers { get; set; }
    public virtual ICollection<ResultsTransmittalContact> ResultsTransmittalContacts { get; set; }
    public virtual Target Target { get; set; }
    public virtual CorrectiveActionPlan CorrectiveActionPlans { get; set; }
    public virtual FmVisit FmVisit { get; set; }
    public virtual AcknowledgeReport AcknowledgeReport { get; set; }
    public virtual SlCap SlCap { get; set; }
    public virtual FmVisitStudentRecordReview FmVisitStudentRecordReview { get; set; }
    public virtual ICollection<ActivityInstanceReminder> ActivityInstanceReminders { get; set; } 
   // public virtual InstancesTable Instances { get; set; }
}

The Mapping

    public ActivityInstanceMap()
    {
        ToTable("ActivityInstances");
        HasKey(x => x.Id);

        Property(t => t.Id);
        Property(t => t.LocationId);
        Property(t => t.TargetId);
        Property(t => t.ActivityId);
        Property(t => t.BeginDate);
        Property(t => t.ExpirationDate);
        Property(t => t.DurableInstanceWorkflowId);
        Property(t => t.TriggeredById);

        HasOptional(x => x.Target)
            .WithMany(x => x.ActivityInstances)
            .HasForeignKey(x => x.TargetId);

        HasRequired(t => t.Activity)
            .WithMany(t => t.ActivityInstances)
            .HasForeignKey(t => t.ActivityId);

        HasRequired(t => t.Location)
            .WithMany(t => t.ActivityInstances)
            .HasForeignKey(t => t.LocationId);

        HasOptional(t => t.AcknowledgeReport)
            .WithRequired(t => t.ActivityInstance);

    }

The table design

    CREATE TABLE [dbo].[ActivityInstances]
(
    [Id] INT IDENTITY(1,1) NOT NULL, 
    [DurableInstanceWorkflowId] UNIQUEIDENTIFIER NULL,
    [TargetId] NVARCHAR(128) NULL,
    [LocationId] INT NOT NULL, 
    [ActivityId] INT NOT NULL, 
    [BeginDate] DATETIME NULL,
    [ExpirationDate] DATETIME NULL,
    [TriggeredById] INT NULL,
    CONSTRAINT [Pk_ActivityInstances_Id] PRIMARY KEY CLUSTERED ([Id] Asc), 
    CONSTRAINT [Fk_ActivityInstances_ToLocations] FOREIGN KEY ([LocationId]) REFERENCES dbo.Locations([Id]), 
    CONSTRAINT [Fk_ActivityInstances_ToActivities] FOREIGN KEY ([ActivityId]) REFERENCES dbo.Activities([Id]),
    CONSTRAINT [FkInstanceTriggeredInstance] FOREIGN KEY ([TriggeredById]) REFERENCES dbo.ActivityInstances([Id]),
    CONSTRAINT [Fk_ActivityInstances_ToDurableWorkflowInstance] FOREIGN KEY ([DurableInstanceWorkflowId]) REFERENCES [System.Activities.DurableInstancing].InstancesTable([Id])
)

And this is what I am getting in my Output window. The Id value is the default integer 0.

 INSERT [dbo].[ActivityInstances]([Id], [LocationId], [TargetId], [ActivityId], [BeginDate], [ExpirationDate], [DurableInstanceWorkflowId], [TriggeredById])
    VALUES (@0, @1, NULL, @2, @3, @4, NULL, NULL)

UPDATE I modified the ActivityInstance model with the first suggested answer which has led me toward the actual issue, though I still have not solved it. That recommendation was to modify the ActivityInstance model setting the below on id

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

Now I am getting the error:

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'Id'.

I am looking at the optional one to one relationship currently. Acknowledge report table is:

CREATE TABLE [dbo].[AcknowledgeReports] ( [ActivityInstanceId] INT NOT NULL, CONSTRAINT [PK_AcknowledgeReportsId] PRIMARY KEY CLUSTERED ([ActivityInstanceId] ASC) )

AcknowledgeReport model

public class AcknowledgeReport : IActivityInstance
{
    public int ActivityInstanceId { get; set; }

    public virtual List<Report> Reports { get; set; }
    public virtual ActivityInstance ActivityInstance { get; set; }

}

And AcknowledgeReport mapping

    public AcknowledgeReportMap()
    {
        ToTable("AcknowledgeReports");
        HasKey(t => t.ActivityInstanceId);

        Property(t => t.ActivityInstanceId);


        // Relationships


    }

I am uncertain which duplicate choose from the related column on the right... Did you search anything about this error before? — Steve
Yes, I have been through EVERY similar post and eliminated all the possible solutions. This is not a duplicate. — Tom

bhmahler bhmahler · Accepted Answer · 2016-02-08T16:17:29

Try adding the attributes to let it know that it is database generated

[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

Cannot insert explicit value for identity column in table 'ActivityInstances' when IDENTITY_INSERT is set to OFF

2 Answers