Всегда зашифровано, LINQ и где содержится

Как правило, это нужно сделать, чтобы вернуть набор данных, соответствующий списку:

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

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

Однако, когда столбец SSN зашифрован с помощью Always Encrypted, возникает эта ошибка:

SqlException: типы данных varchar(9), зашифрованные с помощью (encryption_type = 'DETERMINISTIC', шифрование_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_varchar2' и в операторе equal to несовместимы .

В целом все настроено правильно, потому что работает одно значение:

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, не обязательно в отношении Always Encrypted), где есть какое-то причудливое расширение, которое создает кучу «или». Я также знаю, что с табличными переменными я мог бы делать некоторые хитрые вещи с хранимой процедурой. Но я действительно ищу элегантный способ сделать это, желательно через LINQ, но, по крайней мере, в клиентском коде. Я нахожусь на этапе определения того, представляет ли Always Encryption какие-либо непреодолимые препятствия для нового проекта, поэтому я открыт для предложений.


sql-server linq always-encrypted
person John Chase    schedule 23.05.2019    source источник
comment
С LINQ я бы рекомендовал цикл на стороне клиента и объединить на клиенте все результаты для разных номеров SSN.   -  person Piotr    schedule 23.05.2019
comment
Да, у меня тоже была такая мысль...   -  person John Chase    schedule 23.05.2019

Ответы (1)


arrow_upward
1
arrow_downward

Возможно, вам придется сгенерировать предикат, который имитирует вызов 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, который поддерживает функцию постоянного шифрования или, по крайней мере, имеет возможность генерировать совместимые запросы.

person Jeff Mercado    schedule 23.05.2019
comment
Ух ты. Элегантный и скромный. Такое ощущение, что я даже не коснулся всего, что предлагает .NET. Но... Я попробовал, и я получаю ту же ошибку. - person John Chase; 24.05.2019
comment
Да, глядя на свойство DebugView возвращаемого выражения, я понимаю, почему. Always Encrypted работает только с параметрами, а не с текстовыми константами. - person John Chase; 24.05.2019
comment
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} - person John Chase; 24.05.2019
comment
Не могли бы вы привести пример сгенерированного запроса, когда он работает в вашем вопросе? Я не очень хорошо знаком с функцией постоянного шифрования и не уверен, как будет выглядеть запрос. Я предположил, что это было автоматически на сервере. - person Jeff Mercado; 24.05.2019
comment
Интересный вопрос. Попытка заставить WHERE IN работать в чистом SQL тоже кажется проблематичной. Например, это не работает: объявить @ssn_list varchar(max) = '123456789,987654321' выбрать * из dbo.Employee, где SSN в (выбрать значение из string_split(@ssn_list, ',')) - person John Chase; 24.05.2019
comment
Ошибка: невозможно назначить одну и ту же схему шифрования двум выражениям в строке «5». Схема шифрования первого выражения (encryption_type = 'DETERMINISTIC', шифрование_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'). - person John Chase; 24.05.2019