主键问题:向SQLite表添加记录时发生了错误。

huangapple go评论71阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年7月11日 08:03:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/76657986.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定