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.

Screenshot of MergeEdmx Projects

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 :)

26 Responses to Tricking EF to span Multiple Databases

  1. James G says:

    Great idea Rachel. Thanks for the tip.
    I am using EF code first (no edmx files..) across multiple databases on the same sql instance and wanted to avoid distributed transactions. By creating a synonym on one instance and modifying the EntityTypeConfiguration mapping to point to the synonym, I was able to run queries across the databases without using MS-DTC.
    Great post.

    • Rachel says:

      Thanks James! Glad it helped you out. This was my first time working with EF and I wasn’t willing to accept the fact that a single EF model couldn’t span multiple databases without a lot of extra work configuring views. I was quite surprised that using synonyms wasn’t a commonly-known workaround so figured I’d post something about it.

  2. Stephen says:

    I tried using this with WCF Data Services (OData) and it does not work.

    It seems that the meta data is not there….

    • Rachel says:

      I have not worked with OData WCF Data Services yet, so it’s possible that it doesn’t work with Synonyms

      • Stephen says:

        I think part of the problem is that the class that inherits from ObjectContext does not have the entities in it. (Though they do appear in the designer just fine.)

        I am going to play around with this and see if I can get it to work with OData. If I do, I will post back with more info.

        Thanks for the great blog post!

      • Stephen says:

        This again and it worked just fine. I think I had not compiled in the right order the first time.

        Thank you for the great tip. This will let me present a single OData endpoint for several databases!

      • Stephen says:

        So, I figured out what the problem was. After you merge the files, you need to right click on the merged edmx and select “Run Custom Tool”. That will force Entity Framework to update the designer.cs file.

        Simple, I know, but I had forgotten to do it. Once I added that simple step, it all started working.

      • Rachel says:

        Glad you got it working, and thanks for sharing what the problem was!

  3. Dominik says:

    There seems to be a problem with that approach when you are mixing instances of SQL Server 2008 and 2005.
    If the working model is pointed at SQL Server 2008 and the synonyms point at 2005 tables, EF Querys may use datatypes that are not known to the SQL Server 2005.

    http://sql-ution.com/error-using-net-entity-framework-4/

    • Rachel says:

      Hi Dominik,

      You can tell EF that it should compile for SQL 2005. You can do this by opening your edmx file in a text editor and changing the ProviderManifestToken property of the Schema tag from 2008 to 2005 (should be at the top of the file, directly under the tag)

      <Schema Namespace="YourNamespace.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" … >

      Afterward, re-compile and it should work.

      • Dominik says:

        Yes, i did try the trick, but sadly it didn’t work for me.
        The EF would still generate the exception. For the moment i decided to add another model to access my second database.

  4. MMu says:

    Dear Rachel,

    thank you for great post.
    It works great between multiple DBs on the same server.
    As soon as I tried to do it between DBs on two servers I have got following error message:

    A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

    Has you or anyone else met with such an error and solved it somehow?

    Thank you for your reply.

    With kind regards,

    Marek

    • Rachel says:

      Hi Marek,

      I know this works on databases located on different servers because that was the original network design I was working with when I wrote this.

      There are some limitations in executing queries across linked servers, so I did a quick Google search on the error and the top item was the MSDN site for SQL’s OUTPUT clause. In the Remarks section, it says that the OUTPUT clause is not supported for Remote tables, so I suspect that is your problem.

      Hope that can point you in the right direction. Good luck with your project.

      Rachel

  5. Hi Rachel

    Great Post, congratulations!!!!

    I put in this message some key words for spiders apps like google and Bing find better your post and positioning for more developers searches results OK
    How to bind two datacontexts on EF, merge schemas on EF

  6. Simone says:

    Uff… Does not work for me :(
    I’m using VS2008… I’ve follewed exactly your instructions.
    In my “merged edmx” I see synonyms but I don’t manage to use them in my code!
    What could be?

    • Simone says:

      I see synonyms like table in my edmx but not in Designer.cs

      • Simone says:

        Solved… Im sorry for my previous comments…
        However the problem was a property in edmx file:
        “Lazy Loading Enabled” set to TRUE… Ive set it to False and the Designer.cs file has been updated immediately….

        Now everything works!!

        Thank you very much for yuoir interesting post!

      • Rachel says:

        Glad you got it figured out, and thanks for sharing your solution :)

  7. simone says:

    it seems does not work in entity framework 3.5 :(

    • simone says:

      Solved:
      change the namespace with

      nsMan.AddNamespace(“edmx”, “http://schemas.microsoft.com/ado/2007/06/edmx”);

      After the edmx in created click on right click of mouse and validate it!

  8. vvutpala says:

    Nice article. Would be more helpful if sample source code is available for download.

    • Rachel says:

      Thanks, and I’ll see about adding a sample project some day but life’s been fairly busy lately so I probably won’t be able to get to it anytime soon

  9. David says:

    Thanks for the article, a simple solution that works great. You just saved me a lot headaches.

  10. Kai says:

    Entity Framework 5
    need to change the namespace with:

    nsMan.AddNamespace(“edmx”, “http://schemas.microsoft.com/ado/2009/11/edmx”);

  11. Is this method still true with Entity Framework 6? Surely there has to be a better way to do this via CodeFirst Migrations. Large Organizations have multiple SQL Server Databases and Multiple Server Instances. I would think that the demand for this ability would be enough for Microsoft to include it in some upcoming release.

    • Rachel says:

      Hi Kenneth,

      I haven’t tested this in EF6, however I don’t see why the code wouldn’t work in that version as well.

      I know this is one of the highest voted requests for the EF team, so I would hope they decide to include it in a future version of their framework eventually. The current status of it is “Under Review”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 109 other followers

%d bloggers like this: