How can I use two different databases with Linq to SQL in Linqpad?

Question

I'm starting out with Linq To SQL, fiddling around with Linqpad and I'm trying to duplicate a SQL script which joins on tables in separate databases on the same server (SQL Server 2008).

The TSQL query looks approximately like this:

using MainDatabase
go

insert Event_Type(code, description)

select distinct t1.code_id, t2.desc

from OtherDatabase..codes t1
     left join OtherDatabase..lookup t2 on t1.key_id = t2.key_id and t2.category = 'Action 7'

where t2.desc is not null

I'm basically trying to figure out how to do a cross-database insertion. Is this possible with Linq To SQL (and is it possible in Linqpad?)

Answer 1

Use linked servers with fully qualified names to query another database from the current DB. That should work.

using MainDatabase 
go 

insert Event_Type(code, description) 

select distinct t1.code_id, t2.desc 

from <Linked_Server>.OtherDatabase..codes t1 
     left join <Linked_Server>.OtherDatabase..lookup t2 on t1.key_id = t2.key_id and t2.category = 'Action 7' 

where t2.desc is not null 

Answer 2

This is possible in LINQ to SQL if you create a (single) typed DataContext that contains table classes for objects in both databases. This designer won't help you here, so you have to create some of the table classes manually. In other words, use the VS designer to create a typed DataContext for your primary database, then manually add classes for the tables in the other database that you wish to access:

[Table (Name = "OtherDatabase.dbo.lookup")]
public class Lookup
{
  ...
}

Right now LINQPad doesn't let you query across multiple databases "out of the box" - this is a requested feature. However, if you write a typed DataContext in VS as described above, you can query it in LINQPad as follows:

1. Click 'Add Connection'
2. Click 'Use a typed DataContext from your own assembly'
3. Enter the details of your assembly and connection in the next screen.

Your custom DataContext will then show in the schema explorer and you'll be able to interactively query it.