Multiple Database Connections in LINQ to SQL DataContext

I have a LINQ to SQL DataContext that queries four different tables. But I need to move one of these tables to another database. Is it possible to have a database and connection string for certain tables and another for another table?

So now I have something like:

[global::System.Data.Linq.Mapping.DatabaseAttribute(Name="DATABASE1")]
public partial class DataClassesDataContext : System.Data.Linq.DataContext
{
    private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();

    public DataClassesDataContext() : base(global::System.Configuration.ConfigurationManager.ConnectionStrings["DATABASE1ConnectionString"].ConnectionString, mappingSource)
    {
        OnCreated();
    }

    public DataClassesDataContext(string connection) : base(connection, mappingSource)
    {
        OnCreated();
    }
}

      

So now that all four tables are being processed. I would like it to handle the first 3 tables and have another one for the last one. Is it possible?

Thank!

+3


source to share


4 answers


Not directly; the most obvious would be to split the data context into two separate data context classes (and two dbml installations).

If you're careful, you can leave "as is" and just explicitly specify the connection string for each instance of the data context, rather than use the wrong bits , however: this is risky. In particular, by leaving it intact, you can still have queries that try to join between tables that are now in different databases that won't work.



The data context here is only meant to work in one database.

+3


source


Linq-to-SQL works best when all the data you need is in one database. If you start moving tables to a different database, then cross-database merging can be painful.



http://www.enderminh.com/blog/archive/2009/04/25/2654.aspx

+1


source


We ran into a similar situation by creating a LINQ to SQL context in a development database that has all the tables in one database, and then creating a synonym in the production database to point to the table (s) in another database, and it all works ...

Brief information on how it works:

Dev environment:

use [TheDatabase]
go

create table Table1
{
    -- stuff goes here
)
go

create table Table2
(
    -- stuff goes here
}
go

create table Table3
{
    -- stuff goes here
}

      

Work environment

use [Database2]
go

create table Table3
{
    -- stuff goes here
)

use [Database1]
go

create table Table1
{
    -- stuff goes here
)
go

create table Table2
(
    -- stuff goes here
}
go

create synonym Table3 for Database2.dbo.Table3

      

Obviously, depending on your environment, this may not work in your situation, but it worked for us.

0


source


I had the same problem in the past and the way I overcame it was to move the table as stated and then create a view in the original database that references the table.

There is one drawback in that the view is then read-only. However, in the future I would not recommend this approach, I would recommend separate datacontexts for each database.

0


source







All Articles