LINQ across multiple databases

Question

I've got two tables that need to be joined via LINQ, but they live in different databases. Right now I'm returning the results of one table, then looping through and retrieving the results of the other, which as you can guess isn't terribly efficient. Is there any way to get them into a single LINQ statement? Is there any other way to construct this to avoid the looping? I'm just looking for ideas, in case I'm overlooking something.

Note that I can't alter the databases, i.e. I can't create a view in one that references the other. Something I haven't tried yet is creating views in a third database that references both tables. Any ideas welcome.

Answer 1

Create a proc/view in your database.

Answer 2

You can do this, even across servers, as long as you can access one database from the other. That is, if it's possible to write a SQL statement against ServerA.DatabaseA that accesses ServerB.DatabaseB.schema.TableWhatever, then you can do the same thing in LINQ.

To do it, you'll need to edit the .dbml file by hand. You can do this in VS 2008 easily like this: Right-click, choose Open With..., and select XML Editor.

Look at the Connection element, which should be at the top of the file. What you need to do is provide an explicit database name (and server name, if different) for tables not in the database pointed to by that connection string.

The opening tag for a Table element in your .dbml looks like this:

<Table Name="dbo.Customers" Member="Customers"> 

What you need to do is, for any table not in the connection string's database, change that Name attribute to something like one of these:

<Table Name="SomeOtherDatabase.dbo.Customers" Member="Customers"> <Table Name="SomeOtherServer.SomeOtherDatabase.dbo.Customers" Member="Customers"> 

If you run into problems, make sure the other database (or server) is really accessible from your original database (or server). In SQL Server Management Studio, try writing a small SQL statement running against your original database that does something like this:

SELECT SomeColumn FROM OtherServer.OtherDatabase.dbo.SomeTable 

If that doesn't work, make sure you have a user or login with access to both databases with the same password. It should, of course, be the same as the one used in your .dbml's connection string.