Multiple databases with slightly changing models. How do I allow Entity Framework to remove columns at run-time?

Question

This is a followup on "Multiple databases with slightly changing models. How do I allow EF to work with different database structures at run-time?" question:

I am working with EF6, MSSQL, Oracle, .NET4.5 on a system that is used globally across company (many departments) to query different databases that belong to our department, that have mostly same EF model, some databases are Oracle and some are Microsoft SQL, some are development or uat, some are logs.

I am using different EF models for Oracle and for MSSQL databases.

One requirement is to switch between databases at run time, and this is easy,

public AggregatorEntities(string connectionString) : base(connectionString) { }

however it does have side effects - many databases (dev, uat, dr, logs,...) are out of sync from what Live is (model is generated from Live), which results in errors when querying those databases.

Management knows about situation and they are ok for devs that work on some specific database to do changes to global querying system that would allow testers and uat to query the data. However they want changes they have to do to take minimum time to do this - as it is additional cost to each project that involves database changes. I will basically need to build a 'can handle all' resilient system, that when one changes database in EF will do something to accommodate to specific database.

There are different failure scenarios:
1. Name of column on table is the same but Type is different in entity
2. No column on table but there is one on entity in EF
3. Additional columns on table that are not on EF
4. Additional tables in database that are not in EF model
5. No table in database but there is entity in EF model.

this is case of removing columns at runtime that are in EF model but not in database (point 3 above).

@Desperado but mechanisms will be different for 5 possible situations, I am planning on moving part of original here and then deleting it, do you agree? — Matas Vaitkevicius, Jun 22 '16 at 10:38
@Desperado I think just leave it as is to separate the different situations — Anonymous Duck, Jun 22 '16 at 10:40

score 1 · Accepted Answer · edited May 23 '17 at 12:08

Ok so I built this nasty thing that allows to remove columns from tables at runtime.

1.You will need to add constructor overload to your DbContext (*.edmx->*.Context.tt ->*.Context.cs) like:

public partial class EcomEntities : DbContext
{
    public EcomEntities(DbConnection connection)
        : base(connection, true)
    {
    }

    public EcomEntities(string connectionString)
        : base(connectionString)
    {
    }

2.You will need database connection changer (I am hardcoding parameters explicitly here for clarity, one normally would get them from SystemToDatabaseMapping). ColumnsToRemove has table and column names that need to be removed, EF connectionString is self explanatory.

public void ChangeConnection(SystemToDatabaseMapping systemToDatabaseMapping)
{  
    if (systemToDatabaseMapping.ColumnsToRemove != null)
        {
            var entityConnection = EntityConnectionExtensions.Create(
                new List<ColumnsToRemove> { new ColumnsToRemove("QUOTE_HOUSE", "UPRN"), new ColumnsToRemove("QUOTE_HOUSE", "INSIGHT_DATA") },
                systemToDatabaseMapping.ConnectionString);
            this.Ecom = new EcomEntities(entityConnection);
        }
        else
        {
            this.Ecom = new EcomEntities(systemToDatabaseMapping.ConnectionString);
        }
 ....
}

And then the guy that actually does nasty things like removes nodes from entity mapping xmls, before feeding them to MetadataWorkspace

using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Mapping;
using System.Data.Entity.Core.Metadata.Edm;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Linq;

public static class EntityConnectionExtensions
{
    public static IEnumerable<XElement> ElementsAnyNS<T>(this IEnumerable<T> source, string localName)
        where T : XContainer
    {
        return source.Elements().Where(e => e.Name.LocalName == localName);
    }

    public static IEnumerable<XElement> ElementsAnyNS(this XContainer source, string localName)
    {
        return source.Elements().Where(e => e.Name.LocalName == localName);
    }

    private static void RemoveNodes(XElement element, List<ColumnsToRemove> tableAndColumn)
    {
        if (element.Attribute("Name") != null && tableAndColumn.Any(oo => oo.Table == element.Attribute("Name").Value) ||
            element.Attribute("StoreEntitySet") != null && tableAndColumn.Any(oo => oo.Table == element.Attribute("StoreEntitySet").Value))
        {
            var matchingSelectParts = tableAndColumn.Where(oo => element.Value.Contains(string.Format("\"{0}\".\"{1}\" AS \"{1}\"", oo.Table, oo.Column))).ToList();
            if (matchingSelectParts.Any())
            {
                foreach (var matchingSelectPart in matchingSelectParts)
                {
                    var definingQuery = element.ElementsAnyNS("DefiningQuery").Single();
                    definingQuery.Value = definingQuery.Value.Replace(string.Format(", \n\"{0}\".\"{1}\" AS \"{1}\"", matchingSelectPart.Table, matchingSelectPart.Column), "");
                }
            }
            else
            {
                var nodes = element.Nodes()
                    .Where(o =>
                        o is XElement
                        && ((XElement) o).Attribute("Name") != null
                        && tableAndColumn.Any(oo => ((XElement) o).Attribute("Name").Value == oo.Column));
                foreach (var node in nodes.ToList())
                {
                    node.Remove();
                }
            }
        }
    }

