CODEWITHSUNDEEP
asp.net-mvcentity-framework
kaboom
kaboom

Reputation: 833

mvc entity framework many-to-many relationship of entities from multiple database

so I have 2 entities from 2 database. 'tblApp' is from Db1 and 'tblGroup' is from Db2 .How can I build many to many relationship of 2 tables from 2 databases

Currently I have

 public class EFDbContextDb1 : DbContext
    {
        public DbSet<Group> tblGroup { get; set; }
            // the problem is 'tblApp' is in the Db2 database
            // so I guess I should do something here
        public DbSet<App> tblApp{ get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {

            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Group>().ToTable("tblGroup");

            modelBuilder.Entity<App>()
               .HasMany(g => g.tblGroup)
               .WithMany(a => a.tblApp)
               .Map(m =>
               {
                   m.MapLeftKey("AppID");
                   m.MapRightKey("GroupID");
                   m.ToTable("AppGroup");
               });
        }

    }

public class EFDbContextDb2 : DbContext
    {
        public DbSet<App> tblsApp{ get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<App>().ToTable("tblApp");

            base.OnModelCreating(modelBuilder);
        }
    }

the connection string to connect with 'Db1' and 'Db2'

<connectionStrings>
    <add name="EFDbContextDb1" providerName="System.Data.SqlClient" connectionString="Data Source=Db1;Initial Catalog=Security;Integrated Security=true;" />
    <add name="EFDbContextDb2" providerName="System.Data.SqlClient" connectionString="Data Source=Db2;Initial Catalog=ServerStatus;Integrated Security=true;" />
  </connectionStrings>

Upvotes: 1

Views: 227

Answers (1)

Brad Christie
Brad Christie

Reputation: 101614

Short answer is you can't. EF can only deal with one context (and even schema) at a time.

However, you can:

  • Create a service layer (with its own set of entities) and place the joining logic there. And now the service entities become a collage of information pulled from multiple locations.
  • Create table-valued functions that call pull information from both locations and return the joined information in s format that one EF context would encompass in a single object.
  • Use something like Dapper.net and use synonyms in the database and query it to your own model.

Upvotes: 1

Related Questions