7

Typically, one would do this to return a data set that matches a list:

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

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

However, when the SSN column is encrypted via Always Encrypted, this error occurs:

SqlException: The data types varchar(9) encrypted with (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' and varchar are incompatible in the equal to operator.

Things in general are setup correctly, because a single value works:

string ssn = "123456789";

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

I'm looking for the best way to do this client-side. I know this work-around works, but it requires the entire table to come across the wire:

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

I've seen some examples (related to older version of .NET, not necessarily in regards to Always Encrypted) where there's some fancy extension that creates a bunch of "or"s. I also know that with table variables I could do some tricky stuff with a stored procedure. But I'm really looking for an elegant way to do this, preferably via LINQ, but at least, in the client code. I'm in the phase of determining if Always Encrypted presents any impassible barriers for a new project, so I'm wide open to suggestions.

sri harsha
  • 676
  • 6
  • 16
John Chase
  • 328
  • 3
  • 10

1 Answers1

1

You may have to generate the predicate that simulates the ssn.Contains(w.SSN) call with the or chain. It should be simple to create.

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

If available, it might be better to look for a linq driver that supports the always encrypted feature, or at least has an option to generate queries that would be compatible.

Jeff Mercado
  • 129,526
  • 32
  • 251
  • 272
  • 1
    Wow. Elegant, and humbling. Feel like I haven't even scratched the surface of all that .NET offers. But... I tried it out, and I'm getting the same error. – John Chase May 24 '19 at 11:44
  • Yes, looking at the DebugView property of the return expression, I see why. Always Encrypted only works with parameters, never text constants. – John Chase May 24 '19 at 13:13
  • DebugView = ".Lambda #Lambda1(AlwaysEncrypted.Models.Employee $var1) {\r\n $var1.SSN == \"123456789\" || $var1.SSN == \"987654321\"\r\n}" – John Chase May 24 '19 at 13:13
  • Would you mind providing an example of the generated query for when it works in your question? I'm not terribly familiar with the always encrypted feature and am not sure what the query would look like. I assumed it was automatic in the server. – Jeff Mercado May 24 '19 at 15:06
  • Interesting question. Trying to get WHERE IN to work in straight SQL seems problematic, too. For instance, this does not work: declare @ssn_list varchar(max) = '123456789,987654321' select * from dbo.Employee where SSN in (select value from string_split(@ssn_list, ',')) – John Chase May 24 '19 at 15:35
  • The error is: Cannot assign the same encryption scheme to two expressions at line '5'. The encryption scheme of the first expression is (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') and the encryption scheme of the second expression is (encryption_type = 'PLAINTEXT'). – John Chase May 24 '19 at 15:36