Entity Framework Firebird Provider wrong translation of boolean expression

[niklasxulls]

Issue

I am currenty migrating my .NET 6 project to .NET 10. Going hand in hand with his I upgraded all the Nuget packages including Entity Framework Core (now version 10.0.1) and FirebirdSql.EntityFrameworkCore.Firebird (now pre-release version 13.0.0-alpha1)

Now I noticed that queries that involve boolean checks (that are smallints in the db) are translated wrong, which causes the SQL statement to fail. In partciluar statements that use custom expression trees with booleans. The expression trees are built by leveraging DelegateDecompiler in my case.

While the project was on .NET 6 I already had those issues with the query translation, but funnily enough only when running the project via Visual Studio 2019, which uses msbuild for building the solution. The workaround back then was to always build the project via dotnet build with release configuration (dotnet build -c Release)

However, now this workaround doesn't work anymore.
If this could be relevant: I use a firebird 3.0 database, with DataTypeCompatbility=2.5

Minimal Example: (simplified)

Activity:

public class Activity {
    public bool? IsInternal { get; set; }
    public bool? IsNotBillable {get; set; }
    public bool? IsInactive { get; set; }

    public decimal CostsGross { get {
            return IsInternal == true || IsNotBillable == true || IsInactive == true ? 0m : -1m;
        } 
    }
}

Activity Configuration:

...
builder.Ignore(a => a.CostsGross);
builder.Computed(a => a.CostsGross);

builder.Property(a => a.IsInternal).HasConversion<BooleanNullableToSmallIntNullableValueConverter>().HasColumnName("INTERN");
builder.Property(a => a.IsNotBillable).HasConversion<BooleanNullableToSmallIntNullableValueConverter>().HasColumnName("NICHTVER");
builder.Property(a => a.IsInactive).HasConversion<BooleanNullableToSmallIntNullableValueConverter>().HasColumnName("INAKTIV");
...

BooleanNullableToSmallIntNullableValueConverter:

public class BooleanNullableToSmallIntNullableValueConverter : ValueConverter<bool?, short?>
{
    public BooleanNullableToSmallIntNullableValueConverter() : 
        base(BooleanNullableToSmallIntNullableConversion, BooleanNullableFromSmallIntNullableConversion)
    {
    }


  public static Expression<Func<bool?, Int16?>> BooleanNullableToSmallIntNullableConversion = pInMemory => pInMemory == true ? 1 : null;
  public static Expression<Func<Int16?, bool?>> BooleanNullableFromSmallIntNullableConversion = pInDB => pInDB != null && pInDB > 0;
      
}

Firebird Configuration:

...
                    services.AddDbContext<MyDbContext>((serviceProvider, options) =>
                    {
                        options.UseFirebird(connectionStr, b => b.UseRelationalNulls(false)
                                                                 .WithExplicitParameterTypes(true)
                                                                 .WithExplicitStringLiteralTypes(true)
                                                                 .MigrationsAssembly(typeof(MyDbContext).Assembly.FullName)
                        );
                    }, optionsLifetime: ServiceLifetime.Scoped);
...

Query:

var query = _context.Activities.Select(a => a.CostsGross).Take(100);

query = DelegateDecompiler.EntityFrameworkCore.DecompileExtensions.DecompileAsync(query);
        
var results = await query.ToListAsync(cancellationToken);

Generated SQL:

      SELECT CASE
          WHEN COALESCE("l"."INTERN", NULL) THEN 0.0
          WHEN COALESCE("l"."NICHTVER", NULL) THEN 0.0
          WHEN COALESCE("l"."INAKTIV", NULL) THEN 0.0
          ELSE -1.0
      END
      FROM "LEISTUNG" AS "l"
      ROWS (CAST(@p AS INTEGER))

Exception:

[14:47:09 EROR] [Microsoft.EntityFrameworkCore.Query] An exception occurred while iterating over the results of a query for context type 'MyDbContext'.
FirebirdSql.Data.FirebirdClient.FbException (0x80004005): Dynamic SQL Error
SQL error code = -104
Invalid usage of boolean expression
 ---> FirebirdSql.Data.Common.IscException: Dynamic SQL Error
SQL error code = -104
Invalid usage of boolean expression
   at FirebirdSql.Data.Client.Managed.IResponseExtensions.HandleResponseException(IResponse response)
   at FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.ReadResponseAsync(CancellationToken cancellationToken)
   at FirebirdSql.Data.Client.Managed.Version11.GdsStatement.PrepareAsync(String commandText, CancellationToken cancellationToken)
   at FirebirdSql.Data.Client.Managed.Version11.GdsStatement.PrepareAsync(String commandText, CancellationToken cancellationToken)
   at FirebirdSql.Data.FirebirdClient.FbCommand.PrepareAsync(Boolean returnsSet, CancellationToken cancellationToken)
   at FirebirdSql.Data.FirebirdClient.FbCommand.PrepareAsync(Boolean returnsSet, CancellationToken cancellationToken)
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommandAsync(CommandBehavior behavior, Boolean returnsSet, CancellationToken cancellationToken)
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

In this case the expression should be translated to COALESCE(...) = 1, however this is clearly not the case. With the workaround that worked previously the expression was correctly translated to COALESCE(...)