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.

score 2 · Accepted Answer · answered Oct 05 '10 at 03:40

2

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.

answered Oct 05 '10 at 03:40

James

12,636
12
67
104

Thanks for the response. My problem is that I can't make any DB schemata changes. – Snowy Oct 05 '10 at 15:39
That is more problematic can you request changes? If not I don't really see any other way unless you can talk to DatabaseB from another dbml context or connection. – James Oct 05 '10 at 16:38

score 0 · Answer 2 · answered Jun 29 '17 at 13:41

Try adding a linked server to your local:

EXEC sp_addlinkedserver     
   @server=N'SERVER',   
   @srvproduct=N'',  
   @provider=N'SQLNCLI',   
   @datasrc=N'SERVER';  
SELECT * FROM sys.servers

EXEC sp_addlinkedsrvlogin '<SERVER>', 'false', '<DOMAIN>\<USERNAME>', '<USER>', '<PASSWORD>';

And access your local referring to the linked server:

SELECT * FROM SERVER.DB.SCHEMA.OBJECT

score 0 · Answer 3 · answered Jun 30 '17 at 15:16

I had used SQL Synonyms in Entity Framework and LINQ-To-SQL, you can create a SQL Synonnym to point to a linked Server object, like this:

And then perform a SQL Query:

Northwnd db = new Northwnd(@"c:\northwnd.mdf");
IEnumerable<Customer> results = db.ExecuteQuery<Customer>
    ("SELECT contactname FROM customersSynonym WHERE city = {0}",
    "London");

You can read the documentation here and also you can read another question like this one but using Entity Framework, which uses the same principle, using a SQL Synonym.

Linq to SQL DBML Access across a Linked Server?

3 Answers3