The other day I needed to do a cross-database LINQ-to-SQL join, and encountered the mysterious “the query contains references to items defined on a different data context” InvalidOperationException. After an extensive web search, I almost gave up, as every web reference I found on the internet states that doing a cross-database LINQ join is not possible.
However, I believe that is not fully correct, as I was able to do so when the two tables reside on the same physical SQL Server in two different database instances and feel compelled to write this blog post to rectify this misinformation.
First of all, doing a cross-database join in SQL alone is not something I recommend you to do. Your program’s architecture should not require you to do so. If it does, consider putting your data tables onto the same database instance. However, sometimes you have to deal with large amounts of legacy code where you don’t have the luxury to do this refactor. If so, in order to do a cross-database join via LINQ you need to configure the table in the foreign database with a fully-qualified database name in your DataContext designer like so:
Note that you do not have to create two DatabaseContexts or database connection string; a single .dbml file suffices. Visual Studio .NET might warn you when you attempt to drag the table from the foreign database instance into your server, but it’s possible. Just set the “source” property appropriately, and then you can write your LINQ query as if that table is “in-house”.
I was able to do so when the two database instances reside on the same physical SQL Server. I am certain that there are performance implications with this (it probably does the join in-memory), so do this with a grain of salt. I haven’t tried doing this when the two database instances actually reside on different servers--I doubt that works, but someone try and let us know.