CODEWITHSUNDEEP
sql-serverasp.net-coreentity-framework-coreasp.net-core-webapi
Fabricio Rodriguez
Fabricio Rodriguez

Reputation: 4239

Error when inserting record to SQL Server database from ASP.NET Core 2.1 Web API using EF Core 2.1

I've created a new ASP.NET Core 2.1 Web API. It uses EF Core, code first, to read and write to a SQL Server database. So I've used migrations to generate/scaffolding the database.

On the [HTTPPOST] action method in the controller, when it adds a new record to the DbContext and tries to save, I get the following error:

System.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'Readings' when IDENTITY_INSERT is set to OFF.

The database has only one table:

USE [eballcoz_mssql]
GO

/****** Object:  Table [eballcoz_admin].[Readings]    Script Date: 2018/11/08 21:13:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [eballcoz_admin].[Readings](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [BaseId] [int] NULL,
    [Frequency] [int] NULL,
    [Modulation] [int] NULL,
    [Agc1] [int] NULL,
    [Agc2] [int] NULL,
    [TimeStamp] [datetime2](7) NULL,
 CONSTRAINT [PK_Readings] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

My model looks like this:

public class Reading
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int? BaseId { get; set; }
    public int? Frequency { get; set; }
    public int? Modulation { get; set; }
    public int? Agc1 { get; set; }
    public int? Agc2 { get; set; }
    public DateTime? TimeStamp { get; set; }
}

And my action method like this:

    // POST: api/Readings/one
    [HttpPost]
    public async Task<IActionResult> PostReading([FromBody] Reading reading)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        _context.Readings.Add(reading);
        await _context.SaveChangesAsync();

        return CreatedAtAction("GetReading", new { id = reading.Id }, reading);
    }

I understand what the problem is - my model inclues the "Id" primary key field, and so it's trying to write that to the database table, which SQL Server doesn't like. The problem is that I need the "Id" field in the model for when I read from the database. I would have thought that the [DatabaseGenerated(DatabaseGeneratedOption.Identity)] decorator in the model would tell EF that it should not try to insert the Id column, but it doesn't seem to be working. I've also tried to do this in FLUENT:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Reading>()
        .HasKey(r => r.Id);

        modelBuilder.Entity<Reading>()
        .Property(r => r.Id)
        .UseSqlServerIdentityColumn()
        .ValueGeneratedOnAdd();
    }

to no avail. How can I keep the Id column as part of my model, but tell EF not to include it in the INSERT query? I have read this:

There are basically 2 different ways to INSERT records without having an error:

  1. When the IDENTITY_INSERT is set OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENT
  2. When the IDENTITY_INSERT is set ON. The PRIMARY KEY "ID" MUST BE PRESENT

So I'm basically trying to go for solution #1 - I don't want to supply a primary key value. I want SQL Server to automatically generate it for me. But my model does indeed include the Id column, because I need it when reading from the database... Any ideas?

Upvotes: 0

Views: 1774

Answers (2)

Yepeekai
Yepeekai

Reputation: 2723

You should check at reading.Id. It must be 0 when you add an object to your dbcontext. You can edit it to force: reading.Id = 0;

Upvotes: 1

Adam Vincent
Adam Vincent

Reputation: 3811

Entity Framework already handles the problem you're looking at. I believe your problem lies elsewhere.

Default behavior of EF

When you new up a object with a property of int, it's default value is 0. It's not a nullable type, so it can't be null. When that property (the Id in this case) is tagged as the auto-incremented primary key, EF Core leaves it as 0 in your DbContext until SaveChanges() is called, and EF Core then populates the Id property with whatever value SQL Server has generated for it.

var reading = new Reading(); //Id = 0
_context.Add(reading); //Id still 0
_context.SaveChanges(); //Id = 5 (or whatever Id from SQL
System.Console.Writeline($"Id: {reading.Id}" //output -> Id: 5

The real problem?

When you receive your Reading object from the client (whatever is posting to PostReading action) I'm going to take a guess that the Id field has already been populated at this point, causing your error.

[HttpPost]
public async Task<IActionResult> PostReading([FromBody] Reading reading)
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }

    //quick debug test
    if (reading.Id > 0) 
    {
       throw new ArgumentException("Something erroneously filled out the Id.");
    }

    _context.Readings.Add(reading);
    await _context.SaveChangesAsync();

    return CreatedAtAction("GetReading", new { id = reading.Id }, reading);
}

Upvotes: 1

Related Questions