多个数据库，其模型稍有变化.如何允许Entity Framework在运行时删除列? [英] Multiple databases with slightly changing models. How do I allow Entity Framework to remove columns at run-time?
问题描述
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:
我正在使用EF6，MSSQL，Oracle，.NET4.5的系统在整个公司(许多部门)中全局使用，以查询属于我们部门的不同数据库，这些数据库大多数具有相同的EF模型，有些数据库是Oracle，有些数据库是Microsoft SQL，有些是开发或uat，有些是日志.
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.
我为Oracle和MSSQL数据库使用了不同的EF模型.
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)
{
}
public AggregatorEntities(string connectionString)
: base(connectionString)
{
}
但是它确实有副作用-许多数据库(dev，uat，dr，log等)与Live是不同步的(模型是通过Live生成的)，这会在查询这些数据库时导致错误.
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.
管理层了解情况，对于在某些特定数据库上工作的开发人员可以更改全局查询系统(允许测试人员和uat查询数据)而言，它们是可以的.但是，他们希望所做的更改需要花费最少的时间来完成，因为这是涉及数据库更改的每个项目的额外费用.我基本上将需要构建一个可以处理所有问题"的弹性系统，以便在EF中更改数据库时可以做一些事情来适应特定的数据库.
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.
有不同的故障情况:
1.表上的列名称相同，但实体中的类型不同
2.表上没有列，但EF
中的实体上有一列
3.表格上不在EF
上的其他列
4.数据库中不属于EF模型的其他表
5.数据库中没有表，但EF模型中有实体.
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.
这是在运行时删除EF模型但不在数据库中的列的情况(上面的第3点).
this is case of removing columns at runtime that are in EF model but not in database (point 3 above).
推荐答案
好，所以我构建了一个讨厌的东西，允许在运行时从表中删除列.
Ok so I built this nasty thing that allows to remove columns from tables at runtime.
1.您将需要向DbContext(*.edmx-> *.Context.tt-> *.Context.cs)中添加构造函数重载，如:
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.您将需要数据库连接更改程序(为了清楚起见，我在此处明确地对参数进行硬编码，通常可以从SystemToDatabaseMapping中获取它们). ColumnsToRemove具有需要删除的表名和列名，EF 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);
}
....
}
然后实际执行令人讨厌的事情的人将节点从实体映射xml中删除，然后再将其提供给MetadataWorkspace
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.
这篇关于多个数据库，其模型稍有变化.如何允许Entity Framework在运行时删除列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
