CODEWITHSUNDEEP
asp.net-mvcentity-framework
HBhatia
HBhatia

Reputation: 565

Using Specific table/s of a different Database in Entity Framework MVC

I have been searching on different threads but was unable to find an answer which can give me clear picture.

Here is the scenario:

I am creating an application using MVC & Entity framework DB first . I have two databases, DB1 is dedicated DB for my application and DB2 is a huge common Database getting used by many other applications. I will only have to use specific tables/views from DB2.

Now following are the approches I can go with:

  1. Use normal SQL commands for DB2 and populate viewmodels with them. (I only have read-only access on DB2)
  2. Create SQL views in the DB1 and generate EDMX.
  3. Add DB2 in the context of my application.

I dont want to go with Approach#3 as I mentioned DB2 is huge and I only need to use couple of tables from DB2.

Also, Just in case I want to go with code first approach for DB1 what is the best solution in that case.

Upvotes: 2

Views: 2494

Answers (1)

Code Uniquely
Code Uniquely

Reputation: 6373

I often have projects with three of more models because we have many databases.

I would create a two EDMX set-up with two distinct contexts. Create your application DB1 Context as normal then create an additional context and only pull in those tables that you are interested in from DB2.

To make your life easier in the long run and easier to maintain generally just create a DLL for each model so that it has its own namespace and that way you can distinguish between users in DB1 and users in DB2 for instance and add or remove entities from one without affecting the other.

Each DLL would have an app.config connection string that gets yo to your data, such as

<add name="DB1Entities" connectionString="metadata=res://*/DB1Model.csdl|res://*/DB1Model.ssdl|res://*/DB1Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(local);initial catalog=ClientDb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" /&gt;

<add name="DB2Entities" connectionString="metadata=res://*/DB2Model.csdl|res://*/DB2Model.ssdl|res://*/DB2Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(local);initial catalog=ClientMaster;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient"/&gt;

<add name="DefaultConnection" connectionString="Data Source=(local);Initial Catalog=Reports;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>

Just remember to copy each of the connection string from the Dll's App.config into your applications app.config or you web.config file for a site.

In your project reference the DLL's and then load your contexts.

DB1Entities DB1Context = new DB1Entities()
DB2Entities DB2Context = new DB2Entities()

You can now happily distinguish between DB1 and DB2 entities and use content from one in the other like this.

var address1 =  DB1Context.Addresses.Single(a => a.AddressId == 1);
var address2 =  DB2Context.Addresses.Single(a => a.Id == address1.GlobalAddressId);

Upvotes: 1

Related Questions