SQL Server Entity Framework: how to properly add entities with composite primary key?

wz1wpwve  于 2023-08-02  发布在  Perl
关注(0)|答案(1)|浏览(154)

In EF 6 with a database-first approach, is there something special that needs to be done to add an entity to its set collection when the entity definition contains a primary key made up from two columns?

I've been running into the following error:
System.Data.Entity.Infrastructure.DbUpdateConcurrencyException: 'Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.'

Here is the high level setup:

  1. DB First
  • Primary key from two columns defined on the table:

  • RecordId: int identity

  • Created: datetime

  • Both columns set to be entity keys via the EDMX

And some example code:

  1. Dim ctx = New TestEntities
  2. Dim newTestRecord = New TestTable
  3. newTestRecord.Created = Now
  4. newTestRecord.DataPoint = "this is a test"
  5. ctx.TestTables.Add(newTestRecord)
  6. ctx.SaveChanges()

The exception is thrown when we try to call ctx.SaveChanges .

I've observed and tried the following:

  1. I changed things so that RecordId is the only PK, refreshed/updated the EDMX settings and the insert works.
  2. Via step 1, I discovered that the original code is definitely interacting with the database because that identity column RecordId is incrementing even when the original code throws. You can also set Created to a value which is out of bounds for the SQL datetime and you will get an error.
  3. I changed things so that Created is the only PK, refreshed/updated the EDMX settings and the insert works.

Which gets us back to the question in point: is there something special that you need to do when adding entities that contain a composite primary key? Is this approach even a doable thing, having a composite primary key where one part is set in code and the other part is set via SQL Server's internal handling of the identity (and so EF won't even know about which value SQL assigns until it gets the response)?

Addendum/background: we're trying to use composite primary keys because we're partitioning the underlying table and need the Created field to be part of the clustered index for use by the partitioning function/scheme.

sf6xfgos

sf6xfgos1#

how can you have an identity column and another primary key? This could be your problem, EF assumes identity = unique primary key, nothing else matters. I imagine them that the code is creating a primary key on RecordID and a unique key on Created.

Setting a composite key in EF "code first" requires you to use the modelBuilder.Entity<TestTable>().HasKey(k => new {k.RecordID, k.Created} ) fluent declaration in your DBContext, so I would look at the generated code to see where it references your table and see what it is generating.

also: using a date for a partitioning system seems wierd, as the identity functions as a form of date (ie the bigger the ID, the newer it is). So if you replaced it with a sequence, then you could keep the unique incrementing ID and also add whatever date component you need into a single composite key insert.

相关问题