Entity Framework with Sql Server Column Level Encryption

Question

I have a requirement to encrypt a number of database columns (in Sql Server 2012). It has been decided that we should use column level encryption (implemented in sql server). On the application side i will be building a web api on top of some complex domain models. I really want to utilize Entity Framework's code first approach, to maintain a clean domain model). Does anyone have a workable solution here that does not involve resorting back to stored procedures? Ideally I would like to somehow manipulate the sql generated by entity framework to wrap certain fields to do the sql encryption / decryption functions.

Ideally , something like:

modelBuilder.Entity<MyTable>().ToTable("Table1").Property(p => p.SensativeData).encrypt("keyName",authenticatorFunc);

Answer 1

In SQL Server 2012, column level encryption can be done mainly in two ways ie,

1. Defining Custom Encryption function in Entity framework. this blog
2. SQL Cell Level Encryption implementation done in entity framework in dbcontext Class (execute open symmetric key code here) using this blog and using stored procedure (which contain decryption code for specified field in tables ) retrieve result sets.

In SQL server 2016 there is new feature ie, Always encrypted and has its implementation in entity framework here.