EF Core decimal precision for Always Encrypted column

Question

Hello I have SQL server with setting up always encrypted feature, also I setup EF for work with always encrypted columns, but when I try to add/update, for Db manipulation I use DbContext, entry in my Db I get follow error:

Operand type clash: decimal(1,0) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = '****', column_encryption_key_database_name = '****') is incompatible with decimal(6,2) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = '*****', column_encryption_key_database_name = '****')

Model that I use

public class Model
{
    /// <summary>
    /// Payment method name
    /// </summary>
    [Column(TypeName = "nvarchar(MAX)")]
    public string Name { get; set; }

    /// <summary>
    /// Payment method description
    /// </summary>
    [Column(TypeName = "nvarchar(MAX)")]
    public string Description { get; set; }

    /// <summary>
    /// Fee charges for using payment method
    /// </summary>
    [Column(TypeName = "decimal(6,2)")]
    public decimal Fee { get; set; }
}

Also I tried to specify decimal format in OnModelCreating method

 builder.Entity<Model>().Property(x => x.Fee).HasColumnType("decimal(6,2)");

What I missed ? Thanks for any advice

Answer 1

My colleague and I have found a workaround to the problem using the DiagnosticSource.

You must know that:

• Entity Framework Core hooks itself into DiagnosticSource.
• DiagnosticSource uses the observer pattern to notify its observers.

The idea is to populate the 'Precision' and 'Scale' fields of the command object (created by EFCore), in this way the call made to Sql will contain all the information necessary to correctly execute the query.

First of all, create the listener:

namespace YOUR_NAMESPACE_HERE
{
    public class EfGlobalListener : IObserver<DiagnosticListener>
    {
        private readonly CommandInterceptor _interceptor = new CommandInterceptor();

        public void OnCompleted()
        {
        }
        public void OnError(Exception error)
        {
        }
        public void OnNext(DiagnosticListener value)
        {
            if (value.Name == DbLoggerCategory.Name)
            {
                value.Subscribe(_interceptor);
            }
        }
    }
}

Where CommandInterceptor is:

namespace YOUR_NAMESPACE_HERE
{
    public class CommandInterceptor : IObserver<KeyValuePair<string, object>>
    {
        // This snippet of code is only as example, you could maybe use Reflection to retrieve Field mapping instead of using Dictionary
        private Dictionary<string, (byte Precision, byte Scale)> _tableMapping = new Dictionary<string, (byte Precision, byte Scale)>
        {
            { "Table1.DecimalField1", (18, 2) },
            { "Table2.DecimalField1", (12, 6) },
            { "Table2.DecimalField2", (10, 4) },
        };

        public void OnCompleted()
        {
        }
        public void OnError(Exception error)
        {
        }
        public void OnNext(KeyValuePair<string, object> value)
        {
            if (value.Key == RelationalEventId.CommandExecuting.Name)
            {
                // After that EF Core generates the command to send to the DB, this method will be called

                // Cast command object
                var command = ((CommandEventData)value.Value).Command;

                // command.CommandText -> contains SQL command string
                // command.Parameters -> contains all params used in sql command

                // ONLY FOR EXAMPLE PURPOSES
                // This code may contain errors.
                // It was written only as an example.

                string table = null;
                string[] columns = null;
                string[] parameters = null;

                var regex = new Regex(@"^INSERT INTO \[(.+)\] \((.*)\)|^VALUES \((.*)\)|UPDATE \[(.*)\] SET (.*)$", RegexOptions.Multiline);
                var matches = regex.Matches(command.CommandText);

                foreach (Match match in matches)
                {
                    if(match.Groups[1].Success)
                    {
                        // INSERT - TABLE NAME
                        table = match.Groups[1].Value;
                    }
                    if (match.Groups[2].Success)
                    {
                        // INSERT - COLS NAMES
                        columns = match.Groups[2].Value.Split(",", StringSplitOptions.RemoveEmptyEntries).Select(c => c.Replace("[", string.Empty).Replace("]", string.Empty).Trim()).ToArray();
                    }
                    if (match.Groups[3].Success)
                    {
                        // INSERT - PARAMS VALUES
                        parameters = match.Groups[3].Value.Split(",", StringSplitOptions.RemoveEmptyEntries).Select(c => c.Trim()).ToArray();
                    }
                    if (match.Groups[4].Success)
                    {
                        // UPDATE - TABLE NAME
                        table = match.Groups[4].Value;
                    }
                    if (match.Groups[5].Success)
                    {
                        // UPDATE - COLS/PARAMS NAMES/VALUES
                        var colParams = match.Groups[5].Value.Split(",", StringSplitOptions.RemoveEmptyEntries).Select(p => p.Replace("[", string.Empty).Replace("]", string.Empty).Trim()).ToArray();
                        columns = colParams.Select(cp => cp.Split('=', StringSplitOptions.RemoveEmptyEntries)[0].Trim()).ToArray();
                        parameters = colParams.Select(cp => cp.Split('=', StringSplitOptions.RemoveEmptyEntries)[1].Trim()).ToArray();
                    }
                }

                // After taking all the necessary information from the sql command
                // we can add Precision and Scale to all decimal parameters
                foreach (var item in command.Parameters.OfType<SqlParameter>().Where(p => p.DbType == DbType.Decimal))
                {
                    var index = Array.IndexOf<string>(parameters, item.ParameterName);
                    var columnName = columns.ElementAt(index);

                    var key = $"{table}.{columnName}";

                    // Add Precision and Scale, that fix our problems w/ always encrypted columns
                    item.Precision = _tableMapping[key].Precision;
                    item.Scale = _tableMapping[key].Scale;
                }
            }
        }
    }
}

