用(encryption_type ='DETERMINISTIC'xxx)加密的数据类型nvarchar(max)在等于运算符中不兼容

问题描述

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();

错误:

顺便说一下，数据库列是加密的，大多数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)在等于运算符中不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！