LINQ to SQL decrypt value before populating object

Question

There is a field in one of my tables that is encrypted using

ENCRYPTBYPASSPHRASE(<passphrase>,<value>)

When the value is placed into the object the field is still encrypted so I can't do anything with it. I can't create a view or stored proc or any other item that decrypts the fields on the database because then it defeats the purpose of encrypting the fields. Is there a way of having the frame work run something like

DECRYPTBYPASSPHRASE(<passphrase>, <columnName>)

before assigning the value to the object?

Right now I'm Getting the data then calling ExecuteQuery to decrypt the value. and assigning that new value over the encrypted value on my data model class. It works but I was just wondering if it could be done automatically through some options I don't know about. I've tried searching but have not found anything.

Answer 1

I'm assuming that you are using linq-to-sql and that the table you are pulling from is structured like so:

+--------+---------------+
| UserId |   Passphrase  |
+--------+---------------+
|      1 | laskdfmlsadkf |
+--------+---------------+

With this information, you can apply the decrypt method during your select.

var password = "password";
var userId = 1;

var result = usertable.Where(c => c.UserId == userId).ToList()
.Select(t => new 
{
    Passphrase = DECRYPTBYPASSPHRASE(t.Passphrase)
}).First()

bool areSame = (password == result.Passphrase);