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

Question

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:

When the IDENTITY_INSERT is set OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENT
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?

Yepeekai · Accepted Answer · 2018-11-08 22:00:16Z

1

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;

answered Nov 8, 2018 at 22:00

Yepeekai

2,79130 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Vincent Over a year ago

This answer at face value does not fix an underlying problem, it just sweeps it under the rug. I would strongly advise against it.

Yepeekai Over a year ago

There is an underlying problem, the Id should be 0 by default, so there is another input (or something else) sending the Id of something else and the model binder take that Id when calling PostReading and put it in the reading object. So the OP should ensure that there is no input with name="id" in the form since he is trying to add a new object. But the question is mainly about EF side of things and I was answering that.

Adam Vincent · Accepted Answer · 2018-11-08 20:43:03Z

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);
}

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Default behavior of EF

The real problem?

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Default behavior of EF

The real problem?

Comments

Your Answer

Sign up or log in

Post as a guest

Related