Entity framework 4 and multiple database.

Question

Something changes or it still not support this? For example join database1.dbo.Users and database2.dbo.Addresses

Answer 1

Entity Framework has always supported multiple database backends - so nothing has changed in that respect with Entity Framework 4.

See this blog post by the ADO.NET team on third-party EF support - from August 2008 - they list amongst others:

• Oracle
• MySQL
• PostgreSQL
• SQLite
• Firebird
• IBM DB2
• Informix Dynamic Server (IDS)
• OpenLink Virtuoso
• Ingres
• Sybase
• Progress
• SQL Anywhere
• VistaDB

Answer 2

I think what ais asked is if you can join tables from different databases, not different providers, resulting in one entity mapped to two or more tables or views from different databases.

If you think about it, when you create a EDM model with Visual Studio it ask you to give an existing database, and when finished creating the model, it generates an EF connection string, that internally address to the given underlying database connection string.

E.g: metadata=res:///EFTestModel.csdl|res:///EFTestModel.ssdl|res:///EFTestModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\;Initial Catalog=EFTest;Integrated Security=True;MultipleActiveResultSets=True"*

So each model matches only a database, only a connection string.

EF4 still does not support creating one conceptual model which works with N storage models. At least this is not supported with any built-in provider. Perhaps in the future this could be done through a new provider that combines the support of many storages (from the same providers or different).

I havent done enough research on it, but perhaps Windows Server AppFabric (Codename Velocity) could be the bridge to go through this gap.

Note: I have tried even editing manually the xml for the EDM (edmx) to insert a second element inside the <edmx:StorageModels> tag but it does not match the EDM XML Schema so VS warns about it: Error 10021: Duplicated Schema element encountered.

Rafa Ortega MAP2010