Linq2SQL Cross DataBase Join

August 9, 2010

I recently had a requirement to query some data in another DataBase, which of course was not available within the main DataContext for my application. At first, I thought I would add a seperate DataContext for the related data, then query it to get the related records, generate data tables and perform a join – This all seemed rather inefficient.

After some trial and error I discovered it is possible to connect to different databases from within one single Linq2SQL data context.

Heres how I did it:
(Below assumes you already have a working Linq2SQL DataContext in place)

  1. In server explorer, add a connection to the database you want the data from (B)
  2. Open your existing DataContext (.DBML File), which will have table(s) from a different database (A)
  3. Drag the required table(s) from (B) to the DataContext
  4. For each table you use, view the properties and change the source to qualify the database name, e.g. dbo.Countries becomes DatabaseB.dbo.Countries
  5. The DataContext will only connect using one connection, so you’ll need to connect to SQL with an account that has the relevent permissions for each database

Whats the Point in this?

After doing the above, I was able to query two databases at once using a cross-database join. This in itself it nothing new – Its been possible in SQL server at least since SQL 2000, but achieving the same result in LINQ2SQL was not obvious. Its also very handy to be able to connect to another database when required – But I really would recommend considering two DataContexts for this – Although bear in mind points below…

Useful Pointers:

  • It is possible to specify the connection string when instantiating the connection – This will work fine if you only want data from one place at a time, although if this is the case you may as well have two DataConext
  • Joins will not work across DataContexts
  • I actually ended up using the same account that was used to connect to Database A, simply giving this read permission to Database B – But be very careful here!
  • It’s a good idea to rename the related classes in your DataContext so you (and future developers) know that these are from a separate database. I suggest prefixing them with the database name, followed by an underscore

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: