views:

1462

answers:

7

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.

A: 

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

tuinstoel
Third-party. DBA's want to stay away from creating objects there, even if it's a simple view.
gfrizzle
A: 

Create a proc/view in your database.

A: 

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.

TGnat
+12  A: 

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.

Kyralessa
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.
David B
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.
Sam
A: 

Hi,

How do I create DLINQ across multiple databases. Here the Database name is variable. It can change based on user selection from front end. How do I start creating data context class for this..??

please help..

Vinay
A: 

It is very much possible to perform cross database join using LINQ-to-SQL

Refer to the below link, good example of how to perform cross database join operation

http://aspdotnethacker.blogspot.com/2010/06/hello-folks-today-i-will-tell-you-how.html

sandeep