用(encryption_type ='DETERMINISTIC'xxx)加密的数据类型nvarchar(max)在等于运算符中不兼容 [英] 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();

错误:

[17:16:22 ERR]错误:使用(encryption_type ='DETERMINISTIC'，encryption_algorithm_name ='AEAD_AES_256_CBC_HMAC_SHA_256'，column_encryption_key_name ='CEK_WITH_AKV'，column_encryption_base， 2)加密为(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')在等于运算符中不兼容

[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.

顺便说一下，数据库列是加密的，大多数nvarchar列是加密的.

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?

推荐答案

AspNetUserClaims.ClaimValue是nvarchar(max)

@countryCode是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.

因此，您还需要将参数声明为nvarchar(max). EG

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;

这篇关于用(encryption_type ='DETERMINISTIC'xxx)加密的数据类型nvarchar(max)在等于运算符中不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！