Encrypt column data with LINQ

Question

I was wondering if there is easy solution to this or I'm stuck with following:

When updating DB:

dti.Pass = Crypter.Encrypt(dti.Pass);
_db.SubmitChanges();

When selecting from DB:

Data.DbTableItem dti = _db.Single(a=>a.Id == id);
dti.Pass = Crypter.Decrypt(dti.Pass);

Meaning - I am not really into writing repetitive code and this seems like logical thing to be supported by LINQ; so I'm wondering if it is.

Answer 1

You could add a partial class with a property encapsulating this logic like:

public partial class DbTableItem
{
  public String UnencryptedPass
  {
    get
    {
       return  Crypter.Decrypt(this.Pass);
    }

    set
    {
       this.Pass = Crypter.Encrypt(value)
    }
  }
}

Hope it helps : )

Answer 2

You could define a fake Password property which encapsulates your password logic and an original password field (which is mapped to the database - PasswordInternal in my example) should be e.g. internal.

public partial class YourEntity
{
   public string Password
   {
        get
        {
            return Crypter.Decrypt(this.PasswordInternal)
        }
        set
        {
            this.PasswordInternal = Crypter.Encrypt(value)
        }
   }
}

AFAIK, there is no built-in functionality you're looking for.

Answer 3

You should use SQL Server cryptographic functions, ENCRYPTBYKEY and DECRYPTBYKEY. Even better still, use Transparent Database Encryption. Right now you encrypt and decrypt the password with some key stored who know where. Databases have this nasty habit of moving around and being restored on completely new machines in case of disaster recovery or as part of various high availability scenarios, and you'll discover that storing the encrypted data in the database and the encryption key in the system key store (or worse, in the app) has left you with a bunch of 'completely secure' data, impossible to decrypt because you lost the key.