Entity Framework - Single EMDX Mapping Multiple Database


Because of my recent craze on Entity Framework thanks to Sir Humprey, I have continuously searched the Internet for tutorials on how to apply it to our current system.


So I've come to learn that with EF, I can eliminate the numerous coding of methods/functions for CRUD operations, my overly used assigning of connection strings, Data Adapters or Data Readers as Entity Framework will map my desired database and will do its magic to create entities for each table I want (using EF Powertool) and does all the methods/functions for my Crud Operations.


 But as I begin applying it to a new project I was assigned to, I realized our current server is designed to contain each similar entities in different databases. For example Our lookup tables are stored in LookupDb, Accounting-related tables are in AccountingDb, Sales-related tables in SalesDb. My dilemma is I have to use an existing table from LookupDB and use it as a look-up for my new table. 


Then I have found Miss Rachel's Blog (here)Thank You Miss Rachel!  which enables me to let EF think that my TableLookup1 is in the AccountingDB using the following steps.


 Im on VS 2010, I am using C# , Using Entity Framework 5, SQL Server 2008 as our DB Server



Step 1:

Creating A SQL Synonym. If you want a more detailed discussion on synonyms, this was what i have read -> (link here). To simply put it, A synonym enabled me to simplify my query for the Look-up table when I'm using the AccountingDB from


SELECT [columns] FROM LookupDB.dbo.TableLookup1


to

SELECT [columns] FROM TableLookup1


Syntax: CREATE SYNONYM  TableLookup1(1) FOR LookupDB.dbo.TableLookup1 (2)


1. What you want to call the table on your other DB

2. DataBaseName.schema.TableName


Step 2:


We will now follow Miss Rachel's steps. you can either visit the link on the original topic I posted earlier or just follow the step I made.


1. I created a Visual Basic Solution that will contain the 4 projects needed to complete the merging

2. First project will contain the edmx file pointing to the AccountingDB

3. Second project will contain the edmx file pointing to the LookupDB

4. Third Project will will be our repository of the merged edmx file. Create an edmx file pointing To AccountingDB as this the database that we created the Synonym on.

Reminder: Aside from using the same name for the Entities, please make sure that you have the same Model Namespace for all your Entities 

5. Fourth project that will contain the beautiful EDMX merger that Miss Rachel created that will free you from Hard coding of the merge/recoding the Edmx File of the third project everytime a change is done on either one of the first two projects' Edmx File.

6. Run the solution, but make sure that on the solutions properties Single startup project is selected and the project containing the EDMX merger is selected.

7. After running the solution, double click on the EDMX file of the 3rd project and set Lazy Loading Enabled = False. This will let you use the tables/entities that you see in that EDMX File.

8. Feel free to do your CRUD Operations.

I don't know if EF 5 already has a feature to support synonyms as I am still a newbie on that aspect but I have seen a linked where there are supposed suggestions on Entity Framework upgrades and one is the "Support for multiple databases"


 So that's it! Thanks for reading!





Print | posted on Saturday, September 8, 2012 1:25 AM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)