How to insert primary keyless entity to table?

Question

I'm working on with 15 years old DB.Now our scenario is DB first ,inserting an entity to primary keyless table.I read a lots of microsoft EF Core docs such as https://docs.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=data-annotations. And they said not supported as a conclusion .

Firstly,I added HasNokey() as a usual then it gave an exception. After that, I added dummy primary key in entity class then HasKey(x=>x.DummyId) Ignore(x=>x.DummyId) it.But it didn't work again.Now I'm confused.

How to handle this problem guys? Thank you for any advice.

Answer 1

EF Core does not directly support updating, deleting, and inserting into keyless tables with a DbSet as of version 5.0.

There are two solutions I am aware of

Raw SQL Query

In EF Core, it is possible to execute raw SQL Commands. You could create a SQL command to perform the insert and execute it manually. Microsoft documentation (https://docs.microsoft.com/en-us/ef/core/querying/raw-sql) does not cover your exact use case, but you can use raw or interpolated SQL Directly on the Database member of a DbContext.

    var ctx = new DbContext(options);
    ctx.Database.ExecuteSqlRaw("INSERT INTO Table_Name .....");

Change Database Schema You could add an primary key column to the schema such that EF core would be able to use it as intended for updates, inserts, and deletes. Broadly speaking, the change would follow the steps as below.

1. Create a new table identical to the old table
2. Add an auto-incremented, primary key column
3. Copy the data from old table to new table
4. Delete old table
5. Rename new table to name of old table

Once complete, you would need to change your entity definitions as shown below.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ConfigTemplate>(entity =>
    {
         // Adding the key itself, using the index name as well
         entity.HasKey(e => e.primary_key)
               .HasName("PRIMARY");

         // Adding the index definition is not a required
         entity.HasIndex(e => e.primary_key)
               .HasName("PRIMARY");

         entity.ToTable("config_template");
    }
}

Answer 2

if your keyless table got some other columns which could be used as composite keys, you can try to update config for that entity in dbcontext as a workaround, but I agree with that modify exist table with a new primary key is a better solution

 modelBuilder.Entity<SampleEntity>(entity =>
 {
    entity.HasKey(e => new { e.Property1, e.Property2  });
 });