CODEWITHSUNDEEP
c#sql-serverentity-frameworkasp.net-web-api2
Digital-Clouds
Digital-Clouds

Reputation: 552

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF when not trying to set a value for an IDENTITY column

I have a WebApi2/Entity Framework project using Code First that is throwing this error:

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

This is the model:

public class Session
{
    [Key, ForeignKey("Device")]
    public long Id { get; set; }
    public virtual Device Device { get; set; }
}
public class Device
{
    [Key]
    public long Id { get; set; }
    public long AnalyticsId { get; set; }
    [ForeignKey("AnalyticsId")]
    public Session AnalyticsSession { get; set; }
}

The controller:

    public async Task<IHttpActionResult> PostSession(SessionInitialDTO sessionDTO)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        Session session = new Session();

        db.Sessions.Add(session);

        Analytics.Models.Device device = sessionDTO.Device;
        device.AnalyticsSession = session;

        db.Devices.Add(device);

        await db.SaveChangesAsync();

        return CreatedAtRoute("CreateInitialSession", new { id = session.Id }, session);
    }

Everything I've read says that this issue comes from trying to set an IDENTITY column to a value, but as far as I can tell, I'm not trying to do that (or at least if I am, am not seeing where). The Device object in the DTO does not set the Id. I've seen some things suggesting using the [DatabaseGenerated(DatabaseGeneratedOption.Identity)] annotation to specify that the field is autogenerated, but this then causes issues because of the ForeignKey

This is the code for the generated tables:

CREATE TABLE [dbo].[Sessions] (
  [Id]           BIGINT         IDENTITY (1, 1) NOT NULL,
  CONSTRAINT [PK_dbo.Sessions] PRIMARY KEY CLUSTERED ([Id] ASC),
  CONSTRAINT [FK_dbo.Sessions_dbo.Devices_Id] FOREIGN KEY ([Id]) REFERENCES [dbo].[Devices] ([Id])
);

CREATE TABLE [dbo].[Devices] (
  [Id]                  BIGINT         IDENTITY (1, 1) NOT NULL,
  [AnalyticsId]         BIGINT         NOT NULL,
  CONSTRAINT [PK_dbo.Devices] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Bizarrely, this used to work, I made some changes to the model, started getting this, reverted to this version of the model, and the error persisted.

Upvotes: 1

Views: 1058

Answers (2)

bubi
bubi

Reputation: 6501

Without trying to understand if the model meets the requirements (a device can have one or zero sessions), you are trying to build a 1-1 relationship so Session.Id cannot be autogenerated (with EF by default integer Keys are autogenerated).
To specify it you can just add attribute [DatabaseGenerated(DatabaseGeneratedOption.None)] to Session.Id property.
After that you need to migrate the database because Session.Id will not be an IDENTITY.

Upvotes: 1

Vitaliy Smolyakov
Vitaliy Smolyakov

Reputation: 472

The problem is that Session.Id is identity column. Device record created first and try set Session foreign key - Id.

You must disable identity: 

public class Session
{
    [Key, ForeignKey("Device")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public long Id { get; set; }
    public virtual Device Device { get; set; }
}

Upvotes: 1

Related Questions