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


    }

Answer 1

Thanks to bhmahler's suggestion above I finally started looking into every relationship. One of the other programmers added the MsdsDataValidation and when they mapped the relationship they configured it backward. So in the MsdsDataValidationMap he had

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

and should have had

            HasRequired(t => t.ActivityInstance)
            .WithOptional(t => t.MsdsDataValidation);

The back of his head will be smacked. Thanks for the help.

Answer 2

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

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