EF Core: how to handle an entity without a database-generated key?

Question

In Entity Framework Core 7, I have the following entity:

 [Index(nameof(Time))] 
 [Table("DataEntries")]
 public class DataEntry
 {
 [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
 public DateTime Time { get; set; } = DateTime.Now;
 public byte StatusCode { get; set; }
 }

I can successfully insert new entries, but updating or deleting an entry always results in the following error:

DbUpdateConcurrencyException when calling SaveChanges().
The database operation was expected to affect 1 row(s), but actually affected 0 row(s);

Here's a minimal example that produces the exception every time:

var result = ctx.DataEntries
 .Where(t => t.Time >= start && t.Time <= end)
 .OrderBy(t => t.Time).First();
result.StatusCode = 1;
ctx.SaveChanges();

Upon further investigation, I strongly suspect that it has to do with my entity having a manually set key (and thus no identity column in the database).

So my question is simple: is what I am trying to do even possible in EF Core? If yes, then how?

If no, I guess I'd have to add an extra Id column. The problem here is that the database already contains many thousand rows, so I don't know if a simple database migration will work in this case.

EDIT: fun fact, this statement works:

 var result = ctx.DataEntries.First();
 result.StatusCode = 1;
 ctx.SaveChanges();

EDIT2: I also tried adding a separate "Id" column of type int. I populated the column using random ints (just for testing). I then declared a [PrimaryKey] on the entity class, using both "Time" and "Id" as identity columns. Still didn't work though.

• Cleo: In this case, it's guaranteed that it is unique. The design here is not in question. The question is: does EF core support what I'm trying to here. I don't think EF makes a difference between "good" and "bad" keys. Either it is a column with unique values or it isn't.
• Svyatoslav Danyliv: Why not use ExecuteUpdate and ExecuteDelete?
• Alexander Petrov: Which DBMS exactly are you using? I had to deal with a problem when the DateTime did not match in the code and in the database due to ticks.
• Cleo: I am using EF core 7 with MS SQL Server 2022. HOWEVER, the database was originally an MySQL/MariaDB database. I used a migration tool to convert it into MS SQL. Works fine, except for the above problem.
• Anton Kovachev: DateTime type is a bad option for a primary key for many reasons for example you don't know if it is a full date time with a time or just a date with zeros for the time values which will lead to none unique primary keys. If you can just add a new Primary Key with type int or guid. The database migration generated by EF core most likely will set the value for the existing rows automatically. If not you can do that with fluent api setting the default Id column valu to Guid.NewGuid(). More info on this here magnussundstrom.se/blog/add-guid-ef-code-first

Answer 1

Thanks to the comments, I was able to pinpoint the problem: the database was originally a MySQL/MariaDB. It was converted into an MS SQL database using the tool "ESF Database Migration Toolkit - Pro". During the conversion, the tool used the type "datetime" for DateTime columns, which would work fine for the classic Entity Framework 6. However, EF Core uses the type "datetime2". And that's where the problems came from.

I learned a lot through that.