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.

Narco

Why can't you change the database(s)? – Srinagar

Third-party. DBA's want to stay away from creating objects there, even if it's a simple view. – Narco

Answer 1

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.

Teeth

If there is concern about modifying a generated file, I recommend extracting the generated (C# or VB) class to a file under manual control and making the change in the attributes on that class. – Soileau

You can change the source in the O/R-Designer as well. Just open the properties of a table, look for "source", which will contain "dbo.Customers" and can be changed to contain the database name "SomeOtherDatabase.dbo.Customers". So this is no hack, it's perfectly fine to change the source. – Valdovinos

I was looking through a slew of questions and answers here about this, and everyone kept saying it can't be done.. it can't be done.. it can't be done.. i was getting frustrated, but this answer works great. Only to prepare it I have to create all the tables in a temporary database to create the model, then go back and fix the table names and point it to the production database. a bit of effort, but it works wonderfully. – Ossetia

Answer 2

Create a proc/view in your database.

Penna

Answer 3

Given your conditions, I don't think you can do this in one Linq statement. But you can join the results of your L2S queries into a Linq to Objects query.

Christa