StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do you use Linq to connect tables in different databases?
text
Body
copied!<p>I'm a bit of a Linq newbie, and I couldn't find any documentation to help me with what seems to be a pretty trivial problem - so your help will be much appreciated!</p> <p>I have a table <code>Table1</code> in database <code>DB1</code>, which has a "pseudo" foreign key <code>Table2ID</code> to table <code>Table2</code> in database <code>DB2</code>, on the same server. "Pseudo", because obviously I can't have an <em>actual</em> FK spanning two databases.</p> <p>Now I'm playing around with the O/R designer, and I love the way all the relationships are generated when I bring database objects into the designer... very cool! And I want my <code>Table1</code> object to have a relationship to <code>Table2</code>, just like it has relationships with all the "real" foreign key-related objects in <code>DB1</code>. But I can't bring <code>Table2</code> into my db diagram, because it's in the wrong DB.</p> <p>To synthesize this, I tried creating a view <code>Table2</code> in <code>DB1</code>, which is simply <code>select * from DB2..Table2</code>. Aha, now I can drop a <code>Table2</code> object into my diagram. I can even make a parent/child relationship between <code>Table1</code> and <code>Table2</code>. But when I look at the generated code, <code>Table1</code> still has no relationship to <code>Table2</code>, which I find most perplexing.</p> <p>Am I missing a step somewhere? Is there a better/recommended way of doing this?</p> <p>Thanks!</p> <hr> <h2>Later...</h2> <p>Along the lines of what one person suggested, I tried filling in the partial class of <code>Table1</code> with all the methods required to access <code>Table2</code>, by copying all the structures for a related object within the same DB.</p> <p>This actually worked for reads, but as soon as I tried to update or insert a record, I got an exception:</p> <p><code>An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.</code></p> <p>So it looks like the designers of Linq have actually thought about this scenario, and decided that you are <strong>not allowed</strong> to connect objects in different databases. That's really a shame... :(</p> <hr> <h2>... and even later...</h2> <p>Thanks to @williammandra.com, I found that you need to create the primary key on a view manually. But there's still another problem: for some reason when you load a value from the view <code>Table2</code> and set it on the new record <code>Table1</code>, then commit changes, it tries to insert a <strong>new</strong> record into <code>Table2</code>, which obviously causes a PK violation. Any idea why this happens, and how to get around it?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload