Problem with primary key adding a record to a SQLite table

Question

I'm using

<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.8" />

I create a table in sqlitestudio:

CREATE TABLE Product 
(
    name      TEXT,
    price     NUMERIC,
    quantity  NUMERIC,
    productId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

I scaffold the db into code

public string? Name { get; set; }
public byte[]? Price { get; set; }
public byte[]? Quantity { get; set; }
public long ProductId { get; set; }

(I seem to have to amend my Price and Quantity attributes to decimal etc, but it still seems to 'work', so we get)

public string? Name { get; set; }
public decimal? Price { get; set; }
public decimal? Quantity { get; set; }
public long ProductId { get; set; }

I then have some code that adds a new record

var newProduct = new Product
        {
            Name = "New",
            Price = 0,
            Quantity = 0
        };
await context!.Products.AddAsync(newProduct);

I can't set the primary key, because it's autogenerated by the database (I've been through this before with ADO.NET and earlier versions of EF with SQL Server).

But I get an error:

System.InvalidOperationException: The instance of entity type 'Product' cannot be tracked because another instance with the same key value for {'ProductId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached

And yeah, I completely get that, I've added a record with a primary key of 0 and there already is one.

So how am I supposed to add data? (I've done this sooo many times before with SQL Server, but somehow this was never a problem, I'm missing something - that I can't find).

My only workaround at the moment is to generate guids for primary keys, which I always find a bit ugly.

https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations#explicitly-configuring-value-generation — Jeremy Lakeman, Jul 11 '23 at 01:08
Update next primary key in sqlite_sequence. See https://stackoverflow.com/questions/1601697/sqlite-reset-primary-key-field — Tarik, Jul 11 '23 at 02:24
Does this answer your question? [SQLite Reset Primary Key Field](https://stackoverflow.com/questions/1601697/sqlite-reset-primary-key-field) — Tarik, Jul 11 '23 at 02:25

score 1 · Answer 1 · answered Jul 11 '23 at 02:13

1

It seems that the auto gen for record id is not incrementing the new record instance. perhaps check that setting.

or perhaps try adding the record and include id in the code with the next id value, see if that works.

answered Jul 11 '23 at 02:13

IanJ

11
3

score 0 · Answer 2 · answered Jul 11 '23 at 18:57

To be honest I gave up on this and simply did a code first implementation, that seemed to work out of the box.

For some reason the database first approach creates logs of code that describes the database in OnModelCreating and the code first approach doesnt do that, and yet it seems to work by magic.

I PREFER database first, for me they are 2 distinct things, I wonder if there is a bug in the database first approach (its always worked for me with sql server).

I'll persevere code first and may try again one day.

Problem with primary key adding a record to a SQLite table

2 Answers2