Finally add in the Startup.cs the following line of code to register the listener:

DiagnosticListener.AllListeners.Subscribe(new EfGlobalListener());

Answer 2

Ecountered the same issue. Adjusted @SteeBono interceptor to work with commands which contain multiple statements:

    public class AlwaysEncryptedDecimalParameterInterceptor : DbCommandInterceptor, IObserver<KeyValuePair<string, object>>
{
    private Dictionary<string, (SqlDbType DataType, byte? Precision, byte? Scale)> _decimalColumnSettings =
        new Dictionary<string, (SqlDbType DataType, byte? Precision, byte? Scale)>
        {
            // MyTableDecimal
            { $"{nameof(MyTableDecimal)}.{nameof(MyTableDecimal.MyDecimalColumn)}", (SqlDbType.Decimal, 18, 6) },

            // MyTableMoney
            { $"{nameof(MyTableMoney)}.{nameof(MyTableMoney.MyMoneyColumn)}", (SqlDbType.Money, null, null) },

        };

    public void OnCompleted()
    {
    }

    public void OnError(Exception error)
    {
    }

    // After that EF Core generates the command to send to the DB, this method will be called
    public void OnNext(KeyValuePair<string, object> value)
    {
        if (value.Key == RelationalEventId.CommandExecuting.Name)
        {
            System.Data.Common.DbCommand command = ((CommandEventData)value.Value).Command;

            Regex regex = new Regex(@"INSERT INTO \[(.+)\] \((.*)\)(\r\n|\r|\n)+VALUES \(([^;]*)\);|UPDATE \[(.*)\] SET (.*)|MERGE \[(.+)\] USING \((\r\n|\r|\n)+VALUES \(([^A]*)\) AS \w* \((.*)\)");
            MatchCollection matches = regex.Matches(command.CommandText);

            foreach (Match match in matches)
            {
                (string TableName, string[] Columns, string[] Params) commandComponents = GetCommandComponents(match);
                int countOfColumns = commandComponents.Columns.Length;

                // After taking all the necessary information from the sql command
                // we can add Precision and Scale to all decimal parameters and set type for Money ones
                for (int index = 0; index < commandComponents.Params.Length; index++)
                {
                    SqlParameter decimalSqlParameter = command.Parameters.OfType<SqlParameter>()
                        .FirstOrDefault(p => commandComponents.Params[index] == p.ParameterName);

                    if (decimalSqlParameter == null)
                    {
                        continue;
                    }

                    string columnName = commandComponents.Columns.ElementAt(index % countOfColumns);

                    string settingKey = $"{commandComponents.TableName}.{columnName}";
                    if (_decimalColumnSettings.ContainsKey(settingKey))
                    {
                        (SqlDbType DataType, byte? Precision, byte? Scale) settings = _decimalColumnSettings[settingKey];
                        decimalSqlParameter.SqlDbType = settings.DataType;

                        if (settings.Precision.HasValue)
                        {
                            decimalSqlParameter.Precision = settings.Precision.Value;
                        }

                        if (settings.Scale.HasValue)
                        {
                            decimalSqlParameter.Scale = settings.Scale.Value;
                        }
                    }
                }
            }
        }
    }

    private (string TableName, string[] Columns, string[] Params) GetCommandComponents(Match match)
    {
        string tableName = null;
        string[] columns = null;
        string[] parameters = null;

        // INSERT
        if (match.Groups[1].Success)
        {
            tableName = match.Groups[1].Value;

            columns = match.Groups[2].Value.Split(",", StringSplitOptions.RemoveEmptyEntries)
                .Select(c => c.Replace("[", string.Empty)
                .Replace("]", string.Empty)
                .Trim()).ToArray();

            parameters = match.Groups[4].Value
                .Split(",", StringSplitOptions.RemoveEmptyEntries)
                .Select(c => c.Trim()
                    .Replace($"),{Environment.NewLine}(", string.Empty)
                    .Replace("(", string.Empty)
                    .Replace(")", string.Empty))
                .ToArray();

            return (
                TableName: tableName,
                Columns: columns,
                Params: parameters);
        }

        // UPDATE
        if (match.Groups[5].Success)
        {
            tableName = match.Groups[5].Value;
            string[] colParams = match.Groups[6].Value.Split(",", StringSplitOptions.RemoveEmptyEntries)
                .Select(p => p.Replace("[", string.Empty).Replace("]", string.Empty).Trim())
                .ToArray();

            columns = colParams.Select(cp => cp.Split('=', StringSplitOptions.RemoveEmptyEntries)[0].Trim()).ToArray();
            parameters = colParams.Select(cp => cp.Split('=', StringSplitOptions.RemoveEmptyEntries)[1].Trim()).ToArray();

            return (
                TableName: tableName,
                Columns: columns,
                Params: parameters);
        }

        // MERGE
        if (match.Groups[7].Success)
        {
            tableName = match.Groups[7].Value;
            parameters = match.Groups[9].Value.Split(",", StringSplitOptions.RemoveEmptyEntries)
                .Select(c => c.Trim()
                    .Replace($"),{Environment.NewLine}(", string.Empty)
                    .Replace("(", string.Empty)
                    .Replace(")", string.Empty))
                .ToArray();

            columns = match.Groups[10].Value.Split(",", StringSplitOptions.RemoveEmptyEntries).Select(c => c.Replace("[", string.Empty).Replace("]", string.Empty).Trim()).ToArray();

            return (
                TableName: tableName,
                Columns: columns,
                Params: parameters);
        }

        throw new Exception($"{nameof(AlwaysEncryptedDecimalParameterInterceptor)} was not able to parse the command");
    }
}