Whilst building a simple CRUD app over some MSSQL tables I noticed an issue where a new record could not be added. After a bit of digging it appears that if the table I'm trying to add the new record to has an INDENTITY spec column (which is also the primary key in my scenario) AND there is a record present with a 0 (zero) in this column, then the insert fails.
It appears that 0 is always passed as the identity column value which causes the existingItem to be found prior to attempting the insert.
At first I thought it might be because the identity spec was INDENTITY(1,1) and that the zero value was added using IDENTITY_INSERT ON as some point in the past, but I created a new table with IDENTITY(0,1) and the same thing happens.
Using the following code to create and populate the database table
CREATE TABLE dbo.MyTestTable(
[ProductID] [int] IDENTITY(0,1) NOT NULL,
[ProductDesc] [nvarchar](75) NOT NULL,
CONSTRAINT [PK_MyTestTable] PRIMARY KEY CLUSTERED
(
[ProductID] 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
INSERT INTO dbo.MyTestTable(ProductDesc) VALUES
('First'), ('Second'), ('Third')
Give us records with ProductID of 0, 1 and 2
I then created an app in Blazor Studio (updated today) and used defaults for all settings/values.
I created CRUD pages, again using defaults.
When trying to add a new record I get the message "Cannot Save myTestTable"
In the myDatabaseService.cs file, when the following code runs
var existingItem = Context.MyTestTables
.Where(i => i.ProductID == mytesttable.ProductID)
.FirstOrDefault();
the value of mytesttable.ProductID is 0
and therefore existingItem is not null.
I'm not sure if this is an EntityFramework issue or a Blazor Studio issue but would be grateful for any advice.