Tricking EF to span Multiple Databases

I was working on a project recently that used Entity Framework and had to span multiple databases. I was quite surprised to find out that EF didn’t support making one edmx file span multiple databases, and so came up with this solution to get around that using SQL Synonyms.

Here’s a quick walkthrough of how it works. Lets say I have two databases, located on separate servers. We’ll call them ServerA.DatabaseA and ServerB.DatabaseB. I would like to have one edmx file that contains objects from both databases, and include links between the two.

Setting up the Synonyms

First, I needed to setup my database synonyms. A simple script like this was run on DatabaseB to print out my CREATE SYNONYM statements, and the resulting output got verified and ran on DatabaseA.

declare @name varchar(max)

DECLARE db_cursor CURSOR FOR
select name from sysobjects where type = 'U'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0
BEGIN   

print 'CREATE SYNONYM [dbo].[' + @name + '] FOR [ServerB].[DatabaseB].[dbo].[' + @name + ']
 '
       FETCH NEXT FROM db_cursor INTO @name
END   

CLOSE db_cursor
DEALLOCATE db_cursor

This allowed me to reference tables on ServerB.DatabaseB from ServerA.DatabaseA using their regular table names. For example, if DatabaseA had Customers and DatabaseB had products, this allows me to run

SELECT * FROM Products

from DatabaseA instead of writing

SELECT * FROM ServerB.DatabaseB.dbo.Products

It will also work on Views, Stored Procedures, and User-Defined Functions. The only catch is no two object names can be the same.

Creating the EDMX files

Next, we need to create our edmx files. You will end up with one working copy of the edmx file, and a separate copy for each database you are trying to span.

Create a project for each database containing nothing more than a single edmx file pointing to one of the databases. Name the edmx files the same and make sure the entity namespaces are the same.

Now create a 3rd project and edmx file for your working copy. Make sure it’s also named the same and has the same entity namespace. Point it to whatever database contains the synonyms. In this case, that’s DatabaseA.

Merging the edmx files

Now we need to merge the edmx files. Since they are just xml files, this could be done by hand, but if we did that we’d need to do it every time the database got updated, so a script is far better.

Here is the one I use. Its not perfect, but for an XML dummy like me it worked just fine. I created a new project to hold it, and whenever I made changes to my edmx files I would run this project.

static void Main(string[] args)
{
    // Directory that can access all 3 edmx files
    // Fairly sure there's a better way to do this, but this
    // was the first that came to my mind
    string rootDir = Directory.GetCurrentDirectory() + @"..\..\..\..\";

    // Location of working edmx file to write merged edmx to
    string resultFile = rootDir + @"DAL\Entities.edmx";

    // List of edmx files to merge
    string[] files = new string[]
    {
        rootDir + @"DatabaseA\Entities.edmx",
        rootDir + @"DatabaseB\Entities.edmx"
    };

    // Load result file
    var a = new XmlDocument();
    a.Load(resultFile);

    // TODO: Clear result file of anything except LNK_ items?
    // Actually that might cause a problem with nav properties.
    // Will probably have to create a merged file of list of files,
    // then merge changes from that single file with the result file

    // Loop through files to merge and add their nodes to the result file
    foreach (var edmxFile in files)
    {
        var b = new XmlDocument();
        b.Load(edmxFile);

        string rootNode = "/edmx:Edmx/edmx:Runtime/";

        XmlNamespaceManager nsMan = new XmlNamespaceManager(a.NameTable);
        nsMan.AddNamespace("edmx", "http://schemas.microsoft.com/ado/2008/10/edmx");

        // SSDL
        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:StorageModels", nsMan)["Schema"]["EntityContainer"],
            b.SelectSingleNode(rootNode + "edmx:StorageModels", nsMan)["Schema"]["EntityContainer"].ChildNodes,
            a, b);

        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:StorageModels", nsMan)["Schema"],
            b.SelectSingleNode(rootNode + "edmx:StorageModels", nsMan)["Schema"].ChildNodes,
            a, b);

        // CSDL
        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:ConceptualModels", nsMan)["Schema"]["EntityContainer"],
            b.SelectSingleNode(rootNode + "edmx:ConceptualModels", nsMan)["Schema"]["EntityContainer"].ChildNodes,
            a, b);

        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:ConceptualModels", nsMan)["Schema"],
            b.SelectSingleNode(rootNode + "edmx:ConceptualModels", nsMan)["Schema"].ChildNodes,
            a, b);

        // MSL
        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:Mappings", nsMan)["Mapping"]["EntityContainerMapping"],
            b.SelectSingleNode(rootNode + "edmx:Mappings", nsMan)["Mapping"]["EntityContainerMapping"].ChildNodes,
            a, b);

    }

    // Save result file
    a.Save(resultFile);

    Console.WriteLine("Done");
    Console.ReadLine();
}

