1

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?)

Factor Mystic
  • 26,279
  • 16
  • 79
  • 95

2 Answers2

1

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
{
  ...
}

Edit: In LINQPad Premium edition, you can now do cross-database queries with SQL Server - in one of two ways.

The simplest is the drag-and-drop approach: hold down the Ctrl key while dragging additional databases from the Schema Explorer to the query editor. To access those additional databases in your queries, use database.table notation, e.g., Northwind.Regions.Take(100). The databases that you query must reside on the same server.

The second approach is to list the extra database(s) that you want to query in the connection properties dialog. This dialog also lets you choose databases from linked servers. Here's how to proceed:

  1. Add a new LINQ to SQL connection.
  2. Choose Specify New or Existing Database and choose the primary database that you want to query.
  3. Click the Include Additional Databases checkbox and pick the extra database(s) you want to include. You can also choose databases from linked servers in this dialog.

You can now do cross-database queries. These are properly optimized insofar as joins will occur on the server rather than the client.

Joe Albahari
  • 30,118
  • 7
  • 80
  • 91
  • So I'd need to create a single assembly, that contains one DataContext class for each table. That assembly would have these DataContext classes for each database I need to access. And this process to generate this assembly must be done manually. Ugh. What about when the databases have tables with identical names? – Factor Mystic Jun 25 '10 at 17:14
  • No - the assembly should contain a SINGLE DataContext that contains the tables you wish to access from both databases. If there's a name collision, give one of the table classes a different name - this won't matter as long as as you've correctly applied the Table attribute. – Joe Albahari Jun 26 '10 at 02:09
0

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
Baaju
  • 1,992
  • 2
  • 18
  • 22