Sqlite Database Providers Unable to cast object of type 'System.Int64' to type 'System.Boolean'.

[LEIRONGHUA]

Description

When using BulkInsertAsync with Z.EntityFramework.Extensions.EFCore in a project based on Volo.Abp.EntityFrameworkCore.Sqlite, setting options.AutoMapOutputDirection = true throws a casting exception.

• Exception occurs ONLY on SQLite database
• Works perfectly on PostgreSQL and SQL Server
• No error when options.AutoMapOutputDirection = false (all databases)

Environment

• Library: Z.EntityFramework.Extensions.EFCore v10.105.5
• Framework: Volo.Abp.EntityFrameworkCore (ABP Framework) v10.4.0
• Database Providers: Sqlite
• Exception Type: System.InvalidCastException

Exception Message

Unhandled exception. System.InvalidCastException: Unable to cast object of type 'System.Int64' to type 'System.Boolean'.
   at lambda_method77(Closure, Object, Object)
   at .SetValue(Object obj, Object value)
   at Z.EntityFramework.Extensions.PropertyZInfo.SetValue( reader, Object item, Object value, String propertyName)
   at .`1.(Int32 , String , Object )
   at .SetValue(Int32 position,  accessor, Object value)
   at .SetValue(Int32 position,  accessor, Object value)
   at .( , DataSet , String , Boolean )
   at .( , DbCommand )
   at .Execute(List`1 actions)
   at .(List`1 )
   at Z.BulkOperations.BulkOperation.Execute()
   at Z.BulkOperations.BulkOperation.BulkInsert()
   at .BulkInsert[T](DbContext this, IEntityType entityType, IEnumerable`1 list, Action`1 options, SavingSelector savingSelector, Boolean forceSpecificTypeMapping, Boolean isOptimized)
   at .BulkInsert[T](DbContext this, IEnumerable`1 entities, BulkOperation`1 optionsInstance, Action`1 options, Boolean isBulkSaveChanges, Boolean isOptimized)
   at DbContextExtensions.BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 options)
   at DbContextExtensions.`1.()
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at .[](DbContext , Func`2 , Action`1 , CancellationToken )
   at Z.EfCode.Extensions.PgBulkInsert.Program.MainAsync() in C:\Users\UserName\RiderProjects\Z.EfCode.Extensions\Z.EfCode.Extensions.PgBulkInsert\Program.cs:line 45
   at Z.EfCode.Extensions.PgBulkInsert.Program.MainAsync() in C:\Users\UserName\RiderProjects\Z.EfCode.Extensions\Z.EfCode.Extensions.PgBulkInsert\Program.cs:line 77
   at Z.EfCode.Extensions.PgBulkInsert.Program.Main(String[] args) in C:\Users\UserName\RiderProjects\Z.EfCode.Extensions\Z.EfCode.Extensions.PgBulkInsert\Program.cs:line 25
   at Z.EfCode.Extensions.PgBulkInsert.Program.<Main>(String[] args)

Minimal Repro Code

This is a self-contained, minimal console application that reproduces the error.

1. project.csproj

<Project Sdk="Microsoft.NET.Sdk">

    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net10.0</TargetFramework>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="10.0.8"/>
        <PackageReference Include="Volo.Abp.Ddd.Domain" Version="10.4.0"/>
        <PackageReference Include="Volo.Abp.EntityFrameworkCore.PostgreSql" Version="10.4.0"/>
        <PackageReference Include="Volo.Abp.EntityFrameworkCore.SqlServer" Version="10.4.0"/>
        <PackageReference Include="Volo.Abp.EntityFrameworkCore.Sqlite" Version="10.4.0" />
        <PackageReference Include="Z.EntityFramework.Extensions.EFCore" Version="10.105.5"/>
        
    </ItemGroup>

</Project>

2. Program.cs

using System.Diagnostics;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Npgsql;
using Volo.Abp.Domain.Entities.Auditing;
using Volo.Abp.EntityFrameworkCore;
using Volo.Abp.EntityFrameworkCore.Modeling;
using Z.BulkOperations;

namespace Z.EfCode.Extensions.PgBulkInsert;

public class Program
{
    private static readonly ILoggerFactory LoggerFactory = Microsoft.Extensions.Logging.LoggerFactory.Create(builder =>
    {
        builder.AddConsole();
        builder.SetMinimumLevel(LogLevel.Information);
    });

    private static readonly ILogger<Program> Logger = LoggerFactory.CreateLogger<Program>();

    private static async Task<int> Main(string[] args)
    {
        await MainAsync();
        return 0;
    }

    private static async Task MainAsync()
    {
        await using var context = new EntityContext();
        await context.Database.EnsureCreatedAsync();


        var customers = GenerateCustomers(5);

        var auditEntries = new List<AuditEntry>();

        var tcs = new CancellationTokenSource();
        var token = tcs.Token;

        var stopwatch = Stopwatch.StartNew();

        Logger.LogInformation("Starting batch insertion of {Count} records...", customers.Count);
        await context.BulkInsertAsync(customers, options =>
            {
                options.UseAudit = true;
                options.AuditEntries = auditEntries;
                options.AuditMode = AuditModeType.ExcludeAll;
                options.PostConfiguration = bulkOperation =>
                {
                    bulkOperation.ColumnMappings
                        .Where(x => x.SourceName is nameof(Customer.CustomerId))
                        .ToList().ForEach(p => { p.AuditMode = ColumnMappingAuditModeType.Include; });
                };

                options.BatchSize = 100;
                options.AutoMapOutputDirection = true; // Default value is true, A System.InvalidCastException occurred, setting it to false can resolve the issue
                options.InsertIfNotExists = true;
                options.ColumnPrimaryKeyExpression = p => new
                {
                    p.Name
                };
            },
            token);

        stopwatch.Stop();
        Logger.LogInformation("Batch insertion completed, time consumed {ElapsedMs} ms",
            stopwatch.ElapsedMilliseconds);

        await tcs.CancelAsync();

        // // 获取插入的ID
        var insertedIds = auditEntries.Where(p => p.Action == AuditActionType.Insert).SelectMany(p => p.Values)
            .Where(p => p.ColumnName == nameof(Customer.CustomerId) && p.NewValue != null)
            .Select(p => p.NewValue.ToString()).ToList();
        Logger.LogInformation("Inserted IDs: {Ids}", string.Join(", ", insertedIds));
    }

    private static List<Customer> GenerateCustomers(int count)
    {
        var list = new List<Customer>();

        for (var i = 0; i < count; i++)
        {
            list.Add(new Customer()
            {
                CustomerId = i,
                Name = "Customer_" + i
            });
        }

        return list;
    }

    public class EntityContext : DbContext 
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // or use Postgre Sql
            // optionsBuilder.UseNpgsql(
            //     new NpgsqlConnection(
            //         "Host=localhost;Port=5432;Database=PgBulkInsert;User ID=postgres;Password=myPassword;"));
            // or use SQL Server
            // optionsBuilder.UseSqlServer("Server=localhost,1434;Database=PgBulkInsert;User Id=sa;password=myPassw0rd;TrustServerCertificate=True");
            
            var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            optionsBuilder.UseSqlite(connection);
            
            optionsBuilder.UseLoggerFactory(LoggerFactory);
            optionsBuilder.EnableSensitiveDataLogging().EnableDetailedErrors();
            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Customer>(b =>
            {
                b.ConfigureByConvention();
                b.Property(x => x.Name).HasMaxLength(256);
            });
        }

        public DbSet<Customer> Customers { get; set; }
    }


    public class Customer : FullAuditedAggregateRoot<Guid>
    {
        public Customer() : base(Guid.NewGuid())
        {
            CreationTime = DateTime.UtcNow;
        }

        public int CustomerId { get; set; }
        public string? Name { get; set; }
    }
}

[JonathanMagnan]

Hello @LEIRONGHUA,

Thank you for reporting.

Indeed, if the CLR type is a long and the database type is a bool (byte), our library currently doesn't perform the conversion.

A fix should be available in our next release (June 9).

Best Regards,

Jon

[JonathanMagnan]

Hello @LEIRONGHUA ,

A new version has been released today fixing this issue: https://github.com/zzzprojects/EntityFramework-Extensions/releases/tag/10.105.6.0

Let us know if the new version works as expected.

Best Regards,

Jon