How do you use Linq to connect tables in different databases?

Question

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!

I have a table Table1 in database DB1, which has a "pseudo" foreign key Table2ID to table Table2 in database DB2, on the same server. "Pseudo", because obviously I can't have an actual FK spanning two databases.

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 Table1 object to have a relationship to Table2, just like it has relationships with all the "real" foreign key-related objects in DB1. But I can't bring Table2 into my db diagram, because it's in the wrong DB.

To synthesize this, I tried creating a view Table2 in DB1, which is simply select * from DB2..Table2. Aha, now I can drop a Table2 object into my diagram. I can even make a parent/child relationship between Table1 and Table2. But when I look at the generated code, Table1 still has no relationship to Table2, which I find most perplexing.

Am I missing a step somewhere? Is there a better/recommended way of doing this?

Thanks!

---

Later...

Along the lines of what one person suggested, I tried filling in the partial class of Table1 with all the methods required to access Table2, by copying all the structures for a related object within the same DB.

This actually worked for reads, but as soon as I tried to update or insert a record, I got an exception:

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.

So it looks like the designers of Linq have actually thought about this scenario, and decided that you are not allowed to connect objects in different databases. That's really a shame... :(

---

... and even later...

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 Table2 and set it on the new record Table1, then commit changes, it tries to insert a new record into Table2, which obviously causes a PK violation. Any idea why this happens, and how to get around it?

Answer 1

You could create two dbml's, one for each db. Then join the tables in your query:

var tb1 = DataContext1.Table1
var tb2 = DataContext2.Table2

var result = (from t1 in tb1
              join t2 in tb2 on tb1.column equals tb2.column
              where ...
              select ...
             )

You could also set tb2 = to your view rather than another datacontext...

Answer 2

Assuming you can access one database from the other you can do this by manually editing the .dbml file.

<Table Name="Table1.dbo.Table" Member="MemberObject"> 
<Table Name="Table2.dbo.Table" Member="MemberObject">

You might actually be able do this by looking at the properties of a table and changing the source.

Answer 3

Views don't have primary keys (without it the O/R designer can't create the relationship). Your solution to use a view gets you halfway there.... The step you are missing is setting the "Primary Key" property to true in the O/R designer for the key field in the Table2 view. You still have to create the association manually, but once you save the dbml the relationship will show up in the generated code.