Case insensitive operations

[mdima]

Hello,
is there any way to perform a case insensitive operation on an encrypted field?

What I would like to execute is something like:
ClassName = ClassName.Where(CN => CN.Surname.ToLower().Contains(Value.ToLower()));

or even better:
ClassName = ClassName.Where(CN => CN.Surname.Contains(Value, StringComparison.OrdinalIgnoreCase));

or finally:
ClassName = ClassName.Where(CN => CN.Surname.IndexOf(Value, StringComparison.OrdinalIgnoreCase) >= 0);

At the moment I am converting everything to list, then performing the where on "client side".

Thanks,
Michele

[harrison314]

No, it is not possible, because encrypted fields can only be searched for an exact match. (Otherwise the encryption would be pointless. On the database side, all encrypted fields are stored as binary data.)

It is only going to make a case insenitive match so that the lowercase value is stored in the encrypted column.

[mdima]

Ok, clear. Need to understand how to combine this with a table with potentially million of records.

Thank you for your answer.

[harrison314]

Like this. If it is to be searched (substring, compare numbers,...) by column or indexed, it cannot be encrypted in the database.

If the column is encrypted with the option EncryptionMode.Deterministic, then it is possible to search it for an exact match. But that has its problems, for example, it is possible to statistically estimate what the individual values ​​mean. In this mode, I recommend encrypting unique data (eg. social security number).

If the column is encrypted with the option EncryptionMode.Randomized, it is not possible to search against that column. But the data stored in this way is resistant to statistical attacks.

[mdima]

The column is encrypted with the option EncryptionMode.Deterministic, the point is that I have two requirements:

1. Encrypt the personal data (name and surname);
2. Implement a user friendly search for name and surname... which means "contains with case insensitive".

I totally understand that this two requirements are not coherent, at least with the solutions available right now.

The point is that we have a lot of data in that table, at the moment the only way is to retrieve all the information from the DB (translate the entities to a list of entities) then search on the list, in stead of let SQLServer search for the records and return only the records that match.