1/12/2015 9:46:18 PM

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.

//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