Cross-database calls with LINQ to SQL

Cross database queries are often one of those facts of development. LINQ to SQL offers several advantages that you might want in your project. Unfortunately, a Query cannot contain references to items defined on different data contexts.

Here’s one solution.

Modify the Source attribute of the tables that resides in a database other than the one mentioned in your connection string. For example, change "dbo.tbName" to "otherDataBaseName.dbo.tbName".

Keep in mind the security context of your connection; you will need a single user that can connect to both databases (the databases will also need to reside on the same server).

Tip: I used a second dbml file for adding the tables that are hosted in the second database. Then I copied the dbml over to the original dbml file and modified the Source attribute.

Advertisements