Insert new entity with EF Core with a manually assigned Id

Question

I need to insert an enitity into the database that has a constraint on it's id, so I want to set it manually.

Note: The model below is the minimal example to get the idea of what I'm doing. The whole thing is running on ASP.NET Core 2 with EF Core and Pomelo MySql provider.

I'm trying to create an entity in my already existing database (MySQL) that is defined like this:

CREATE TABLE Users (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(128) NOT NULL);

CREATE TABLE UserData (
    id INTEGER PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    FOREIGN KEY(id) REFERENCES Users(id));

So each record in the Users table has a record in the UserData table. Now in my code I want to use something like

Context.Users.Add(user);
Context.SaveChanges();
data.Id = user.Id;
Context.UserData.Add(data);
Context.SaveChanges();

The User should get their Id generated by the database and then their UserData created with that Id. However, the statement executed by EF results in an error:

Failed executing DbCommand (6ms) [Parameters=[@p0='?', @p1='?' (Size = 32)], CommandType='Text', CommandTimeout='30']
  INSERT INTO `UserData` (`id`, `name`)
  VALUES (@p0, @p1);

MySql.Data.MySqlClient.MySqlException: Cannot add or update a child row:
  a foreign key constraint fails (`Main`.`UserData`, CONSTRAINT `UserData_ibfk_1`
  FOREIGN KEY (`id`) REFERENCES `Users` (`id`))

Which means that whatever Id is passed to MySQL it's not the one of User.

I even tried adding a [DatabaseGenerated(DatabaseGeneratedOption.None)] to the UserData.Id as suggested in Entering keys manually with Entity Framework, but I'm thinking that is more with regard to CodeFirst approach rather than having an already running database.

From this question: EF Core Add function returns negative id I learned that Context.Add() modifies the Id, but assigning my value after Add doesn't change anything.

In case it's important, here's my DbContext Configuration

modelBuilder.Entity<UserData>(entity =>
{
    entity.Property(e => e.Id)
        .HasColumnName("id")
        .HasColumnType("int(11)")
        .ValueGeneratedNever();

    entity.Property(e => e.Name)
        .IsRequired()
        .HasColumnName("name")
        .HasMaxLength(32);

    entity.HasOne(d => d.User)
        .WithOne(p => p.UserData)
        .HasForeignKey<UserData>(d => d.Id)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("UserData_ibfk_1");
}
modelBuilder.Entity<User>(entity =>
{
    entity.Property(e => e.Id)
        .HasColumnName("id")
        .HasColumnType("int(11)")
        .ValueGeneratedNever();

    entity.Property(e => e.Email)
        .IsRequired()
        .HasColumnName("email")
        .HasMaxLength(128);
}

score 5 · Answer 1 · answered Nov 21 '17 at 01:53

The User should get their Id generated by the database

Not according to your fluent configuration:

modelBuilder.Entity<User>(entity =>
{
    entity.Property(e => e.Id)
        .HasColumnName("id")
        .HasColumnType("int(11)")
        .ValueGeneratedNever(); // <-- the problem
}

Replace ValueGeneratedNever() with ValueGeneratedOnAdd() and the problem will be solved.

As a side note, since you seem to have navigation properties in both User and UserData, you can use them instead of the FK property, thus eliminating the need of intermediate SaveChanges just the get the generated Id. For instance, instead of:

Context.Users.Add(user);
Context.SaveChanges();
data.Id = user.Id;
Context.UserData.Add(data);
Context.SaveChanges();

you could use simply:

user.UserData = data;
Context.Users.Add(user); // <-- will also add `UserData`
Context.SaveChanges();

EF will insert both records in the correct order (first User, then UserData with the Id generated from the previous insert).

Well, **I already found the solution (below)** and the value is generated by the database since it has an autoincrement on it. Whatever is in the context configuration regarding that, does not matter if I'm not generating SQL for table creation with EF, but rather using existing database. Anyway, thanks for the tip on the reversal - `user.UserData = data`. — Marian Dziubiak, Nov 21 '17 at 14:37
It doesn't matter if you generated database from EF or not. The important is to **map** correctly your database table to your class. Since you have auto-increment column in the existing database table, you need to let EF know that, so it would retrieve the `Id` and update the object **automatically** after inserting the db record. Rather than doing that **manually** as in your self answer. — Ivan Stoev, Nov 21 '17 at 14:56

score 0 · Answer 2 · answered Nov 20 '17 at 14:00

So it turns out, that after adding a new entity and doing

Context.SaveChanges()

The newly added object is not updated. After doing a new pull from the database

Context.Users.First(u => u.Email == user.Email);

The returned entity has the correct Id, which assigned to my UserData.Id has allowed to be correctly added to the table.

Insert new entity with EF Core with a manually assigned Id

2 Answers2

Linked