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

[AndriySvyryd]

Originally filed by @ruirodrigues1971 at PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#1304

Smaller repro from @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);

[AndriySvyryd]

The issue is that at

efcore/src/EFCore/Metadata/Conventions/ValueGenerationConvention.cs

Line 55 in f54b9dc

property.Builder.ValueGenerated(ValueGenerated.Never);

and

efcore/src/EFCore/Metadata/Conventions/ValueGenerationConvention.cs

Line 97 in f54b9dc

property.Builder.ValueGenerated(ValueGenerated.Never);

the value is hardcoded instead of calling GetValueGenerated(property)