Tuesday, June 3, 2008

LINQ and synonyms / linked servers

I've been asked if it's possible to perform joins across different databases using LINQ. The first obvious answer is yes, you just do inproc join using Linq To Objects and two different contexts:

from a in new List<A>( context1.TheA )
  join b in new List<B>( context2.TheB )
    on ---

But what if you'd like to have it executed on the server side? At first I thought that it should be obvious - while you cannot refer to two different contexts, you can of course use linked servers / synonyms mechanism built into the SQL Server 2005.


This however turned out to be impossible - for an unknown reason, probably a bug, sqlmetal does not include synonyms in the generated model. And because VS uses internally the same mechanism used in sqlmetal, the designer is not a big help also.


But what if you generate the missing part of the model by hand? Well, it seems that it does the trick. You just

  • generate the model for the main database

  • generate models for databases which contain tables linked from within the main database

  • trim these generated models (usually you do not create synonyms for the WHOLE database but rather for selected tables)

  • change the namespace and the class name of all generated classes to the same names

That's it. You have a single model which spans several files generated independently but it does not matter as all files contain partial definition of the same class.


BTW. The lack of the on-demand synchronization between the relational database schema and the dbml definition is kind of mockery to me.

No comments: