FK properties with default values aren't always marked as store-generated

[ruirodrigues1971]

Steps to reproduce

Only needs to see for IdUserState

SQL table

CREATE TABLE `user` (
  `idUser` int NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `surname` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  ...
   `idUserState` int NOT NULL DEFAULT '1',
..
   PRIMARY KEY (`idUser`),
  UNIQUE KEY `email_UNIQUE` (`email`),
  **KEY `fk_user_state_idx` (`idUserState`),**
  CONSTRAINT `fk_user_state` FOREIGN KEY (`idUserState`) REFERENCES `accesstate` (`idaccesstate`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Generated command for all database dotnet ef dbcontext scaffold "SERVER=localhost; DATABASE=zaccsemel; user ID=XXX; PASSWORD=XXXX; port=3306" "Pomelo.EntityFrameworkCore.MySql" --startup-project ../WebServerAPI -o Models -d -f

The generate results appear to be fine

in migration idUserState = table.Column<int>(type: "int", nullable: false, defaultValueSql: "'1'"),

in context.cs

modelBuilder.Entity<User>(entity =>
{
    ...
    entity.Property(e => e.IdUserState).HasDefaultValueSql("'1'");
    ...
});

The model

[Table("user")]
[Index(nameof(Email), Name = "email_UNIQUE", IsUnique = true)]
**[Index(nameof(IdUserState), Name = "fk_user_state_idx")]**
public partial class User
{
    public User()
    {
        Licenseplates = new HashSet<Licenseplate>();
        Loghistoricallicenseplates = new HashSet<Loghistoricallicenseplate>();
        Loguseractions = new HashSet<Loguseraction>();
        Loguserposts = new HashSet<Loguserpost>();
        Usercompanies = new HashSet<Usercompany>();
        Usergroups = new HashSet<Usergroup>();
        Userpoweraccesses = new HashSet<Userpoweraccess>();
        Userzones = new HashSet<Userzone>();
    }

    [Key]
    [Column("idUser")]
    public int IdUser { get; set; }
    [Required]
    [Column("name", TypeName = "varchar(100)")]
    public string Name { get; set; }
    [Required]
    [Column("surname", TypeName = "varchar(100)")]
    public string Surname { get; set; }
...
    [Column("idUserState")]
    public int IdUserState { get; set; }
  ...
    [ForeignKey(nameof(IdUserState))]
    [InverseProperty(nameof(Accesstate.Users))]
    public virtual Accesstate IdUserStateNavigation { get; set; }
    [InverseProperty(nameof(Licenseplate.IdUserNavigation))]
    public virtual ICollection<Licenseplate> Licenseplates { get; set; }
    [InverseProperty(nameof(Loghistoricallicenseplate.IduserNavigation))]
    public virtual ICollection<Loghistoricallicenseplate> Loghistoricallicenseplates { get; set; }
    [InverseProperty(nameof(Loguseraction.IduserNavigation))]
    public virtual ICollection<Loguseraction> Loguseractions { get; set; }
    [InverseProperty(nameof(Loguserpost.IduserNavigation))]
    public virtual ICollection<Loguserpost> Loguserposts { get; set; }
    [InverseProperty(nameof(Usercompany.IduserNavigation))]
    public virtual ICollection<Usercompany> Usercompanies { get; set; }
    [InverseProperty(nameof(Usergroup.IduserNavigation))]
    public virtual ICollection<Usergroup> Usergroups { get; set; }
    [InverseProperty(nameof(Userpoweraccess.IduserNavigation))]
    public virtual ICollection<Userpoweraccess> Userpoweraccesses { get; set; }
    [InverseProperty(nameof(Userzone.IduserNavigation))]
    public virtual ICollection<Userzone> Userzones { get; set; }
}

The issue

When I create a user. When Testing the default value for IdUserState is not put in 1

user = new User(); or user = new User() { IdUser = manageUser.GetUniqueID(), Name = "Rui", Surname = "Rodrigues" };

I'm doing something wrong?

of course when I do dbContext.Add<User>(user); res = dbContext.SaveChanges();

I will get a error because the IdUserState value is 0 and not exist in the other table

Exception message: (But is expected, because the value 0 is wrong)

MySqlConnector.MySqlException: Cannot add or update a child row: a foreign key constraint fails (`zaccsemel`.`user`, CONSTRAINT `fk_user_state` FOREIGN KEY (`idUserState`) REFERENCES `accesstate` (`idaccesstate`) ON DELETE RESTRICT ON UPDATE CASCADE) ---> MySqlConnector.MySqlException: Cannot add or update a child row: a foreign key constraint fails (`zaccsemel`.`user`, CONSTRAINT `fk_user_state` FOREIGN KEY (`idUserState`) REFERENCES `accesstate` (`idaccesstate`) ON DELETE RESTRICT ON UPDATE CASCADE)

Further technical details

MySQL version: 8.0.22 Operating system: windows 10 Pro Pomelo.EntityFrameworkCore.MySql version: 5.0.0-alpha.2 Microsoft.AspNetCore.App version: .NET 5.0 (run application), the model/context in .NET standard 2.1

Other details about my project setup:

Forcing the value of IdUserState for a valid value (for example the default value) works fine.

[lauxjpn]

This one is a very weird issue. I reproduced the behavior not just with MySQL, but also with SQL Server. It seems that this issue is part of EF Core.

The sample code I used for reproduction:

Program.cs

using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;

namespace IssueConsoleTemplate
{
    public class User
    {
        public int UserId { get; set; }
        public int IdUserState { get; set; } // <-- fails with `IdUserState`, but succeeds with `UserStateId`

        public virtual AccessState UserState  { get; set; }
    }

    public class AccessState
    {
        public int AccessStateId { get; set; }

        public virtual ICollection<User> Users { get; set; } = new HashSet<User>();
    }

    public class Context : DbContext
    {
        public virtual DbSet<AccessState> AccessStates { get; set; }
        public virtual DbSet<User> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1304_01";
            
            optionsBuilder.UseMySql(
                    connectionString,
                    ServerVersion.AutoDetect(connectionString),
                    options => options.CharSetBehavior(CharSetBehavior.NeverAppend))
            // optionsBuilder.UseSqlServer(@"Data Source=FASTJACK\MSSQL14;Integrated Security=SSPI;Initial Catalog=Issue1304_01")
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        configure => configure
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<AccessState>(
                entity =>
                {
                    entity.Property(e => e.AccessStateId)
                        .ValueGeneratedNever();

                    entity.HasData(
                        new AccessState {AccessStateId = 1});
                });

            modelBuilder.Entity<User>(
                entity =>
                {
                    // Fails with `IdUserState`, but works with `UserStateId`.
                    // Also works with `IdUserState`, if moved below the relationship definition.
                    entity.Property(e => e.IdUserState)
                        .HasDefaultValue(1);
                    
                    entity.HasOne(d => d.UserState)
                        .WithMany(p => p.Users)
                        .HasForeignKey(d => d.IdUserState);
                    
                    // <-- Works if moved to here, even if named `IdUserState`.
                });
        }
    }

    internal static class Program
    {
        private static void Main(string[] args)
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Users.Add(new User());

            context.SaveChanges();
        }
    }
}

