[Always Encrypted] EF Core 5.0 Operand type clash : nvarchar(4000) is incompatible with decimal(19,3)

[Programmer04515]

nvarchar(4000) is incompatible with decimal(19,3) when Always Encrypted is enabled on a decimal column

If values are supplied for Dec1, Dec2, and Dec3 then the add to database call works just fine. However, when one of these columns are equal to NULL the following error is thrown.

SqlException: Operand type clash: nvarchar is incompatible with decimal(19,3) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'Answers')

Answer.cs

namespace SharedLibrary.Answer
{
    [Table("Answer", Schema = "anw")]
    public class Answer
    {
        [Key]
        public int ID {get;set;}

        [Column(TypeName = "decimal(19,3)")]
        public decimal? Dec1 { get; set; }

        [Column(TypeName = "decimal(19,3)")]
        public decimal? Dec2 { get; set; }

        [Column(TypeName = "decimal(19,3)")]
        public decimal? Dec3 { get; set; }
    }
}

DBContext.cs

        ...
        protected string connectionString { get; set; }

        connectionString = "server = StorageServer; Database=Answers; uid=SomeName; password=SomePassword; encrypt=true; trustServerCertificate=true; Column Encryption Setting = enabled;"

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
            //.UseLazyLoadingProxies()
            .UseSqlServer(connectionString);
        }

        public virtual DbSet<Answer> Answers { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Answer>().HasIndex(c => c.ID).IsUnique(true);
            modelBuilder.Entity<Answer>(entity =>
            {
                entity.Property(e => e.ID).ValueGeneratedOnAdd();
                entity.Property(e => e.Dec1).HasPrecision(19,3);
                entity.Property(e => e.Dec2).HasPrecision(19,3);
                entity.Property(e => e.Dec3).HasPrecision(19,3);
            }
         }
         ,,,

Framework Specs

    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.9">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>

    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="5.0.9" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Proxies" Version="5.0.9" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc.NewtonsoftJson" Version="5.0.9" />

Error Producing Code

            DbContext smdbc = new DbContext();

            Answer ans = new Answer();  /// <<< Three NULL Decimal Values Here

            smdbc.Answers.Add(ans);

            smdbc.SaveChanges(); // <<<<<Error Happens Here 

[ajcvickers]

@Programmer04515 Please post the code you are running against the context that generates this exception.

[Programmer04515]

@ajcvickers More code as been supplied. Need anything else?

[roji]

@Programmer04515 any information on where the nvarchar(4000) column is coming from? The example above shows only 3 decimal properties - are you by any chance trying to map a .NET decimal property to a SQL Server nvarchar column? If not, a full, runnable code sample along with the database schema would be helpful.

[ajcvickers]

@Programmer04515 Thanks; I am able to reproduce this.

Note for triage: this is because we are not setting the DbType for the parameter.

public static class Your
{
    public static string ConnectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow;Column Encryption Setting = Enabled";
}

[Table("Answer", Schema = "anw")]
public class Answer
{
    [Key]
    public int ID {get;set;}

    [Column(TypeName = "decimal(19,3)")]
    public decimal? Dec1 { get; set; }

    [Column(TypeName = "decimal(19,3)")]
    public decimal? Dec2 { get; set; }

    [Column(TypeName = "decimal(19,3)")]
    public decimal? Dec3 { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .LogTo(Console.WriteLine, LogLevel.Information)
            .AddInterceptors(new Interceptor())
            .UseSqlServer(Your.ConnectionString)
            .EnableSensitiveDataLogging();

    public virtual DbSet<Answer> Answers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Answer>().HasIndex(c => c.ID).IsUnique(true);
        modelBuilder.Entity<Answer>(entity =>
        {
            entity.Property(e => e.ID).ValueGeneratedOnAdd();
            entity.Property(e => e.Dec1).HasPrecision(19, 3);
            entity.Property(e => e.Dec2).HasPrecision(19, 3);
            entity.Property(e => e.Dec3).HasPrecision(19, 3);
        });
    }
}

public class Interceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        return base.ReaderExecuting(command, eventData, result);
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            // context.Database.EnsureDeleted();
            // context.Database.EnsureCreated();

            Answer ans = new Answer();
            context.Answers.Add(ans);
            context.SaveChanges();
        }
    }
}

``

[aaronjedwards]

@ajcvickers is there any known workaround for this in the meantime?

[ajcvickers]

@aaronjedwards 6.0 is out today. The best workaround is to update.

[aaronjedwards]

I'm not in a position to update right now, but in my case my schema is flexible, so I am going to attempt to change my column type to match what is being inferred in the query that EF generates.