CODEWITHSUNDEEP
c#linqlinq-to-sql
user482375
user482375

Reputation:

Multiple Database connections in a LINQ to SQL DataContext

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

So right 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 right now that handles all four tables. I would like for it to handle the first 3 tables and have another for the last. This possible?

Thanks!

Upvotes: 3

Views: 5566

Answers (4)

Jon
Jon

Reputation: 16718

We have tackled a similar situation by creating the LINQ to SQL context on 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 the other database, and it all Just Works.

A brief outline of 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
}

Production 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 it might not work in your situation, but it has worked well for us.

Upvotes: 0

LongArm
LongArm

Reputation: 208

I have had the same issue in the past and the way I overcame this was to move the table as stated, then create a view in the original database which references the table.

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

Upvotes: 0

Christopher Rathermel
Christopher Rathermel

Reputation: 935

Linq-to-SQL works best when all the data you need is on the same database. If you start moving tables to another database doing the cross database joins can be a pain.

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

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062502

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

If you are careful, you could leave "as is", and just explicitly supply the connection-string to each data-context instance, and just don't use the wrong bits, however: this is risky. In particular, leaving it intact means you still might have queries that try to join between tables that are now in different databases, which won't work.

The data-context here is only designed to work in a single database.

Upvotes: 3

Related Questions