Encrypt / Decrypt data at db context

Override savechanges method and object materialized methods to automatically encrypt/decrypt data without manually doing it.
Here, the custom attribute [Encrypted] is used to mark a property as encrypted or secured data.
Use your way of security for encryption and decryption.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Objects;
using System.Linq;
using System.Threading.Tasks;
using System.Web;

public class BaseDbContext : DbContext
    {
        public BaseDbContext() : base("mydbcontext")
        {
            ((IObjectContextAdapter)this).ObjectContext.ObjectMaterialized += new ObjectMaterializedEventHandler(ObjectMaterialized);
        }

        public override int SaveChanges()
        {
            var contextAdapter = ((IObjectContextAdapter)this);

            contextAdapter.ObjectContext.DetectChanges();

            var pendingEntities = contextAdapter.ObjectContext.ObjectStateManager
                .GetObjectStateEntries(EntityState.Added | EntityState.Modified)
                .Where(en => !en.IsRelationship).ToList();

            foreach (var entry in pendingEntities) //Encrypt all pending changes
                EncryptEntity(entry.Entity);

            int result = base.SaveChanges();

            foreach (var entry in pendingEntities) //Decrypt updated entities for continued use
                DecryptEntity(entry.Entity);

            return result;
        }

        void ObjectMaterialized(object sender, ObjectMaterializedEventArgs e)
        {
            DecryptEntity(e.Entity);
        }

        private void EncryptEntity(object entity)
        {
            //Get all the properties that are encryptable and encrypt them
            var encryptedProperties = entity.GetType().GetProperties()
                .Where(p => p.GetCustomAttributes(typeof(Encrypted), true).Any(a => p.PropertyType == typeof(String)));
            foreach (var property in encryptedProperties)
            {
                string value = property.GetValue(entity) as string;
                if (!String.IsNullOrEmpty(value))
                {
                    property.SetValue(entity, SimpleEncrypt(value));
                }
            }
        }

        private void DecryptEntity(object entity)
        {
            //Get all the properties that are encryptable and decyrpt them
            var encryptedProperties = entity.GetType().GetProperties()
                .Where(p => p.GetCustomAttributes(typeof(Encrypted), true).Any(a => p.PropertyType == typeof(String)));

            foreach (var property in encryptedProperties)
            {
                string encryptedValue = property.GetValue(entity) as string;
                if (!String.IsNullOrEmpty(encryptedValue))
                {
                    Entry(entity).Property(property.Name).OriginalValue = SimpleDecrypt(encryptedValue);
                    Entry(entity).Property(property.Name).IsModified = false;
                }
            }
        }

        public string SimpleEncrypt(string value)
        {
            // your encryption logic
            return value;
        }

        public string SimpleDecrypt(string value)
        {
            // your decryption logic
            return value;

        }
    }

    public class Encrypted : Attribute { }

jQuery Datatables server-side pagination in ASP.NET MVC with Entity Framework

In order to capture datatable events we need to create a class as follows;

   /// <summary>  
   /// Class that encapsulates most common parameters sent by DataTables plugin  
   /// </summary>  
   public class jQueryDataTableParams  
   {  
     /// <summary>  
     /// Request sequence number sent by DataTable,  
     /// same value must be returned in response  
     /// </summary>      
     public string sEcho { get; set; }  
     /// <summary>  
     /// Text used for filtering  
     /// </summary>  
     public string sSearch { get; set; }  
     /// <summary>  
     /// Number of records that should be shown in table  
     /// </summary>  
     public int iDisplayLength { get; set; }  
     /// <summary>  
     /// First record that should be shown(used for paging)  
     /// </summary>  
     public int iDisplayStart { get; set; }  
     /// <summary>  
     /// Number of columns in table  
     /// </summary>  
     public int iColumns { get; set; }  
     /// <summary>  
     /// Number of columns that are used in sorting  
     /// </summary>  
     public int iSortingCols { get; set; }  
     /// <summary>  
     /// Comma separated list of column names  
     /// </summary>  
     public string sColumns { get; set; }  

Following is the method to load data for datatable. Assuming that we are going to show firstname, lastname, company and jobtitle from member table.

 public ActionResult GetDataForDatatable(jQueryDataTableParams param)  
 {  
    IQueryable<Member> memberCol = myDbContext.Member  
                      .Select(m => m.FirstName,  
                          m.LastName,  
                          m.Company,  
                          m.JobTitle)  
                      .AsQueryable();  
    int totalCount = memberCol.Count();  
    IEnumerable<Member> filteredMembers = memberCol;  
    if (!string.IsNullOrEmpty(param.sSearch))  
    {  
      filteredMembers = memberCol  
              .Where(m => m.FirstName.Contains(param.sSearch) ||  
                 m.LastName.Contains(param.sSearch) ||  
                 m.Company.Contains(param.sSearch) ||  
                 m.JobTitle.Contains(param.sSearch));  
    }  
    var sortIdx = Convert.ToInt32(Request["iSortCol_0"]);  
    Func<Member,string> orderingFunction = (m => sortIdx == 0 ? m.FirstName :  
                         sortIdx == 1 ? m.LastName :  
                         sortIdx == 2 ? m.Company :  
                         m.JobTitle);  
    var sortDirection = Request["sSortDir_0"]; // asc or desc  
    if (sortDirection == "asc")  
      filteredMembers = filteredMembers.OrderBy(orderingFunction);  
    else  
      filteredMembers = filteredMembers.OrderByDescending(orderingFunction);  
    var displayedMembers = filteredMembers  
             .Skip(param.iDisplayStart)  
             .Take(param.iDisplayLength);  
    var result = from a in displayedMembers  
           select new[] { a.FirstName, a.LastName, a.Company,   
                  a.JobTitle, a.ID.ToString() };  
    return Json(new  
       {  
         sEcho = param.sEcho,  
         iTotalRecords = totalCount,  
         iTotalDisplayRecords = filteredMembers.Count(),  
         aaData = result  
       },  
       JsonRequestBehavior.AllowGet);  
 }  

Following is how we initialize our datatable and design the HTML.

-> Reference to jQuery js, jQuery Datatable js, jQuery Datatable css

 <table id="tblMember" class="table" style="width: 100%">  
   <thead>  
     <tr>  
       <th>First Name</th>  
       <th>Last Name</th>  
       <th>Company</th>  
       <th>Job Title</th>  
       <th>Actions</th>  
     </tr>  
   </thead>  
   <tbody>  
   </tbody>  
 </table>  
 <script>  
   $(document).ready(function () {  
     // destroy existing datatable before, initiating  
     if (oTable != undefined) {  
       oTable.fnDestroy();  
     };  
     oTable = $('#tblMember').dataTable({  
       "bServerSide": true,  
       "sAjaxSource": '@Url.Action("GetDataForDatatable","Member")',  
       "bProcessing": true,  
       "aoColumns": [  
               { "sName": "FirstName" },  
               { "sName": "LastName" },  
               { "sName": "Company" },  
               { "sName": "JobTitle" },  
               {  
                 "sName": "ID",  
                 "bSearchable": false,  
                 "bSortable": false,  
                 'mRender': function (data, type, row) {  
                   return '<a href="@Url.Action("Edit", "Member")/' + row[4] + '" class="btn btn-warning btn-xs" role="button">@labels.btn_edit</a>' + '&nbsp;' +  
                       '<button onclick="confirmDelete(' + row[4] + ');" class="btn btn-danger btn-xs">@labels.btn_delete</button>';  
                 }  
               },  
       ]  
     });  
   }  
 </script>