始终加密、LINQ和Where Contains

Question

通常，人们这样做是为了返回与列表匹配的数据集:

string[] ssn = { "123456789", "987654321" };

var result_set = db.employee.Where(w => ssn.Contains(w.SSN)).ToList();

然而，当使用Always Encrypted加密SSN列时，会出现以下错误：

SqlException：数据类型为varchar（9）的加密数据， （encryption_type ='DETERMINISTIC'，encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256'，column_encryption_key_name = 'CEK_SSN'，column_encryption_key_database_name = 'MyCompany'） collation_name = 'Latin1_General_BIN2'与varchar在等于操作符中不兼容。

一般来说设置都是正确的，因为一个单一的值可以工作：

string ssn = "123456789";

var result_set = db.employee.Where(w => w.SSN == ssn).ToList();

我正在寻找最佳的客户端处理方式。我知道这种解决方法可行，但需要传输整个表格：

var result_set = db.employee.ToList().Where(w => ssn.Contains(w.SSN));

我看过一些示例（与旧版本的.NET相关，不一定涉及始终加密）其中有一些花哨的扩展程序创建了许多“or”条件。我还知道使用表变量可以在存储过程中进行一些巧妙的处理。但我真的正在寻找一种优雅的方式来完成这个任务，最好是通过LINQ，但至少也要在客户端代码中实现。我现在正处于确定始终加密是否会对新项目带来任何无法克服的障碍的阶段，因此我非常乐意听取建议。

- John Chase

---

使用LINQ，我建议在客户端循环并将不同社保号码的所有结果合并在客户端上。 - Piotr Palka

是的，我也有这个想法... - John Chase

你找到一种执行 contains 方法的方法了吗？ - Anupam Sharma

哇，这是一段时间以前的事情了。我已经从那个项目中脱身了很久。抱歉，我不能提供更多的帮助。 - John Chase

Answer 1

您可能需要生成谓词，使用或链模拟ssn.Contains(w.SSN)调用。创建它应该很简单。

var result_set = db.employee.Where(GenerateContainsSsn(ssn)).ToList();

Expression<Func<Employee, bool>> GenerateContainsSsn<T>(IEnumerable<T> collection)
{
    var param = Expression.Parameter(typeof(Employee));
    var body = collection.Select(v =>
            Expression.Equal(Expression.Property(param, "SSN"), Expression.Constant(v))
        )
        .Aggregate((a, b) => Expression.OrElse(a, b));
    return Expression.Lambda<Func<Employee, bool>>(body, param);
}

如果可能的话，最好寻找支持始终加密功能的linq驱动程序，或者至少有一个选项来生成兼容的查询。

- Jeff Mercado

---

哇，优雅而令人敬畏。感觉自己甚至还没有探索 .NET 所提供的所有功能。但是......我尝试了一下，结果出现了相同的错误。 - John Chase

是的，看着返回表达式的 DebugView 属性，我明白了。Always Encrypted 只能使用参数，而不能使用文本常量。 - John Chase

DebugView = ".Lambda #Lambda1<System.Func`2[AlwaysEncrypted.Models.Employee,System.Boolean]>(AlwaysEncrypted.Models.Employee $var1) {\r\n $var1.SSN == "123456789" || $var1.SSN == "987654321"\r\n}" 调试视图 = ".Lambda #Lambda1<System.Func`2 [AlwaysEncrypted.Models.Employee, 系统.布尔]> (AlwaysEncrypted.Models.Employee $ var1) {\ r \ n $ var1.SSN ==“123456789”|| $ var1.SSN ==“987654321”\ r \ n}" - John Chase

您能否提供一个生成的查询示例，以便在您的问题中起作用？我对始终加密功能并不是非常熟悉，也不确定查询会是什么样子。我假设它在服务器上是自动的。 - Jeff Mercado

有趣的问题。尝试在纯SQL中让WHERE IN起作用似乎也很棘手。例如，以下代码无法正常工作：declare @ssn_list varchar(max) = '123456789,987654321'select * from dbo.Employee where SSN in (select value from string_split(@ssn_list, ',')) - John Chase

错误信息为：无法在第5行将相同的加密方案分配给两个表达式。第一个表达式的加密方案为（encryption_type = 'DETERMINISTIC'，encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256'，column_encryption_key_name = 'CEK_DMS_SSN'，column_encryption_key_database_name = 'xx'），而第二个表达式的加密方案为（encryption_type = 'PLAINTEXT'）。 - John Chase