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:

• 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!



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.