.NET Entity Framework: Reference Tables from Different Databases

The .NET Entity Framework has become a great ORM tool. I especially like it from a Code First perspective. But one issue I have had with it is creating a single data context that represents tables from different databases. It does not handle this situation well.

Unfortunately, many jobs you get, will have this scenario. I recently discovered an incredibly easy solution. Database VIEWS.

Now I have only tried this with a single database server (it might work with a link server) with mutliple databases. But if you create a View in the database you are pointing your EF data context to, that respresents a table in a different database, EF will treat it as if it is a local table. Its simple and beautiful.

copy
//my data context
public class MyDbContext : DbContextBase
{
	...
	//primary db table
	public DbSet<Entities.User> Users { get; set; }
	//secondary db table
	public DbSet<Entities.UserLoginToken> UserLoginTokens { get; set; }

	protected override void OnModelCreating(DbModelBuilder modelBuilder)
	{
		//User
		modelBuilder.Entity<Entities.User>().ToTable("Users");			

		//UserLoginToken - pointing to a VIEW
		modelBuilder.Entity<Entities.UserLoginToken>().ToTable("v_UserLoginTokens");
	}        
}

//create view
CREATE VIEW [dbo].[v_UserLoginTokens]
AS

SELECT * FROM MyOtherDB.dbo.UserLoginTokens