private static void MergeNodes(XmlNode parentNodeA, XmlNodeList childNodesB, XmlDocument parentA, XmlDocument parentB)
{
    foreach (XmlNode oNode in childNodesB)
    {
        // Exclude container node and comments
        if (oNode.Name == "EntityContainer" || oNode.Name == "#comment") continue;

        bool isFound = false;
        string name = oNode.Attributes["Name"].Value;

        foreach (XmlNode child in parentNodeA.ChildNodes)
        {
            if (child.Name == "EntityContainer" || child.Name == "#comment") continue;

            // If node already exists, exit loop
            if (child.OuterXml == oNode.OuterXml && child.InnerXml == oNode.InnerXml)
            {
                isFound = true;
                Console.WriteLine("Found::NoChanges::" + oNode.Name + "::" + name);
                break;
            }

            // If node by the same name exists
            if (child.Attributes["Name"].Value == name)
            {
                if (oNode.Name == "EntityType")
                {
                    foreach (XmlNode property in child.ChildNodes)
                    {
                        // Make sure to keep any navigation properties that have been added
                        if (property.Name == "NavigationProperty"
                            && property.Attributes["Relationship"] != null
                            && Regex.IsMatch(property.Attributes["Relationship"].Value, @".*\.LNK_.*"))
                        {
                            oNode.AppendChild(parentB.ImportNode(property, true));
                        }
                    }
                }

                isFound = true;
                Console.WriteLine("Found::Replaced::" + oNode.Name + "::" + name);
                parentNodeA.ReplaceChild(parentA.ImportNode(oNode, true), child);
            }
        }        if (!isFound)
        {
            Console.WriteLine("NotFound::Adding::" + oNode.Name + "::" + name);
            parentNodeA.AppendChild(parentA.ImportNode(oNode, true));
        }
    }}

This is a work in progress, but it works for now. I’ll update this post after I fix up the merge project.

Creating Links between the Databases

Now you’ll have one edmx file, containing the information from both databases. You can create Associations between the two objects, however they will be erased anytime you merge your changes. To get around this, make sure all your links contain the same prefix and modify the merge code to exclude NavigationProperties that are prefixed with whatever you use. In my case, I prefixed them with LNK_

Summary

And there you have it. Test it out and you’ll find it works just fine. Your working edmx points to DatabaseA, which contains synonyms for DatabaseB objects. EF has no idea that half its objects exist on another database, because from its point of view it is all one database. Navigational properties between the two databases work just fine providing you’ve manually setup the links in the working copy.

To update the working copy, update the projects containing the individual database edmx, and run the merge code again. Providing you don’t make any changes to the working copy except links, and all your links contain the same prefix to avoid getting erased when the merge occurs, you should have no problems.

Notes

• This only works in a databases that supports Synonyms. I was using SQL Server 2005.
• This was the first project I did using EF and I was using EF 4. I have no idea if this works on older versions.
• You have to be sure that items are uniquely named between databases.
• The Merge code is not perfect – sorry. I plan on fixing it up to remove Deleted items but haven’t gotten around to it. I just have a TODO bit in my code for now. If someone knows of a better tool to merge XML files, let me know! It has to be something an xml-idiot can use though