Save same changes to multiple databases with Entity Framework

Question

I have 3 Oracle databases; production, test, development. For the most part, they are all identical. In my application, I would like the changes to be applied to multiple databases. For example:

    using (var context = new Context())
    {
        context.People.Add(new Person { name = "sean" });
        context.SaveChanges();
    }

I then tried to override the SaveChanges method and save to multiple databases by doing this:

    public void SaveChanges(int auditPersonNumber)
    {
        OracleCredentials.Default.Server = "VDev";
        base.SaveChanges();

        OracleCredentials.Default.Server = "VTest";
        base.SaveChanges();

        OracleCredentials.Default.Server = "VProd";
        base.SaveChanges();
    }

This didn't work but should explain what I am trying to achieve.

Answer 1

I was able to figure out a solution thanks to the help of Sangman.

public class Context : Shared.Data.Context
{
    new public void SaveChanges(int auditPersonNumber)
    {
        var errors = string.Empty;
        var testConnectionString = "ConnectionString";
        var developmentConnectionString = "ConnectionString";

        //Save to test database
        if (SecurityMaintenanceUser.ApplyToTest)
            errors = ApplyToDatabase(testConnectionString, auditPersonNumber, "Test");

        if (!string.IsNullOrWhiteSpace(errors))
            errors += "\n\n";

        //Save to development database
        if (SecurityMaintenanceUser.ApplyToDevelopment)
            errors += ApplyToDatabase(developmentConnectionString, auditPersonNumber, "Development");

        if (!string.IsNullOrWhiteSpace(errors))
            MessageBox.Show(errors, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

        //Save to production database
        base.SaveChanges(auditPersonNumber);
    }

    private string ApplyToDatabase(string connectionString, int auditPersonNumber, string server)
    {
        try
        {
            using (var context = new Context(connectionString))
            {
                context.Configuration.ValidateOnSaveEnabled = false;

                foreach (var entry in ChangeTracker.Entries())
                {
                    var dataSet = context.Set(entry.Entity.GetType());

                    if (entry.State == EntityState.Added)
                    {
                        dataSet.Add(entry.Entity);
                    }
                    else if (entry.State == EntityState.Deleted)
                    {
                        var contextEntity = dataSet.Find(GetPrimaryKeyValues(entry));
                        context.DeleteEntity(contextEntity, auditPersonNumber);
                    }
                    else if (entry.State == EntityState.Modified)
                    {
                        var contextEntity = dataSet.Find(GetPrimaryKeyValues(entry));
                        context.Entry(CopyProperties(entry.Entity, contextEntity)).State = EntityState.Modified;
                    }
                }

                context.SaveChanges(auditPersonNumber);
                return string.Empty;
            }
        }
        catch (Exception e)
        {
            return $"Failed to apply database changes to {server}.\n{e.GetFullMessage()}";
        }
    }

    private object CopyProperties(object source, object destination)
    {
        if (source == null || destination == null)
            throw new Exception("Source or/and Destination Objects are null");

        var typeDest = destination.GetType();
        var typeSrc = source.GetType();

        foreach (var srcProp in typeSrc.GetProperties())
        {
            if (srcProp.Name == "Type" || srcProp.Name == "AuthenticationLog")
                continue;

            //This blocks any complex objects attached to the entity, will need to be changed for your application
            if (srcProp.PropertyType.FullName.Contains("Library.Shared"))
                continue;

            if (!srcProp.CanRead)
                continue;

            var targetProperty = typeDest.GetProperty(srcProp.Name);

            if (targetProperty == null)
                continue;

            if (!targetProperty.CanWrite)
                continue;

            if (targetProperty.GetSetMethod(true)?.IsPrivate == true)
                continue;

            if ((targetProperty.GetSetMethod().Attributes & MethodAttributes.Static) != 0)
                continue;

            if (!targetProperty.PropertyType.IsAssignableFrom(srcProp.PropertyType))
                continue;

            targetProperty.SetValue(destination, srcProp.GetValue(source, null), null);
        }

        return destination;
    }

    private object GetPrimaryKeyValues(DbEntityEntry entry)
    {
        var objectStateEntry = ((IObjectContextAdapter)this).ObjectContext.ObjectStateManager.GetObjectStateEntry(entry.Entity);
        return objectStateEntry.EntityKey.EntityKeyValues[0].Value;
    }

    public static string GetFullMessage(this Exception ex)
    {
        return ex.InnerException == null ? ex.Message : $"{ex.Message}\n{ex.InnerException.GetFullMessage()}";
    }

    public static string Replace(this string source, string oldString, string newString, StringComparison comp)
    {
        int index = source.IndexOf(oldString, comp);

        if (index >= 0)
        {
            source = source.Remove(index, oldString.Length);
            source = source.Insert(index, newString);
        }

        if (source.IndexOf(oldString, comp) != -1)
            source = Replace(source, oldString, newString, comp);

        return source;
    }
}

Answer 2

I haven't yet used EntityFramework against an Oracle database, but it should be similar to connecting against SQL Server in that the database name is specified via a ConnectionString. Your project should have a config file (web.config, app.config, or if it's a .NET Core application it could be in appsettings.json) with that ConnectionString in it.

For example:

<add name="YourConnectionString" providerName="YourOracleProviderName" connectionString="User Id=test;Password=testpassword;Data Source=eftest" />

The DbContext base constructor accepts a string argument that specifies which ConnectionString it should use, and thus which database to connect to. If you look into your context class, the default constructor should call the base constructor with that argument.

public YourDbContext() : base("YourConnectionString") {}

In order to save to multiple databases you will need to work against different instances of DbContext each with a different ConnectionString argument. So, your config will need to list a few different connection strings for every Db and you'll probably want your DbContext class to allow the argument in its constructor as well.

Perhaps the SaveChanges method implementation could instantiate the other DbContexts you'd need to use:

    public void SaveChanges(int auditPersonNumber)
    {
        using (var context = new Context("OtherConnectionString1"))
        {
            // apply same changes
            context.SaveChanges();
        }

        using (var context = new Context("OtherConnectionString2"))
        {
            // apply same changes
            context.SaveChanges();
        }

        base.SaveChanges();
    }

As for the applying the same changes, I would expect you can read them out from the DbContext ChangeTracker. There's an explanation about that using EF Core here but in earlier versions it's similar: http://www.entityframeworktutorial.net/efcore/changetracker-in-ef-core.aspx

Also keep in mind that the SaveChanges call to OtherConnectionString1 could succeed while others could fail, so the data might be inconsistent in your different databases. You may have to look into using transactions across multiple databases but I haven't done this yet myself.