Linked servers and Entity Framework. Any alternative?

Question

I have to rewrite a ASP.NET webforms log viewer application. The new version is based on ASP.NET MVC.

Architecture:

There is one web frontend but there are multiple databases spread all over the world (per jurisdiction). The current solution has linked servers from a central DB to all other instances. A few jurisdictions are protected by firewalls but there is an exception rule on the firewall which allows a connection from the central DB server to the jurisdictional SQL Server. No other connections are allowed.

What they did in the current solution is, that they created a separate DB on the central server containing views which access the remote DB tables through the linked servers. As mentioned, there is one UI instance. The user can select the DB instance for checking the logs.

I want to migrate this to entity framework and it would work if i use the same architecture.

Now the question: Is there any other way to achieve this without creating/maintaining these link databases in order to achieve the same result?

Answer 1

No, nothing simpler than this as of EF 6.1.

If synonyms were supported IN EF then you could create in your central DB synonyms for all tables in the linked databases (in different schemas, of course). That would take some of the overhead off because synonyms are easier to maintain than views and you could easily do that with a DataTools project for the central database.

Synonyms support is currently a proposal for a next version of EF.

As for now, why do you not create and maintain the views in the central database? One schema per linked server/database:

[jurisdiction1].[vTableA]
[jurisdiction1].[vTableB]
...
[jurisdictionN].[vTableA]

Similar to synonyms, with the downside that you need to write more code for the views. You could still use DataTools for versioning, change tracking and to provide incremental update scripts.

If you think it's hard to manage your database(s) now, I recommend looking into DataTools. They make schema management a lot easier to handle.