Always Encrypted, LINQ, and Where Contains

Question

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.

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