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

Question

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:

Use normal SQL commands for DB2 and populate viewmodels with them. (I only have read-only access on DB2)
Create SQL views in the DB1 and generate EDMX.
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.

you can always select no. of tables in Database-First Approach ... because its a separate database i would recommend firing sql query directly against that database using context.database.sqlquery<T>(query,params) — Vishal Sharma
– Vishal Sharma, Commented Oct 5, 2013 at 6:01

Ester Kaufman · Accepted Answer · 2017-06-26 13:54:26Z

1

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);

edited Jun 26, 2017 at 13:54

Ester Kaufman

88813 silver badges20 bronze badges

answered Oct 5, 2013 at 6:49

Code Uniquely

6,3834 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HBhatia Over a year ago

Thanks, I will try this. What will be the difference If go with code first for DB1

Code Uniquely Over a year ago

You MUST specify the POCO's, all the relationships and constraints in code (most likely you'll need to use fluent) that provide the existing DB1 structure and functionality. In this case that's probably work you don't really need to do as you have DB1 already. There are some tools that take a DB First and give you a leg up to a new version using Code First. There is no effect on DB2 if you wish to stick with importing that one.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related