.NET 9+EF+MariaDB迁移报错：无法将DBNull转为Int64

我使用.NET 9、EF Core、MySql.EntityFrameworkCore，通过Code First迁移搭配本地XAMPP的MariaDB搭建极简API。执行以下命令时：

dotnet ef database update --verbose --project Boliche.Data  --startup-project Boliche.Api

触发错误：Unable to cast object of type 'System.DBNull' to type 'System.Int64'。

应用结构 #

应用结构截图

Client模型代码 #

using System.ComponentModel.DataAnnotations.Schema;

namespace Boliche.Data.Models;

[Table("PRO_Clients")]
public class Client
{
  public int Id { get; set; }
  public string Name { get; set; }
  public DateTime Record { get; set; }
}

DbContext代码 #

using Microsoft.EntityFrameworkCore;

namespace Boliche.Data.Models;

public partial class BolicheContext : DbContext
{
  // Connection string is static for testing purposes only.
  private readonly string _connectionString = "server=localhost;database=boliche_bd;user id=root;password=XXXXXXXXX";
  
  public BolicheContext() { }
  
  public BolicheContext(DbContextOptions<BolicheContext> options)
    : base(options) { }
  
  public virtual DbSet<Client> Clients { get; set; }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseMySQL(_connectionString);

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Client>(entity =>
    {
      entity.HasKey(e => e.Id).HasName("PRIMARY");

      entity.ToTable("prod_clients");

      entity.Property(e => e.Id)
        .HasColumnType("int(11)")
        .HasColumnName("id");
      entity.Property(e => e.Name)
        .HasColumnType("varchar")
        .HasMaxLength(50)
        .HasColumnName("name");
      entity.Property(e => e.Record)
        .HasDefaultValueSql("'current_timestamp()'")
        .HasColumnType("date")
        .HasColumnName("record");
    });

    OnModelCreatingPartial(modelBuilder);
  }

  partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

完整报错日志 #

Using project 'C:\3.des\CSharp\Boliche\Boliche.Data\Boliche.Data.csproj'.
Using startup project 'C:\3.des\CSharp\Boliche\Boliche.Api\Boliche.Api.csproj'.
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\mumo\AppData\Local\Temp\tmp2fa2ry.tmp /verbosity:quiet /nologo C:\3.des\CSharp\Boliche\Boliche.Data\Boliche.Data.csproj
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\mumo\AppData\Local\Temp\tmpdmeek0.tmp /verbosity:quiet /nologo C:\3.des\CSharp\Boliche\Boliche.Api\Boliche.Api.csproj
Build started...
dotnet build C:\3.des\CSharp\Boliche\Boliche.Api\Boliche.Api.csproj /verbosity:quiet /nologo /p:PublishAot=false

Compilación correcta.
    0 Advertencia(s)
    0 Errores

Tiempo transcurrido 00:00:01.27

Hay actualizaciones de carga de trabajo disponibles. Ejecute "dotnet workload list" para obtener más información.

