EF4 cross database relationships

Question

Hello all,

I was wondering if EF4 support cross-databse relationships? For instance:

db1

Author
  Id
  Name

db2

Posts
  Id
  Content
  db1.Author.Id

What ideally I need to do to get this relation in my ef4 model?

Do you guys have any idea?

Thanks

Answer 1

I recently began a project that uses entity framework with two databases, one Oracle and one SQL Server. I could not find any information regarding cross-database or multiple database support in the entity framework.

Most posts from the MS Entity framework team are a couple of years old and indicate that including two databases in a single model is not a feature that will be included soon. I would be interested in having a concrete answer on whether it was included in 2010 myself although I suspect the answer is no.

Currently out project gets around this limitation by having a separate entity model for each database. This has solved the problem for the majority of the scenarios we've encountered thus far in the project.

In cases where we've needed to query the data from the two databases at the same time, we simply created a view in one or the other databases. Since we're using Oracle and SQL Server, this view would utilize either a Linked Server (SQL) or a DBLink (Oracle).

The disadvantage of views in the entity framework is we've had to spent more time than I expected getting the primary keys working.

Hope this helps.

Answer 2

I've found this entry in Microsoft Connect that answers the question about the support given at this moment by EF (actually it is not supported yet):

https://connect.microsoft.com/VisualStudio/feedback/details/375866/entity-framework-support-across-multiple-databases?wa=wsignin1.0

Also found a thread in Social MSDN about this concern:

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/cad06147-2168-4c20-ac23-98f32987b126/?prof=required

Other links in stackoverflow:

• http://stackoverflow.com/questions/610197/ado-net-entity-framework-across-multiple-databases
• http://stackoverflow.com/questions/2659942/entity-framework-4-and-multiple-database/2788108

In summary, the only given alternatives are:

1. Using views in EF: http://smehrozalam.wordpress.com/2009/08/12/entity-framework-creating-a-model-using-views-instead-of-tables/

2. Use NHibernate instead: http://mikehadlow.blogspot.com/2008/10/mapping-entities-to-multiple-databases.html

I wish Microsoft catches the token and invest in this feature for future releases

Rafa Ortega MAP 2010