Entity Framework–Multiple Database support

One of the limitations that EF still has is the support for multiple databases. It simply does not support that option.out of the box. Allthough on UserVoice a lot of people have voted this feature, it still doesn’t exist.

Credit where credit is due: this is not my original idea. I first downloaded the original script, used it and then i almost completely rewrote it to suit my needs and to improve certain aspects. I love Linq, so i used it a lot. The original and fantastic script can be found here

The trick is making EF believe that those objects are in the database by using synonyms. I dont know if every EF-supported database allows synonyms or not, but this is possible in SQL Server (which i am using).  

My script adds the ability to backup the edmx files first (which i find useful to prevent my model from being corrupted), replace the prefixes in the resulting model (which i had to do by hand with the other script) and finally reorder the navigation properties by alphabetical order. This last one is a must for me, because i use LINQPad a lot and the properties appear as links in the order they are declared. Another change include the ability to import the Function Imports declared (if i’m not mistaken the original script would throw an exception for the lack of an attribute in the FunctionImport element)

The following procedures must be taken for this approach to work. I know it seems a lot, but you may benefit much more from it.

• Define a database that will have all the synonyms for other database objects. This will be your entry point in EF. It can be any database
• Create synonyms for all foreign objects on your previously defined database. I find it useful to generate a script for the effect which selects a string for every table and then create those synonyms from the script results. Here it is:

	USE databasename
	select 'CREATE SYNONYM dbo.' + t.name + ' FOR ' + 'databasename.dbo.' + t.name
	FROM sys.tables t

view raw synonyms.cs hosted with ❤ by GitHub

• Create one model for each database you want to merge.


• Configure the variables in the script. You need to configure the directory of your models, the backup destination and the result file path. There still are similarities with the original script in this. Your can pass as many database paths as you like. Don’t forget to change the XNamespace’s in the begining of the script. They can vary depending on your EF version.


• Go to the method ReplacePrefixes and configure the variable “CorrectPrefix”. This variable should contain the name of the Conceptual Model of your Main model, the one you created for your entry database. Finally, add new replaces for your other databases in the line where the replace happens.


• Backup everything manually to a safe location first. Things can go wrong.


• Run the script, open and validate your principal edmx to check if everything went smooth. 

Limitations

• The script is a workaround to when you need this kind of feature. It’s a shame EF doesn’t support this, and we have to live with that. Entities will not be removed from the main edmx if they were deleted from any leaf module. I didn’t need this feature, so i didn’t implement it.


• I cannot garantee that this script will work in all cases. The model i use is not that complex and doesn’t cover all use cases, so some features may be missing.


• Navigation properties between entities from the merged models are supported. Just be sure you name them with the “LNK_” prefix. This allows me to verify while merging and to keep those rather than overwrite them. My approach is exactly the same as the original script. If you prefer, you can change this prefix in the MergeEDMX method.


• Take care with objects with the same name between models. If a conflict should exist, your model will become corrupted and you have to modify it manually or restore backups.


• When you have to import the model from the database, do it always on the leaf models (except when you are importing from the database of your main model, of course) and then merge it.


• Every time a change occurs in one of the leaf models, you need to merge it again with the main model.