Build succeeded.
dotnet exec --depsfile C:\3.des\CSharp\Boliche\Boliche.Api\bin\Debug\net9.0\Boliche.Api.deps.json --additionalprobingpath C:\Users\mumo\.nuget\packages --additionalprobingpath "C:\Program Files (x86)\Microsoft Visual Studio\Shared\NuGetPackages" --runtimeconfig C:\3.des\CSharp\Boliche\Boliche.Api\bin\Debug\net9.0\Boliche.Api.runtimeconfig.json C:\Users\mumo\.dotnet\tools\.store\dotnet-ef\9.0.5\dotnet-ef\9.0.5\tools\net8.0\any\tools\netcoreapp2.0\any\ef.dll database update --assembly C:\3.des\CSharp\Boliche\Boliche.Api\bin\Debug\net9.0\Boliche.Data.dll --project C:\3.des\CSharp\Boliche\Boliche.Data\Boliche.Data.csproj --startup-assembly C:\3.des\CSharp\Boliche\Boliche.Api\bin\Debug\net9.0\Boliche.Api.dll --startup-project C:\3.des\CSharp\Boliche\Boliche.Api\Boliche.Api.csproj --project-dir C:\3.des\CSharp\Boliche\Boliche.Data\ --root-namespace Boliche.Data --language C# --framework net9.0 --working-dir C:\3.des\CSharp\Boliche --verbose
Using assembly 'Boliche.Data'.
Using startup assembly 'Boliche.Api'.
Using application base 'C:\3.des\CSharp\Boliche\Boliche.Api\bin\Debug\net9.0'.
Using working directory 'C:\3.des\CSharp\Boliche\Boliche.Api'.
Using root namespace 'Boliche.Data'.
Using project directory 'C:\3.des\CSharp\Boliche\Boliche.Data\'.
Remaining arguments: .
Finding DbContext classes...
Using environment 'Development'.
Finding IDesignTimeDbContextFactory implementations...
Finding DbContext classes in the project...
Found DbContext 'BolicheContext'.
Finding application service provider in assembly 'Boliche.Api'...
Finding Microsoft.Extensions.Hosting service provider...
Using application service provider from Microsoft.Extensions.Hosting.
Using context 'BolicheContext'.
Finding design-time services referenced by assembly 'Boliche.Api'...
Finding design-time services referenced by assembly 'Boliche.Data'...
No referenced design-time services were found.
Finding design-time services for provider 'MySql.EntityFrameworkCore'...
Using design-time services from provider 'MySql.EntityFrameworkCore'.
Finding IDesignTimeServices implementations in assembly 'Boliche.Api'...
No design-time services were found.
The model for context 'BolicheContext' has pending changes. Add a new migration before updating the database. See https://aka.ms/efcore-docs-pending-changes.
Creating DbConnection.
Created DbConnection. (3ms).
Migrating using database 'boliche_bd' on server 'localhost'.
Opening connection to database 'boliche_bd' on server 'localhost'.
Opened connection to database 'boliche_bd' on server 'localhost'.
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (1ms).
Initialized DbCommand for 'ExecuteScalar' (3ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT GET_LOCK('__EFMigrationsLock',-1);
Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT GET_LOCK('__EFMigrationsLock',-1);
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (0ms).
Initialized DbCommand for 'ExecuteScalar' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT RELEASE_LOCK('__EFMigrationsLock');
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT RELEASE_LOCK('__EFMigrationsLock');
Closing connection to database 'boliche_bd' on server 'localhost'.
Closed connection to database 'boliche_bd' on server 'localhost' (0ms).
'BolicheContext' disposed.
Disposing connection to database 'boliche_bd' on server 'localhost'.
Disposed connection to database 'boliche_bd' on server 'localhost' (0ms).
System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.Int64'.
   at MySql.EntityFrameworkCore.Migrations.Internal.MySQLHistoryRepository.AcquireDatabaseLock()
   at MySql.EntityFrameworkCore.Migrations.Internal.MySQLMigrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Unable to cast object of type 'System.DBNull' to type 'System.Int64'.

解决方法 #

1. 禁用迁移锁 #

报错根源是MySQL驱动在处理GET_LOCK返回值时，未兼容DBNull的情况。直接禁用迁移锁即可绕过问题：
修改DbContext的OnConfiguring方法：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseMySQL(_connectionString)
                     .UseMigrationLocks(false);

2. 匹配版本兼容性 #

确保MySql.EntityFrameworkCore版本与.NET 9完全匹配（推荐9.0.x版本），跨大版本组合易引发类型转换类兼容性问题。

3. 重置迁移 #

若之前迁移残留问题，可执行以下步骤重置：

• 删除项目中所有迁移文件
• 登录MariaDB，删除boliche_bd数据库中的__EFMigrationsHistory表
• 重新生成初始迁移：

dotnet ef migrations add InitialCreate --project Boliche.Data --startup-project Boliche.Api

• 再次执行数据库更新：

dotnet ef database update --project Boliche.Data --startup-project Boliche.Api

4. 检查数据库权限 #

确认连接字符串中的root用户拥有执行GET_LOCK、RELEASE_LOCK的权限，或更换为权限更高的数据库用户。

内容的提问来源于stack exchange，提问作者Ariel Poblete