Insert new entity with EF Core with a manually assigned Id

asp.net-core-2.0 c# entity-framework-core mysql

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);
}
1
1
11/20/2017 12:14:37 PM

Popular Answer

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).

4
11/21/2017 1:53:59 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow