How to update the property value (Database field) of T Entity (Database Table) from the DbContext? (save decrypted data back to the DbContext)

Question

How to iterate through DbContext and decrypt certain fields

I am using this StackOverflow answer as a guide: Database Table Export to CSV using EntityFramework DbContext .NET 6

We have very a legacy database (20+ years old) that used software encryption on certain columns. We are decrypting the encrypted columns so we can move to a more modern and efficient way of protecting the data.

I need to iterate through this old MS SQL Server database using EF Core and decrypt the columns with the software decryption. I have the code working to where I can decrypt the values, but I have not been able to figure out how to save them back to the DbContext (i.e. the database).

How do I set the `property value' (data field) of the Entity (data table) so that I may update the DbContext and save the decrypted changes to the database?

We have the GetPropertyValue() method in the TableDecrypt class, but have not been able to discover how to create the SetPropertyValue() method.

private static string GetPropertyValue(IProperty property, object record)
{
    var value = property.GetGetter().GetClrValue(record);
    if (value == null) return "";
    var strValue = value.ToString() ?? "";

    return strValue;
}

I have written the comment // The part I can't figure out next to the issue in the TableDecrypt class below.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using System.Linq;
using System.Reflection;

namespace DatabaseTableExport
{
    public static class TableDecryptUtils
    {
        public static void DecryptTable<T>(DbContext context, IEntityType entityType, ITableDecrypt tableDecrypt) where T : class
        {
            // Export table to csv and get results
            var result = tableDecrypt.DecryptTable(context, context.Set<T>(), entityType);

            // TODO: report result here
        }

        private static readonly MethodInfo ProcessDecryption = typeof(TableDecryptUtils).GetMethod(nameof(DecryptTable))!;

        public static void Execute(DbContext context, ITableDecrypt tableDecrypt)
        {
            var entityTypes = context.Model.GetEntityTypes()
                .Where(et => !et.IsOwned())
                .ToList();

            foreach (var et in entityTypes)
            {
                // create and invoke generic method to get table data database and export to csv
                ProcessDecryption.MakeGenericMethod(et.ClrType).Invoke(null, new object[] { context, et, tableDecrypt });
            }
        }
    }

}

using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore;
using System.Linq;


namespace DatabaseTableExport
{
    public interface ITableDecrypt
    {        
        bool DecryptTable<T>(DbContext dbContext, IQueryable<T> table, IEntityType entityType) where T : class;
    }
}

using DatabaseTableExport.Services.Cryptography;
using DatabaseTableExport.Services.Cryptography.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using System.Collections.Generic;
using System.Linq;

namespace DatabaseTableExport
{
    public class TableDecrypt : ITableDecrypt
    {
        // set RedactPii to true to redact Personally Identifiable Information (PII) from the export
        private bool RedactPii { get; set; }                
        private readonly string _encryptionKey;
        private readonly List<EncryptionHandler> _encryptionHandlers;

        public TableDecrypt(string encryptionKey, List<EncryptionHandler> encryptionHandlers, bool redactPii)
        {
            RedactPii = redactPii;                       
            _encryptionKey = encryptionKey;
            _encryptionHandlers = encryptionHandlers;
        }

        /// <summary>
        /// Returns the value of the given property as a string.
        /// </summary>
        /// <param name="property"></param>
        /// <param name="record"></param>
        /// <returns>String</returns>
        private static string GetPropertyValue(IProperty property, object record)
        {
            var value = property.GetGetter().GetClrValue(record);
            if (value == null) return "";
            var strValue = value.ToString() ?? "";

            return strValue;
        }


        /// <summary>
        /// Sets the value of the given property with the given value.
        /// </summary>
        /// <param name="property"></param>
        /// <param name="record"></param>        
        private static void SetPropertyValue(IProperty property, object record, string value)
        {
            // The part I can't figure out          
        }

        /// <summary>
        /// Decrypts set of records (table) from database context entity.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbContext"></param>
        /// <param name="table"></param>
        /// <param name="entityType"></param>
        /// <returns>Returns true if successful, false if not successful.</returns>
        public bool DecryptTable<T>(DbContext dbContext, IQueryable<T> table, IEntityType entityType) where T : class
        {
            var tableRowsAsCsv = new List<string>();
            var properties = entityType.GetProperties().ToList();
            var tableName = entityType.Name.Split('.').Last();
            var encryption = new AesEncryption(_encryptionKey);

            try
            {
                // get Column Names from entity to compare with encryption handlers
                var columnNames = properties.Select(p => p.GetColumnName()).ToArray();                
                var columnIndex = 0;

                foreach (var record in table)
                {                   
                    foreach (var property in properties)
                    {
                        var columnName = columnNames[columnIndex];
                        try
                        {                            
                            // Decrypt, redact, or skip column value based on encryption handler 
                            // If table has encryption handler, decrypt column value based on handler action, else continue
                            if (_encryptionHandlers.Any(handler => handler.TableName == tableName && handler.ColumnName == columnName))
                            {
                                var handler = _encryptionHandlers.FirstOrDefault(h => h.TableName == tableName && h.ColumnName == columnName);
                                
                                if (property is not null)
                                {
                                    // Skip column value if handler action is Skip then continue
                                    if (handler.Action == "Skip")
                                    {
                                        columnIndex++;
                                        continue;
                                    }

                                    // Handle Decrypt or DecryptOrRedact
                                    if (handler.Action == "Decrypt" || handler.Action == "DecryptOrRedact")
                                    {
                                        // If RedactPii is true, redact column value instead of decrypting, else decrypt
                                        if (RedactPii)
                                        {                                            
                                            SetPropertyValue(property, record,"REDACTED");                                            
                                            continue;
                                        }
                                        else
                                        {
                                            SetPropertyValue(property, record, encryption.Decrypt(GetPropertyValue(property, record))); 
                                            continue;
                                        }
                                    }
                                } 
                                else // if null, continue
                                {
                                    columnIndex++;
                                    continue;
                                }
                            }        
                        }
                        catch 
                        {
                            throw;
                        }                        
                        columnIndex++;
                    }                                             
                    columnIndex = 0;
                }                
                dbContext.SaveChanges();                
            }
            catch
            {
                return false;                             
            }
            return true;
        }

    }
}

Ustulation

Well, for one, you shouldn't use AsNoTracking() if you intend to update values. Change tracking is what EF uses for performing updates. The issue you will run into is trying to go over ever row in a table across a single SaveChanges transaction. Bulk operations like this are not suited to being performed with EF. You should consider writing your decryption/redact operation in the database itself as a stored procedure or to run as a proper workflow using something like SSIS that your application kicks off and can check on progress through a reporting table or other communication channel. – Thickhead

Thanks, we are not worried about performance. We need to do it this way. It's on old legacy database (20+ years old) that used software encryption so we are decrypting the encrypted columns so we can move to a more modern and efficient way of encrypting things. – Ustulation

So, it is one-time operation? How columns can be decrypted In SQL? – Healy

@SvyatoslavDanyliv We can't use SQL because the encryption is software based. It's the way encryption was done when this software was developed. – Ustulation

In that case remove the .AsNoTracking() though I would look to process rows in smaller batches as code like this would essentially try loading the entire table into memory. Extracting the property definitions by reflection like that you will want to use property.SetValue(record, property.GetValue(record) + "REDACTED"), null); and the same for your decrypted string. – Thickhead

@StevePy Thanks, I already removed the .AsNoTracking. I also already tried property.SetValue(). It does not exist for me. – Ustulation

EF isn't a database driver. What you try to do is working at the wrong level. SQL Server itself already offers encryption over the network, on disk and on the fly encryption, all of which are controlled at the driver or database level. If you want your data to remain always encrypted, use SQL Server's Always Encrypted feature. In the connection string add Column Encryption Setting=enabled – Veats

@PanagiotisKanavos Yes, I agree with you, but as mentioned above, this is legacy. We need to be able to do this so we can move to a better method like you have describe. – Ustulation

@SvyatoslavDanyliv and StevePy Thank you for your comments, they helped me realize an error in my code and in my question. I have updated the question and code, but I am still basically having the same problem, just in a different spot in the code. – Ustulation

The fact remains that decryption shouldn't be performed at the business level. You can use interceptors to encrypt/decrypt fields. – Veats

@PanagiotisKanavos thank you for your comment. If you have a solution to this issue, please provide it in the form of an answer. – Ustulation

Answer 1

Once you have tracked entity, it's much easier and convenient to use change tracking API services than model API services. i.e.

var entry = dbContext.Entry(entity);

where entity is either T or just object. Then you can use either CurrentValues / OriginalValues to get/set property values, e.g.

var value = entry.CurrentValues[property]; // get
entry.CurrentValues[property] = value; // set

or obtain PropertyEntry via Property method and use CurrentValue / OriginalValue for the same purpose, e.g.

var propertyEntry = entry.Property(property);
var value = propertyEntry.CurrentValue; // get
propertyEntry.CurrentValue = value; // set

For instance, the method in question can be implemented as follows (added few optimizations as a bonus):

public bool DecryptTable<T>(DbContext dbContext, IQueryable<T> table, IEntityType entityType) where T : class
{
    var tableName = entityType.GetTableName();
    if (tableName is null) return false;

    var decryptInfo = (
        from property in entityType.GetProperties()
            .Where(property => property.ClrType == typeof(string))
        join handler in _encryptionHandlers
            .Where(handler => handler.TableName == tableName
                && handler.Action is "Decrypt" or "DecryptOrRedact")
        on property.GetColumnName() equals handler.ColumnName
        select (property, redactPii: RedactPii/* && handler.Action is "DecryptOrRedact"*/)
    ).ToList();

    if (!decryptInfo.Any()) return false;

    var encryption = new AesEncryption(_encryptionKey);

    try
    {
        foreach (var entity in table.AsTracking())
        {
            var entry = dbContext.Entry(entity);
            foreach ((var property, var redactPii) in decryptInfo)
            {
                var propertyEntry = entry.Property(property);
                // Get property value
                var value = (string)propertyEntry.CurrentValue;
                if (string.IsNullOrEmpty(value)) continue;
                // Redact or decrypt
                if (redactPii)
                {
                    value += $"REDACTED";
                }
                else
                {
                    value = encryption.Decrypt(value);
                }
                // Set property value
                propertyEntry.CurrentValue = value;
            }
        }

        dbContext.SaveChanges();
        return true;
    }
    catch
    {
        return false;
    }
    finally
    {
        dbContext.ChangeTracker.Clear();
    }
}

Tuberosity

Great answer. This response provides the correct answer to the question. If anyone tries to reuse the exact code, please note there are some bugs that will need to be worked out. – Ustulation

Answer 2

There is no need to create a method. You are using reflection to go through the columns. There are likely some control flow logic bugs in your code, such as the column index does not look like it would be incremented when it actually processes a value. I recommend using "fail fast" guard conditions rather than nesting conditions with if/else. The following should do what you are looking for:

foreach (var record in table)
{   
    int columnIndex = -1;             
    foreach (var property in properties)
    {
        columnIndex++;
        var columnName = columnNames[columnIndex];
        var handler = _encryptionHandlers.FirstOrDefault(h => h.TableName == tableName && h.ColumnName == columnName);

        if (handler is null || (handler.Action != "Decrypt" && handler.Action != "DecryptOrRedact"))
            continue;

        if (RedactPii)
            property.SetValue(record, property.GetValue(record) + "REDACTED");                                            
        else
        {
            string decrypted = encryption.Decrypt(property.GetValue(record));
            property.SetValue(record, decrypted);
        }
    }   
}  

I have condensed the conditional logic and streamlined the column index incrementation so it's done only in one place to avoid issues where certain conditional flows risk not incrementing it. The logic around Skip vs. anything other than decrypt/redact got condensed down, though it is a bit of a code smell that you have a flag for Redact and a converter status distinction between decrypt vs. decryptOrRedact. There is no point having a catch block if it is just going to re-throw.

The core of the issue you had remaining is just using the property.SetValue() when you want to update the value of the property in the current row you are iterating over. "property" in this case is the PropertyInfo representing a property in the target class. To Set the value of a record, you call property.SetValue(record) which tells the PropertyInfo which row's property instance will be updated.

The other important detail here is that when you pass a DbSet in as IQueryable that you do not use AsNoTracking as we want the change tracking to be watching for changes on the entity properties. It looks like you had removed the AsNoTracking from the original question so that should be good to go.

Thickhead