MySql.EntityFrameworkCore: Unable to cast object of type 'System.DBNull' to type 'System.Int64'

Ask Question

Asked 5 months ago

Modified 5 months ago

Viewed 212 times

I'm trying to build a minimal Api for access to a local MariaDB database and my tech is .NET 9, EF, MySql.EntityFrameworkCore, Migrations (Code First) and MariaDB (XAMPP).

When I try to update database I get the error:

Unable to cast object of type 'System.DBNull' to type 'System.Int64'.

My code is very simple:

My App Structure

Client Model

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; }
}

Context

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);
}

Execution:

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

Result:

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

Can anyone help me?

edited Jun 13 at 15:40

Ferry To

1,9805 gold badges45 silver badges56 bronze badges

asked Jun 11 at 13:26

Ariel Poblete

512 bronze badges

1

try the pomeloEF Library the version from oracle is notoriously bad and not well maintained. but before you do that please share the configurations of the DB with us

LMA
– LMA

2025-06-11 13:35:47 +00:00
Commented Jun 11 at 13:35
Thnak for aswer. The configuration is the standard configuration from XAMPP. looking for somthing in particulary?

Ariel Poblete
– Ariel Poblete

2025-06-11 14:42:57 +00:00
Commented Jun 11 at 14:42
Is there existing data in that table that might have a null ID? Do you still get an error when Id is of type "int?" in the C#?

SMartDev
– SMartDev

2025-06-11 19:58:31 +00:00
Commented Jun 11 at 19:58
Like @SMartDev already mentioned, it seems like the Id column contains Null-Values. Can you access the Db directly to prove that?

Nikolaus
– Nikolaus

2025-06-11 20:18:03 +00:00
Commented Jun 11 at 20:18
This seems to be a bug in the database provider MySql.EntityFrameworkCore you are using. It has nothing to do with your entity model, since from the log the failing function is GET_LOCK as part of the migration process. Thus, you either try switching to Pomelo.EntityFrameworkCore.MySql provider as mentioned by @LMA, or report an issue and wait for resolution by Oracle. Pomelo is better option IMO (and not only).

Ivan Stoev
– Ivan Stoev

2025-06-12 07:10:37 +00:00
Commented Jun 12 at 7:10

| Show 1 more comment

0 Your Answer

Sign up or log in

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.

Collectives™ on Stack Overflow

MySql.EntityFrameworkCore: Unable to cast object of type 'System.DBNull' to type 'System.Int64'

0

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Sign up or log in

Post as a guest