Entity Framework core doesn't save changes (no primary key, one row table)

Question

I have a legacy database with the table which has only one row without a primary key. When I try to update this row nothing happens. I searched a lot and came on this line of code (dbf.Entry(nextPro).State = EntityState.Modified;). When I added this code I got the error: "Unable to track an instance of type 'NxtId' because it does not have a primary key. Only entity types with primary keys may be tracked."

I cannot remove the row and add a new one. I cannot add primary key. I have the only option to update the row somehow.

var nextPro = dbf.NxtId.FirstOrDefault();

nextPro.ProductNo = 239071;
dbf.Entry(nextPro).State = EntityState.Modified;
dbf.SaveChanges();

Entity:

public partial class NxtId
{
    public int? ProductNo { get; set; }
    public int? Uid { get; set; }
    [Key]
    public int? SatCode { get; set; }
}

How it will know that there is an update if I disable ChangeTracker? I tried to turn it on instead (because it didn't save the changes) and I got the error that explains why it didn't save changes (there is no primary key). — alex, Jun 17 '20 at 14:03
If the table has an another column, you can define that as a key in EF (like adding the [Key] attribute). It will even be unique if you only have one row. — Annosz, Jun 17 '20 at 14:04
I added [Key] annotation to another column which doesn't change. It didn't help. Still getting: Unable to track an instance of type 'NxtId' because it does not have a primary key. Only entity types with primary keys may be tracked. — alex, Jun 17 '20 at 15:15
To my knowledge, you can't save or update an entity which doesn't have a primary key. Even the owned entity types, which can be defined without a key in .NET entity class, are tracked using a shadow property representing their primary key. — Dejan Janjušević, Jun 17 '20 at 16:19
This post may help you https://stackoverflow.com/questions/15381233/can-we-have-table-without-primary-key-in-entity-framework/15381324#:~:text=6%20Answers&text=No%20you%20can't%20because,an%20update%20or%20delete%20operation.&text=There%20is%20a%20great%20difference,be%20without%20a%20primary%20key. — PeonProgrammer, Jun 17 '20 at 16:25
As a side note, you say you cannot add a primary key, but there are very valid arguments for amending this DB to take some kind of a key - how would your system react if someone accidentally added a second row in here? You could save a lot of heartache here later on. — Paddy, Jun 18 '20 at 14:17

score 2 · Answer 1 · answered Jun 17 '20 at 18:35

You cannot track or change an entity with Entity Framework when it does not have a key. However, you can still read data from the database. Also, you can run custom manually written SQL queries using the extension method RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(). Use it to send a normal UPDATE query and then read the entity again from the database to get the new values.

dbf.Database.ExecuteSqlRaw($"UPDATE {nameof(NxtId)} SET {nameof(NxtId.ProductNo)} = {{0}}", 239071);
var nextPro = dbf.NxtId.Single();

score 1 · Answer 2 · answered Jun 18 '20 at 14:11

The code that works for me (Entity Framework Core doesn't like to work with raw sql). So I had to find a command that still works.

        using (SqlConnection connection = new SqlConnection(Configuration["ConnectionStrings:Test"]))
        {
            SqlCommand command = new SqlCommand("UPDATE dbo.NxtID SET ProductNo =" + newProductNo, connection);
            command.Connection.Open();
            command.ExecuteNonQuery();
        }

Entity Framework core doesn't save changes (no primary key, one row table)

2 Answers2