Cross database joins in Entity Framework4

Quite new to Entity Framework 4 I really struggled with the concept of not being able to make an Entity Data Model containing entities of tables that live across different multiple databases. I am still really quite disappointed that EF4 cannot support this and the team behind it doesn't even seem to think that cross database entities are an issue worth considering despite how long this forum has been asking for them.

Anyway, there is a cheeky little way that it can be done. I will use the example of a forum to illustrate this. I need to display posts alongside the username and email address of the contributor of that post. The problem is that the user information lives in a separate database from the post information.

Time to unleash my MS Paint skills to illustrate:

Solution

I got around this by creating a View inside the ForumDB like so:

USE ForumDB
CREATE VIEW dbo.vUserDetails
AS 
 SELECT
  u.UserID,
  u.UserName,
  c.Email
 FROM
  UserDB.dbo.UserNames u
 INNER JOIN
  UserDB.dbo.ContactInfo c ON c.UserID = u.UserID
GO

With this new View in the database I can now create an entity of it in the EDM. Go to your edmx file, right click the background and select 'Update Model from Database...'

Select the data connection to the ForumDB. Then when you are on the Choose Your Database Objects step, make sure you are on the Add tab and select Views and check the vUserDetails view:

You now have access to all your user data. You add an association to link the UserID from the Posts table (Post entity) to the UserID in your new view.

Further

You now have SELECT access to this user data but you cannot currently INSERT/UPDATE/DELETE users. In this scenario this is okay, however if you have a different objective you can still achieve this using stored procedure in the Entities to run on INSERT functions etc.