'The data types nvarchar(max) encrypted with with (encryption_type = 'DETERMINISTIC' xxx) are incompatible in the equal to operator

var countryCode = new SqlParameter("@countryCode", SqlDbType.VarBinary);
var byteArray = Encoding.UTF8.GetBytes(dto.Country);
countryCode.Value = byteArray;
var country = new SqlParameter("@country", "country");
country.Value = "country";

var rawUsers = DbContext.Users.FromSqlRaw("Select u.* from AspNetUsers u join AspNetUserClaims uc on
                                            u.Id = uc.UserId where uc.ClaimType = @country and
                                            uc.ClaimValue = @countryCode", country, countryCode)
                              .ToList();

ERROR:

[17:16:22 ERR] Error:The data types nvarchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_WITH_AKV', column_encryption_key_database_name = 'MyDatabase') and varbinary(2) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_WITH_AKV', column_encryption_key_database_name = 'MyDatabase') are incompatible in the equal to operator.

By the way, db columns are encrypted most nvarchar columns are encrypted.

I've been looking for solutions to make it right. But seems nothing works. Did I miss anything? Anyone who can help?

sql-serverentity-frameworkencryptionsqldatatypesdeterministic


Solution 1:[1]

AspNetUserClaims.ClaimValue is nvarchar(max)

@countryCode is varbinary(2)

AlwaysEncrypted is client-side encryption. So the encrypted value of the parameter must match exactly the encrypted value of the column. Consequently there can be no server-side implicit conversions to do the comparison and the data types must match exactly.

So you need to declare the parameter as nvarchar(max) as well. EG

   var countryCode = new SqlParameter("@countryCode", SqlDbType.NVarChar,-1);
   countryCode.Value = dto.Country;

Solution 2:[2]

Make sure your connection string has "Column Encryption Setting=Enabled;" setting. I had this issue when my parameters from C# code matched exactly including size and precision for decimal types to SQL parameters but without this setting in your connection string, you get the above error.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 David Browne - Microsoft
Solution 2 Harini

Related Questions