    public static EntityConnection Create(List<ColumnsToRemove> tablesAndColumns, string connString)
    {
        var modelNameRegex = new Regex(@".*metadata=res:\/\/\*\/([a-zA-Z.]*).csdl|.*");
        var model = modelNameRegex.Matches(connString).Cast<Match>().SelectMany(o => o.Groups.Cast<Group>().Skip(1).Where(oo => oo.Value != "")).Select(o => o.Value).First();

        var conceptualReader = XmlReader.Create(Assembly.GetExecutingAssembly().GetManifestResourceStream(model + ".csdl"));
        var mappingReader = XmlReader.Create(Assembly.GetExecutingAssembly().GetManifestResourceStream(model + ".msl"));
        var storageReader = XmlReader.Create(Assembly.GetExecutingAssembly().GetManifestResourceStream(model + ".ssdl"));

        var conceptualXml = XElement.Load(conceptualReader);
        var mappingXml = XElement.Load(mappingReader);
        var storageXml = XElement.Load(storageReader);

        foreach (var entitySet in new[] {storageXml, conceptualXml}.SelectMany(xml => xml.Elements()))
        {
            if (entitySet.Attribute("Name").Value == "ModelStoreContainer")
            {
                foreach (var entityContainerEntitySet in entitySet.Elements())
                {
                    RemoveNodes(entityContainerEntitySet, tablesAndColumns);
                }
            }

            RemoveNodes(entitySet, tablesAndColumns);
        }

        foreach (var entitySet in mappingXml.Elements().ElementAt(0).Elements())
        {
            if (entitySet.Name.LocalName == "EntitySetMapping")
            {
                foreach (var entityContainerEntitySet in entitySet.Elements().First().Elements())
                {
                    RemoveNodes(entityContainerEntitySet, tablesAndColumns);
                }
            }

            RemoveNodes(entitySet, tablesAndColumns);
        }

        var storageCollection = new StoreItemCollection(new [] {storageXml.CreateReader()});
        var conceptualCollection = new EdmItemCollection(new[] { conceptualXml.CreateReader() });
        var mappingCollection = new StorageMappingItemCollection(conceptualCollection, storageCollection, new[] {mappingXml.CreateReader()});

        var workspace = new MetadataWorkspace();

        workspace.RegisterItemCollection(conceptualCollection);
        workspace.RegisterItemCollection(storageCollection);
        workspace.RegisterItemCollection(mappingCollection);

        var connectionData = new EntityConnectionStringBuilder(connString);
        var connection = DbProviderFactories
            .GetFactory(connectionData.Provider)
            .CreateConnection();
        connection.ConnectionString = connectionData.ProviderConnectionString;

        return new EntityConnection(workspace, connection);
    }
}

public class ColumnsToRemove
{
    public ColumnsToRemove(string table, string column)
    {
        Table = table;
        Column = column;
    }

    public string Table { get; set; }
    public string Column { get; set; }
}

public class SystemToDatabaseMapping
{
    public string ConnectionString { get; set; }
    public List<ColumnsToRemove> ColumnsToRemove  { get; set; }
}

Hope this saves you some time.

If I run the custom tool to update my context, is this not affected? — Anonymous Duck, Jun 22 '16 at 10:42
@Desperado what sort of tool? If it affects connection or `MetadataWorkspace` then it might blow up. Just try it on local db to see if it works. In my case it's only reads so I am not too worried. — Matas Vaitkevicius, Jun 22 '16 at 10:43
If I'm using EF i normally update my edmx automatically by executing update model from database command. Just saying :) — Anonymous Duck, Jun 22 '16 at 10:49
@Desperado It should work fine with database-first, you might have to update `Entities : DbContext` constructor when you fully regenerate model — Matas Vaitkevicius, Jun 22 '16 at 10:50

Multiple databases with slightly changing models. How do I allow Entity Framework to remove columns at run-time?

1 Answers1

Linked