DataContext across multiple databases

Question

I have an application that needs to join tables from multiple databases into a single LINQ-to-SQL query. Unfortunately, I have a separate DataContext class setup for each database, so this query won't work. I get an error like this: "The query contains references to items defined on a different data context"

The ideal solution seems to be to create a single DataContext for all three databases. They all exist on the same server, so they can use the same connection string. I currently use a script that runs sqlmetal.exe to generate my DBML and CS files, meaning that I don't need to manually edit any files when I change the data model. I want to maintain that level of automation, but sqlmetal.exe only seems to support a single database per DBML file.

Is that possible with sqlmetal or another tool? Or, do I need to consider another solution like using a single database for the entire application?

Here's the batch file script I'm using:

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseOne /views /functions /sprocs /dbml:DatabaseOne.dbml /namespace:Model.Domain.DatabaseOne /context:DatabaseOneDataContext /pluralize
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseOne /views /functions /sprocs /code:DatabaseOne.designer.cs /language:C# /namespace:Model.Domain.DatabaseOne /context:DatabaseOneDataContext /pluralize

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseTwo /views /functions /sprocs /dbml:DatabaseTwo.dbml /namespace:Model.Domain.DatabaseTwo /context:DatabaseTwoDataContext /pluralize
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseTwo /views /functions /sprocs /code:DatabaseTwo.designer.cs /language:C# /namespace:Model.Domain.DatabaseTwo /context:DatabaseTwoDataContext /pluralize

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseThree /views /functions /sprocs /dbml:DatabaseThree.dbml /namespace:Model.Domain.DatabaseThree /context:DatabaseThreeDataContext /pluralize
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SqlMetal.exe" /server:MYSERVER /database:DatabaseThree /views /functions /sprocs /code:DatabaseThree.designer.cs /language:C# /namespace:Model.Domain.DatabaseThree /context:DatabaseThreeDataContext /pluralize

I found an answer that will probably work for me, but I'd still be interested in a higher-level fix. In other words, ubersqlmetal. — MikeWyatt
– MikeWyatt, Commented Dec 15, 2009 at 23:13

MikeWyatt · Accepted Answer · 2009-12-15 23:12:54Z

2

A co-worker found a thread on another site [social.msdn.microsoft.com] that discusses this same issue. One discussed solution was to perform all joins in views in the "primary" database, and expose those views as objects in the application. That will probably work in my situation, since the majority of my data is in one database, and the small number of tables in the other databases are read-only.

answered Dec 15, 2009 at 23:12

MikeWyatt

7,89111 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aaronaught · Accepted Answer · 2009-12-15 23:52:06Z

1

This is going to sound a little crazy, but I just tested it, so try this:

Create a custom entity class for the "external" table you want to join to, or use SqlMetal to generate the class;
Go to the TableAttribute, which should say something like [Table(Name="dbo.ExternalTable")], and change it to [Table(Name="DatabaseTwo.dbo.ExternalTable")]

Create a partial class with the same name as the "DatabaseOne" DataContext, and add your Table<T> property there, i.e.:

partial class DatabaseOneDataContext
{
    public Table<ExternalTableRow> ExternalTable
    {
        get { return GetTable<ExternalTableRow>(); }
    }
}

And now try running your entire query off of the first DataContext:

DatabaseOneDataContext context = new DatabaseOneDataContext();
var query = from s in context.RealTable
            join t in context.ExternalTable
              on s.ID equals t.ID
            select new { s, t };
Console.WriteLine(query.ToList().Count);

Unbelievably, it works. It's not quite as simple as just using SqlMetal, but you only need to write the partial class once, then you can just run SqlMetal on both databases and change the TableAttribute of any external tables to include the database name.

It's not perfect, but it's 95% there, no?

answered Dec 15, 2009 at 23:52

Aaronaught

123k26 gold badges273 silver badges344 bronze badges

2 Comments

MikeWyatt Over a year ago

This is interesting, but it would still require a bit of work to fully automate (as you said). I'll give it a shot if creating views in the main DB doesn't work out.

Aaronaught Over a year ago

You could probably write a short script that you just feed the two CS files (generated by SqlMetal) and the table names you want to "import", and have it automatically generate the partial class and entities. The former is a text template, the latter is just a string.Replace, so you'd end up with something totally automated. But if you control the databases and don't mind creating cross-database views, then that will be easier to manage in the long haul.

Collectives™ on Stack Overflow

DataContext across multiple databases

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related