Linq to SQL DBML Access across a Linked Server?

Question

I have a DBML on a single database in a named instance. The instance has a linked server to another MSSQL database in another server/instance. Both instances and databases have the same dbo-level user. Both databases are MSSQL 2008.

I have some C# code (Framework 3.5) that uses the DBML that accesses the tables and views and sprocs on DatabaseA. I now need to access a table on DatabaseB. What is the smartest way for my code to get to the table/entity over the linked server connection?

Thanks.

Answer 1

One clean way of doing this is to create views inside DatabaseA that encapsulate enities on the other side. You will have to manually define the primary keys and relationships for these entities in your .dbml file. Once this is done they can work just like any other table with CRUD functionality as long as the DTC service is running on DatabaseA.