Why is HasComputedColumnSql ignored?

Question

There is a very old table in a database that contains information about departments and a flag whether the department is active or not. The problem is that this flag is stored as a string with 0 or 1 and an unknown number of extra spaces. When I try to process this flag using HasComputedColumnSql and decode(trim(is_active), '1', 1, 0) expression, then when I subsequently access the model via Linq query (dbContext.Departments.Where(d => d.IsActive)), the IsActive property from the model is used for SQL instead of the calculated field. The calculated field itself is simply ignored. What am I doing wrong?

I am using Oracle.EntityFrameworkCore 8.23.50 and OracleDB 12.1.

//Model
public class Department
{
    public required int Id { get; init; }
    public required string Name { get; init; }
    public required bool IsActive { get; init; }
    //other members
}

//Configuration
public class DepartmentConfig : IEntityTypeConfiguration<Department>
{
    public void Configure(EntityTypeBuilder<Department> builder)
    {
        builder.Property(e => e.Id).HasColumnName("ID_DEPT");
        builder.Property(e => e.Name).HasColumnName("NAME_DEPT");
        builder.Property(e => e.IsActive).HasComputedColumnSql("decode(trim(is_active), '0', 0, 1)", false);
        builder.ToTable("DEPARTMENTS").HasKey(e => e.Id);
    }
}

Answer 1

I recommend using a calculated property with the LINQKit extension. The original is_active field should be mapped to a separate property. Here's an example:

// Model
public class Department
{
    public required int Id { get; init; }
    public required string Name { get; init; }
    public required string IsActiveRaw { get; init; }

    // Other members

    [Expandable(nameof(IsActiveImpl))]
    [NotMapped]
    public bool IsActive => IsActiveRaw == "1";

    static Expression<Func<Department, bool>> IsActiveImpl()
        => d => d.IsActiveRaw == "1"; // maybe trimming is needed, it depends on column type
}

// Configuration
public class DepartmentConfig : IEntityTypeConfiguration<Department>
{
    public void Configure(EntityTypeBuilder<Department> builder)
    {
        builder.Property(e => e.Id).HasColumnName("ID_DEPT");
        builder.Property(e => e.Name).HasColumnName("NAME_DEPT");
        builder.Property(e => e.IsActiveRaw).HasColumnName("is_active");
        builder.ToTable("DEPARTMENTS").HasKey(e => e.Id);
    }
}

Enable LINQKit when configuring the DbContext:

builder
    .UseOracle(connectionString)
    .WithExpressionExpanding();  

This will allow you to use the new property directly in queries:

var query = dbContext.Departments
    .Where(d => d.IsActive);