Minh T. Nguyen

        "Enemy's Gate Is Down" 		Search this site:

Minh Tri Nguyen Minh T. Nguyen enderminh Vietnamese nguyentriminh blog Visual Studio .NET Tips and Tricks Nguyễn Trí Minh
posts - 220, comments - 1949, trackbacks - 138
Bookmark and Share

How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

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:

Using fully-qualified name to do cross-database LINQ joins

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.

posted on Saturday, April 25, 2009 7:06 PM

Feedback

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Hey thanks for this,

Though I would let you know that the join happens on the SQL server. I pulled out the generated SQL and had a look.
5/10/2009 4:33 PM | GravyPower

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

im getting an error:

Invalid object name 'DB1.dbo.[tbl1]'.
8/31/2009 8:09 AM | Martin Kirk

# Very Helpful!!

Thanks for this tip!! It solved my problem.
11/10/2009 8:03 AM | Eric

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Thank you.
2/22/2010 10:54 AM | Hanımın Çiftliği 21. Bölüm izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

I would suggest using dependency injection to pass a class that provides the means for retrieving the current user to make this audit implementation more usable from different environments.
2/22/2010 11:50 PM | bonus di primo deposito

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

thank you very much. good site.
2/24/2010 2:52 PM | izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Thanks man
2/27/2010 5:19 PM | divx izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

nice man
3/11/2010 10:03 AM | film izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

thank you very much.
3/13/2010 5:24 PM | bölüm izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

oh, my god! i didn't know person can be judged with such pure evidences...it's not fail and world should know about those bad events.
3/22/2010 3:41 PM | Chat sitesi

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

thank you very much.
3/25/2010 4:38 AM | Bedava Program

# porno izle, pornosu izle, bedava porno izle, ücretsiz porno izle, sikiş izle, sikişi izle, bedava sikiş izle, ücretsiz sikiş izle

thanks admin
3/25/2010 11:35 AM | porno izle

# porno seyret, pornosu seyret, bedava porno seyret, ücretsiz porno seyret, sikiş seyret, sikişi seyret, bedava sikiş seyret, ücretsiz sikiş seyret

thanks google
3/25/2010 11:36 AM | sikiş izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

thankx
3/26/2010 10:49 PM | tag heuer watches

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

really nice..
3/26/2010 10:50 PM | casio watches

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

karen wimblley
3/26/2010 10:51 PM | swiss army watches

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

free web killer
3/26/2010 10:52 PM | breitling watches

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

http://www.filmshared.com/
http://www.filmevi.org/
4/23/2010 4:28 AM | film izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Great site! Lots of good resources for me to use.
5/7/2010 10:30 PM | registry cleaner reviews

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

good option. best wishes

http://www.dengejiyan.com
http://www.sozcukcevir.com
http://www.makinaburada.net
5/8/2010 8:02 AM | football videos

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

thank you very much.
5/18/2010 7:11 AM | filmini izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Thanks man
5/24/2010 9:03 AM | program indir

# Los Angeles DUI Lawyers

i never thought it would be such a easy one to do.before reading the post i thought it is little bit difficult one <a href="http://www.duiesq.com">Los Angeles DUI Lawyers</a>
5/27/2010 7:40 AM | Los Angeles DUI Lawyers

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

the post helped me how to link cross database with sql.thanks.with out i would have been did that
5/27/2010 11:27 AM | florida injury lawyer

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

This post cleared a lot up for me, thanks!
6/1/2010 11:50 AM | free cam girls

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

I just found this post from the search engine thanks for the post
6/3/2010 4:52 AM | seo services

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

good one

<a href="http://aspdotnethacker.blogspot.com/2010/06/hello-folks-today-i-will-tell-you-how.html">http://aspdotnethacker.blogspot.com/2010/06/hello-folks-today-i-will-tell-you-how.html</a>
6/3/2010 12:23 PM | secret

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Thanks
6/8/2010 11:13 AM | oyun

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

i thought it is complicated thing but it is simple for me to do after seeing it here
6/14/2010 12:30 AM | seo los angeles

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Thanks for the information of cross-database LINQ-to-SQL.
6/16/2010 9:42 PM | weber grill sale

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Thanks for the information...I was looking for the same solution...!!
6/17/2010 1:10 AM | win money online

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

