Associate tables from different databases - Entity Framework

6
votes

I'd like to know how to (if possible) associate tables from different databases using entity framework. I have one edmx for my "xyz" database and another for my "abc" I need to associate them using EF. I know how to fix this using FKs, but that's not what I want, I'd like to be able to do that using the visual editor, not adding, by hand, a FK to my DB.

Here's a pratical example of what I'd like to accomplish:

Table User - database: abc
Table Message - database: xyz

I'd like to associate User to Message and vice-versa.

Could anyone help me?

Thanks!

2
c#entity-framework
By the way you cannot create cross database FKs to my knowledge, nor by hands or visually. - Davide Piras
I know, that wouldn't actually be a FK. Would be a field "extID", for instance, in which I'd put the ID of the record from the other table. PS: but this is the workaround I thought of, there might probably be a better way to fix it. :D thanks for the comment. - eestein

2 Answers

3
votes

You can query them together with Linq-to-Objects, but not with L2S or L2E.

A context is wrapped around 1 Db-connection, you cannot track/update entites from multiple db's.

A possible workaround is to 'link' to one of the Db's from the other. MS-SQL can do this. To the EF it would appear 1 database.

1
votes

There is no way to achieve this using the edmx design surface in EF.

You could encapsulate this association within your domain model and provide a distributed transaction across the two EF Contexts. We've implemented the latter with an EF Context "container" class that given an internal collection of contexts uses a TransactionScope when it contains more than one context (this relies on the IUnitOfWork abstraction to work effectively).