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;
        }

    }
}