You are nice and keep writing like this,
6/20/2010 6:47 AM | Houston Cosmetic Dentist

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Thanks for these tips. Keep posting more updates.:)



6/21/2010 3:16 AM | tungsten wedding bands

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

In Visual Studio 2008 and onwards, its very easy to create a DataContext (DBML) with your database entries (tables, views and relations). It is however impossible to use the designer and all the automation to create a Cross-Database (X-DB) DBML layout.
6/22/2010 8:11 PM | sweepstakes

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

LINQ to SQL provides a runtime infrastructure for managing relational data as objects without losing the ability to query. Your application is free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically.

<a href="http://www.bumgarnerandgoodwin.com/CM/FamilyLaw/Divorce.asp">oklahoma divorce attorney</a>
6/22/2010 10:05 PM | oklahoma divorce attorney

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Hai. This is a very good information for database managers. I am newly learning database. and this thing looks interesting.
6/25/2010 3:40 AM | austin cosmetic dentist

# How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

I will look forward in future also to get more updates...!!!!

6/25/2010 11:03 AM | weber grill cover

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

LINQ to SQL is unable to resolve this query, even though both databases sit on the same server. The compiler will however not warn you not to do this, instead a runtime exception with message
6/28/2010 3:41 AM | win money

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Hi !!!!
For this topic you can take a help from asp.net website.....

6/28/2010 9:53 PM | win free money

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

I'm planning on updating all of these posts to VB in the future. I started off the LINQ to SQL series doing both languages, but found that it ended up being really hard to read the posts when everything was duplicated. I'll be creating separate copies that contain pure VB in the future and link off to all of them.

Thanks,
6/29/2010 2:09 AM | win free stuff

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Your very nice sharing
7/1/2010 2:02 PM | sikiş izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

I could not do it:(
7/1/2010 2:04 PM | sikiÅŸ izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

I could not do it:(
7/1/2010 2:04 PM | liseli pornosu

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Those Jackson kids are adorable! What! No mention of Pink's outstanding performance--twirling and splashing water on all the celebs (help I'm melting) Her voice never quivered. Unlike, Taylor and Stevie--never liked Stevie I think her voice is awful. I do a great impersonation -- kinda Katherine Hepburn and Joan Rivers mixed together --volatile.



film izlemek için http://www.filmshared.com/
7/7/2010 8:48 AM | film izle

# Mr

This one did helped me know things better.I would like try on my own now.If i find any difficulty in doing this i would like to bring in front of you,hope you will assist me.
7/8/2010 2:54 AM | London Female Escorts

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

I could not do it:(
7/20/2010 11:42 AM | perde

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

kinda Katherine Hepburn and Joan Rivers mixed together --volatile.
7/20/2010 12:01 PM | müjde ar

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Thanks for this tip!! It solved my problem.
7/20/2010 12:32 PM | eskort

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

Hi,
I really like your topic .. Topics I've shared your facebook .. Thank you very much again .. In addition, I gave my site a few below, if you like it, you'll be glad to sites in and make comments.
7/23/2010 7:45 PM | Sikiş

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

An open-source Facebook application for sharing, syndicating and promoting articles, links, podcasts and vodcasts.thanks
7/26/2010 4:12 AM | sikiş izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

thank you very much for post. i will come back.
7/27/2010 7:43 AM | film izle

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

This is an excellent topic you are discussing about and i really appreciate it. It should be going on.
7/27/2010 10:39 PM | Used Cars for Sale

# re: How to do a cross-database LINQ-to-SQL join (to avoid the “the query contains references to items defined on a different data context.” InvalidOperationException)

I really loved reading your blog. It was very well authored and easy to understand. Unlike additional blogs I have read which are really not good. I also found your posts very interesting. In fact after reading, I had to go show it to my friend and he enjoyed it as well!
7/27/2010 10:40 PM | raft wars

Post Comment

Title  
Name  
Url
Comment   
Protected by Clearscreen.SharpHIPEnter the code you see: