英文:
Problem with primary key adding a record to a SQLite table
问题
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 database 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.
英文:
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.
答案1
得分: 1
似乎自动生成记录ID的部分没有增加新的记录实例。也许要检查一下设置。
或者尝试添加记录并在代码中包括ID,使用下一个ID值,看看是否有效。
英文:
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.
答案2
得分: 0
坦白说,我放弃了这个,只是做了一个先写代码的实现,似乎能够直接使用。
由于某种原因,数据库优先方法在 OnModelCreating 中创建了描述数据库的大量代码,而代码优先方法却没有这样做,但却似乎能够奇迹般地工作。
我更喜欢数据库优先,对我来说它们是两种不同的东西,我想知道是否存在数据库优先方法的错误(它在我使用 SQL Server 时一直有效)。
我会坚持使用代码优先,也许将来会再次尝试。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论