LINQ - Multiple database in LINQ - Asked By Shan on 27-May-11 06:49 AM

Hi,

 I have 2 databases.I wanted to use both simultaneously. I am able to add the two different database tables in the same dbml,but I latest connection exists.
How to proceed.
Ravi S replied to Shan on 27-May-11 11:21 AM
HI

The LinqToSql library implements the following features and functionality:
  1. Queries multiple databases in one expression e.g. a Microsoft Access Database and an SQL Server Database
  2. Translates function calls and property accessors in the String and DateTime classes that have SQL equivalents e.g. firstName.Length, firstName.ToUpper(), orderDate.Year etc.
  3. Implements all IQueryable methods e.g. GroupBy, Any, All, Sum, Average, etc.
  4. Correctly and comprehensively translates binary and unary expressions that have valid translations into SQL.
  5. Parameterizes queries instead of embedding constants in the SQL transformation.
  6. Performs caching of previously translated expression trees.
  7. Does not use MARS - Multiple Active Result Sets, an SQL Server 2005 specific feature.
  8. Correctly translates calls to SelectMany even when the query source involves method calls. The SQL Server 2005 specific keyword CROSS APPLY is neither required nor used

refer the link for example
http://www.codeproject.com/KB/dotnet/linqToSql5.aspx
http://stackoverflow.com/questions/352949/linq-across-multiple-databases

Jitendra Faye replied to Shan on 28-May-11 03:57 AM

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.