Cannot insert explicit value for identity column in table Entity Framework

Question

I have a form that stores a stock in my database, when I enter one stock it persists the information and when I try to add another stock I get an exception 'cannot insert explicit value for identity column in table'

In my model I have my STOCKID property declared and in my T-SQL it is set as IDENTITY(1,1).

In my model I added [DatabaseGenerated(DatabaseGeneratedOption.Identity)] but that hasn't helped

Any suggestions?

Model

public class Stock
{
    #region Day 1's

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

T-SQL

CREATE TABLE [dbo].[LowFloatStocks] 
(
    [Id]                   INT             IDENTITY (1, 1) NOT NULL,
    [Date]                 DATE            NOT NULL,
    [Ticker]               NVARCHAR (4)    NOT NULL,
    [PreviousClose]        DECIMAL (4, 2)  DEFAULT ((4.00)) NOT NULL,
    [PM_OpeningPrice]      DECIMAL (18, 2) DEFAULT ((3)) NOT NULL,
    [OpeningPrice]         DECIMAL (18, 2) NOT NULL,
    [PMFadePercent]        AS (ROUND(([OpeningPrice] - [PM_OpeningPrice]) / [PM_OpeningPrice], (4)) * (100.0)) PERSISTED NOT NULL,
    [GainPercent]          AS (ROUND(([High] - [OpeningPrice]) / [OpeningPrice], (4)) * (100.0)) PERSISTED NOT NULL,
    [GapPercent]           AS (ROUND(([OpeningPrice] - [PreviousClose]) / [PreviousClose], (4)) * (100.0)) PERSISTED NOT NULL,
    [Spike]                DECIMAL (18, 2) NOT NULL,
    [1stSpike%]            AS (ROUND(([Spike] - [OpeningPrice]) / [OpeningPrice], (4)) * (100.0)) PERSISTED NOT NULL,
    [High]                 DECIMAL (18, 2) NOT NULL,
    [HighPercent]          AS (ROUND(([High] - [PreviousClose]) / [PreviousClose], (4)) * (100.0)) PERSISTED NOT NULL,
    [Low]                  DECIMAL (18, 2) NOT NULL,
    [LowPercent]           AS (ROUND(([Low] - [PreviousClose]) / [PreviousClose], (4)) * (100.0)) PERSISTED NOT NULL,
    [Close]                DECIMAL (18, 2) DEFAULT ((4)) NOT NULL,
    [ClosePercent]         AS              (round(([Close]-[PreviousClose])/[PreviousClose],(4))*(100.0)) PERSISTED NOT NULL,
    [ClosevHigh]           AS              (round(([High]-[Close])/[Close], (4))*(100)) PERSISTED NOT NULL,
    [ClosevOpen]           AS              (round(([OpeningPrice]-[Close])/[OpeningPrice],(4))*(100.0)) PERSISTED NOT NULL,
    [CloseLessEqualToOpen] AS              (CONVERT([nchar](3),case when [Close]<=[OpeningPrice] then 'Yes' else 'No' end)) PERSISTED NOT NULL,
    [CloseRed]             AS              (CONVERT([nchar](3),case when [Close]<[OpeningPrice] then 'Yes' else 'No' end)) PERSISTED NOT NULL,
    [Catalyst]             NVARCHAR (50)   NOT NULL,
    [Float]                DECIMAL (18, 3) NOT NULL,
    [Dilution]             NCHAR (3)       NOT NULL,

    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Add Stock Method

public ICommand AddCommand => _addCommand ?? (_addCommand = new RelayCommand(param => this.AddStock()));


    #endregion 

    #region Actions

    private void AddStock()
    {
        using (var stocks = new AppDbContext())
        {
            stocks.LowFloatStocks.Add(stock);
            stocks.SaveChanges();
            Stocks = stocks.LowFloatStocks.ToList();
            Clear();


        }
    }

are you also using fluent API for configuration? Can you show the code which is inserting into DB — vivek nuna
yes, so try to set it 0 explicitly before saving. just give a try — vivek nuna

Robert Bantele Robert Bantele · Accepted Answer · 2020-08-02T11:40:30

You should use [DatabaseGenerated(DatabaseGeneratedOption.None)] if you want to create they Keys yourself. [DatabaseGenerated(DatabaseGeneratedOption.Identity)] means, that you let the Database handle the Creation of the Primary Key. This is generally the best Option, because two instances of your Software trying to create the same Primary Key will result in an Error. This is an unlikely Scenario, but still: Let the Database create the Primary Keys. As you are using Database First, you do not have the Option to change the DatabaseGeneratedOption without updating your Database. If you created a Code First Model from Database, you do have the Option to change the DatabaseGeneratedOption, create a new Migration and Update the Database. But that may be undesirable, because Entity Framework might drop the entire Table and re-create it.

Please show the Code that creates the Entity added in stocks.LowFloatStocks.Add(stock);. Did you assign a value to Id? If so, try not to do that.

Otherwise, try the Solution suggested by @viveknuna in the Comments:

stock.Id = 0; 
stocks.LowFloatStocks.Add(stock); 
stocks.SaveChanges();

Cannot insert explicit value for identity column in table Entity Framework

1 Answers