Entity Framework - Single EMDX Mapping Multiple Database
- by michaelalisonalviar
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 ServerStep 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 fromSELECT [columns] FROM LookupDB.dbo.TableLookup1toSELECT [columns] FROM TableLookup1Syntax: CREATE SYNONYM TableLookup1(1) FOR LookupDB.dbo.TableLookup1 (2)1. What you want to call the table on your other DB2. DataBaseName.schema.TableNameStep 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 merging2. First project will contain the edmx file pointing to the AccountingDB3. Second project will contain the edmx file pointing to the LookupDB4.
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!