Enough talk! Here is the script, make good use of it!

	//Some change may be needed here, depending on your version
	public static XNamespace edmx = "http://schemas.microsoft.com/ado/2009/11/edmx";
	public static XNamespace xmlnsssdl = "http://schemas.microsoft.com/ado/2009/11/edm/ssdl";
	public static XNamespace xmlnsedm = "http://schemas.microsoft.com/ado/2009/11/edm";
	public static XNamespace xmlnscs = "http://schemas.microsoft.com/ado/2009/11/mapping/cs";
	void Main()
	{
	string rootDir = @"C:\Users\Admin\Documents\visual studio 2013\Projects\ConsoleApplication2\ConsoleApplication2";
	string BackupDestination = @"C:\Users\Admin\Documents\visual studio 2013\Projects\ConsoleApplication2\ConsoleApplication2\Backup";
	string resultFilePath = rootDir + @"\ReportServer.edmx";
	string[] files = new string[]
	{
	rootDir + @"\SimpleAccount.edmx",
	};
	Backup(rootDir, BackupDestination);
	MergeEDMX(files, resultFilePath);
	ReplacePrefixes(resultFilePath);
	ReorderNavigationProperties(resultFilePath);
	}
	private static void MergeEDMX(string[] files, string ResultFile)
	{
	Console.WriteLine ("Merging EDMX");
	foreach (string file in files)
	{
	XDocument doc = XDocument.Load(file);
	XDocument docresult = XDocument.Load(ResultFile);
	//SSDL
	Console.WriteLine ("Merging SSDL");
	MergeNodes(docresult.Root.Descendants(xmlnsssdl + "EntityContainer").First(),
	doc.Root.Descendants(xmlnsssdl + "EntityContainer").Elements(),
	docresult.Root.Descendants(xmlnsssdl + "EntityContainer").Elements(),
	(x,y) => x.Attribute("Name").Value == y.Attribute("Name").Value);
	MergeNodes(docresult.Root.Descendants(xmlnsssdl + "Schema").First(),
	doc.Root.Descendants(xmlnsssdl + "Schema").Elements().Where(x=> x.Name != xmlnsssdl + "EntityContainer"),
	docresult.Root.Descendants(xmlnsssdl + "Schema").Elements().Where(x=> x.Name != xmlnsssdl + "EntityContainer"),
	(x,y) => x.Attribute("Name").Value == y.Attribute("Name").Value);
	Console.WriteLine ("Done SSDL");
	//CSDL
	Console.WriteLine ("Merging CSDL");
	MergeNodes(docresult.Root.Descendants(xmlnsedm + "EntityContainer").First(),
	doc.Root.Descendants(xmlnsedm + "EntityContainer").Elements(),
	docresult.Root.Descendants(xmlnsedm + "EntityContainer").Elements(),
	(x,y) => x.Attribute("Name").Value == y.Attribute("Name").Value);
	//Import NavigationProperties. First we go to already existant entities (OldEntities) to read existing NavProp
	//Then we import those Elements to the new entity (NewEntity) in the loop
	var OldEntities = docresult.Root.Descendants(xmlnsedm + "EntityType");
	var NewEntities = doc.Root.Descendants(xmlnsedm + "EntityType");
	foreach (var NewEntity in NewEntities)
	{
	var OldEntity = OldEntities.FirstOrDefault(x=> x.Attribute("Name").Value == NewEntity.Attribute("Name").Value);
	if (OldEntity == null)
	continue;
	NewEntity.Add(OldEntity
	.Elements(xmlnsedm + "NavigationProperty")
	.Where(x=> x.Attribute("Relationship").Value.Contains(".LNK_")));
	}
	NewEntities = NewEntities.Union(doc.Root.Descendants(xmlnsedm + "Schema").Elements().Where(x=> x.Name != xmlnsedm + "EntityContainer"));
	//
	//Merge the itens inside the Schema (except EntityContainer)
	MergeNodes(docresult.Root.Descendants(xmlnsedm + "Schema").First(),
	NewEntities.ToList(),
	docresult.Root.Descendants(xmlnsedm + "Schema").Elements().Where(x=> x.Name != xmlnsedm + "EntityContainer"),
	(x,y) => x.Attribute("Name").Value == y.Attribute("Name").Value);
	Console.WriteLine ("Done CSDL");
	//C-S
	Console.WriteLine ("Merging C-S");
	//Merge the itens inside EntityContainer in the C-S layer
	MergeNodes(docresult.Root.Descendants(xmlnscs + "EntityContainerMapping").First(),
	doc.Root.Descendants(xmlnscs + "EntitySetMapping"),
	docresult.Root.Descendants(xmlnscs + "EntitySetMapping"),
	(x,y) => x.Attribute("Name").Value == y.Attribute("Name").Value);
	//Merge the functions (requires different attribute comparing)
	MergeNodes(docresult.Root.Descendants(xmlnscs + "EntityContainerMapping").First(),
	doc.Root.Descendants(xmlnscs + "FunctionImportMapping"),
	docresult.Root.Descendants(xmlnscs + "FunctionImportMapping"),
	(x,y) => x.Attribute("FunctionImportName").Value == y.Attribute("FunctionImportName").Value);
	Console.WriteLine ("Done C-S");
	docresult.Save(ResultFile);
	}
	Console.WriteLine("Done Merging");
	}
	//Deletes old nodes that exist in a ParentTarget and in the NewChilds when the comparer returns true
	private static void MergeNodes(XElement ParentTarget, IEnumerable<XElement> NewChilds, IEnumerable<XElement> OldChilds, Func<XElement, XElement, bool> ChildComparer)
	{
	(from OldFunc in OldChilds
	from NewFunc in NewChilds
	where ChildComparer(OldFunc, NewFunc)
	select OldFunc).Remove();
	//Add new functions
	ParentTarget.Add(NewChilds);
	}
	//Reorders the navigation properties of an EDMX
	private void ReorderNavigationProperties(string EDMXFilePath)
	{
	Console.WriteLine ("Reordering navigation properties");
	XDocument doc = XDocument.Load(EDMXFilePath);
	foreach (var Entity in doc.Root.Descendants(edmx + "Runtime")
	.Descendants(edmx + "ConceptualModels")
	.Descendants(xmlnsedm + "Schema")
	.Descendants(xmlnsedm + "EntityType"))
	{
	Entity.ReplaceNodes(
	Entity.Descendants()
	.Where (x => x.Parent == Entity) //Descendentes directos
	.OrderBy (x => x.Name == xmlnsedm + "NavigationProperty" ? x.Attribute("Name").Value : "a" ));
	}
	doc.Save(EDMXFilePath);
	Console.WriteLine("Done");
	}
	//Replace the prefixes of the other models.
	private void ReplacePrefixes(string FilePath)
	{
	Console.WriteLine ("Replacing database prefixes");
	string CorrectPrefix = "Model";
	var text = File.ReadAllText(FilePath);
	text = text.Replace("SimpleAccountModel", CorrectPrefix);
	File.WriteAllText(FilePath, text);
	Console.WriteLine ("Done");
	}
	public void Backup(string source, string destination)
	{
	string DoBackups;
	Console.WriteLine ("Do backups first? y/n");
	do
	{
	DoBackups = Console.ReadLine();
	} while (DoBackups != "y" && DoBackups != "n");
	if (DoBackups == "y")
	{
	Console.WriteLine ("Performing backups");
	DoBackup(source, destination);
	Console.WriteLine ("Done");
	}
	else
	Console.WriteLine ("Ignoring backups...");
	}
	//Backs up all .edmx and .Designer.cs in the Source directory.
	public void DoBackup(string Source, string Destination)
	{
	DirectoryInfo srcDir = new DirectoryInfo(Source);
	DirectoryInfo dstDir = new DirectoryInfo(Destination);
	var files = from file in srcDir.GetFiles()
	where file.Extension == ".edmx"
	|| file.Name.Contains(".Designer.cs")
	select file;
	foreach (FileInfo FileName in files)
	{
	if (!dstDir.Exists)
	dstDir.Create();
	System.IO.File.Copy(FileName.FullName, dstDir.FullName + "\\" + FileName.Name, true);
	}
	}

view raw EF.cs hosted with ❤ by GitHub

Disclaimer: Remember to test the script before using it. This is a workaround and is not perfect, may contain bugs or unexpected behavior. Use at your own risk.

Keep coding.