Console output (SQL)

warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 5.0.1 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 8.0.21-mysql SensitiveDataLoggingEnabled DetailedErrorsEnabled

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (82ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DROP DATABASE `Issue1304_01`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE `Issue1304_01`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (57ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `AccessStates` (
          `AccessStateId` int NOT NULL,
          CONSTRAINT `PK_AccessStates` PRIMARY KEY (`AccessStateId`)
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (90ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Users` (
          `UserId` int NOT NULL AUTO_INCREMENT,
          `IdUserState` int NOT NULL DEFAULT 1,
          CONSTRAINT `PK_Users` PRIMARY KEY (`UserId`),
          CONSTRAINT `FK_Users_AccessStates_IdUserState` FOREIGN KEY (`IdUserState`) REFERENCES `AccessStates` (`AccessStateId`) ON DELETE CASCADE
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO `AccessStates` (`AccessStateId`)
      VALUES (1);

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (53ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX `IX_Users_IdUserState` ON `Users` (`IdUserState`);

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (225ms) [Parameters=[@p0='0'], CommandType='Text', CommandTimeout='30']
      INSERT INTO `Users` (`IdUserState`)
      VALUES (@p0);
      SELECT `UserId`
      FROM `Users`
      WHERE ROW_COUNT() = 1 AND `UserId` = LAST_INSERT_ID();

As you can see, the table declarations in SQL are fine. However, the last INSERT statement is unexpected:

INSERT INTO `Users` (`IdUserState`)
VALUES (@p0);

Because IdUserState has been configured with the HasDefaultValue(1) and we have not explicitly set a value in our context.Users.Add(new User()) statement, it should not be part of the INSERT query.

It gets weirder:

If I rename the IdUserState property to UserStateId, the same DDL statements (with the changed column name of course) get generated as before, including the expected PKs etc., but the generated INSERT statement suddenly correctly omits the UserStateId column and so the INSERT query works as expected.

If I don't rename IdUserState, I can still get the INSERT statement to work, if I move the property configuration below the relationship configuration:

entity.HasOne(d => d.UserState)
    .WithMany(p => p.Users)
    .HasForeignKey(d => d.IdUserState);

// Works, because the configuration happens after the relationship configuration:
entity.Property(e => e.IdUserState)
    .HasDefaultValue(1);

@roji I think the EF Core team should look at this.

[ajcvickers]

/cc @AndriySvyryd

[AndriySvyryd]

Another workaround is to call entity.Property(e => e.IdUserState).ValueGeneratedOnAdd()

Filed https://github.com/dotnet/efcore/issues/23974