EF4 Part 5: Preparing for Multiple Databases

The main driving factor for us using EF4 in our application is so we can easily support multiple databases (please refer to EF4 Part 1): we currently use Microsoft SQL, and we have a client that wants to use Oracle.

Although multiple provider support is used to promote EF4, there is surprising little about that provides a clear guide on how to achieve this goal.

Adjusting Your Solution

Assuming you already have a solution setup aimed at one particular database provider, only minor adjustment is required to ‘slot in’ the extra provider support.

For an excellent step-by-step coverage of this, have a look at Multiple database support with Entity Framework by Moses.

To summarise, you just need to add an additional SSDL file per provider to your model project:

image And then define your provider in that file, e.g. assuming you are trying Devart’s dotConnect for Oracle 6.00 Beta, you would have:

image

You now need to adjust your build process to include this additional file: we have a separate assembly that references the individual files generated from the model, and embeds them…so we just need to Add Existing Item on that project, locate the new file we created above, and select Add As Link

image

We then change the Build Action to Embedded Resource, but you may decide to have it Copy to Output Directory.

image The final step in adjusting the wire up in the solution between projects, is setting the connection string: when having multiple layers and class libraries, etc. if you have multiple stores embedded (as we now do), you need to be explicit with your definitions.  So in our web application, we adjust our connection string to clearly defined the assembly and namespace per embedded file:

image So if you want to use the newly added Oracle one, you’d obviously adjust the filename shown above from SoniaModel.ssdl to SoniaModel.Oracle.ssdl, and also adjust the provider setting.

Additional SSDL Content

So, you’ll have noticed I totally skipped any discussion above on generating the actual content of the new SSDL files?  That’s because this is currently the more tedious aspect of this whole process :-(

We had originally thought we could simply right click on our model, and Generate Database picking our desired provider and it would generate a script to create the database and also provide us with the appropriate store content for our new SSDL file. However, at this point in time we have a road-block with that approach:-

  • Oracle have put out a Statement of Direction, but currently nothing to try/use
  • DataDirect have a provider, but do not seem to have time to respond to our multiple requests for information
  • Devart also have a provider that you can download and try (and see the cost), but it currently fails when generating from our model (tries to create a complex type in the store where the length of the type name and inner component exceed 30 characters in length, and therefore violates Oracles 30 character maximum for naming).  I have a support ticket open on this one, so hopefully we might get a resolution…

The alternative is far more manual:-

  1. Create new database along with schema: either manually or via one of the many tools that exist where you can provide a SQL database as the source and have it spit out an Oracle database (no double entendre intended on the use of the word spit…honest!).
  2. Copy the SQL SSDL content into your new SSDL file, and replace any unsupported types with the supported ones (thank goodness for Ctrl+H)

The only issue we experienced with step 1 was that Oracle has a naming restriction of 30 characters maximum; we have a few instances that were longer (for clarity), but we had to suck-it-up and rename with shorter versions (you could retain longer names in your model and the SQL store, but we wanted to keep the consistency across-the-board).

I would also suggest that only having had one real issue with this was because we had ensured that our database was only tables/relationships/indexes.  We avoided views, stored procedures, database defaults, triggers, etc.  Anything we could do within the conceptual model, we did…keep the actual database itself simple!

Conclusion

Although we can technically support multiple databases, the current lack of automation (or issues with) means that anything store related becomes tedious and manual.  I would imagine this will only improve, but for the moment you need to factor such duplication of effort into your estimates when needing to make any store based adjustments.

Print | posted on Saturday, 4 September 2010 10:27 AM

Feedback

# Moses - Multiple database support with Entity Framework

left by Pingback/TrackBack at 26/09/2010 11:12 AM Gravatar
Moses - Multiple database support with Entity Framework

# re: EF4 Part 5: Preparing for Multiple Databases

left by Riya at 28/10/2010 12:11 AM Gravatar
I have followed all things that you said. Thanks.

# re: EF4 Part 5: Preparing for Multiple Databases

left by Yippr at 5/02/2011 10:18 PM Gravatar
greatly explained, thanks

# re: EF4 Part 5: Preparing for Multiple Databases

left by Evhen at 9/06/2011 6:10 PM Gravatar
After Entity Framework 4.1 release you may use Code-First approach instead of creating and using several XML mapping files. You can find the example of an application working with Microsoft SQL Server, as well as with Oracle, MySQL, PostgreSQL and SQLite using Devart Entity Framework providers here:
www.devart.com/.../...l-postgresql-and-sqlite.html

# re: EF4 Part 5: Preparing for Multiple Databases

left by research paper help at 4/07/2011 3:05 PM Gravatar
I already set-up a solution for my database provider but I have not defined it in file. I'm glad to see your post, I'm now done creating the framework for my database.

# re: EF4 Part 5: Preparing for Multiple Databases

left by radio city rockettes tickets at 20/07/2011 6:19 PM Gravatar
I had really liked the substance very much. I had also book marked this site for my potential use. Thanks a lot for the superb information. I had liked it very much.

# re: EF4 Part 5: Preparing for Multiple Databases

left by PR7 Backlinks at 20/08/2011 10:01 PM Gravatar
Thanks for a marvelous posting! I really enjoyed reading it, you are a great author.I will ensure that I bookmark your blog and will often come back at some point. I want to encourage yourself to continue your great job, have a nice evening!

# re: EF4 Part 5: Preparing for Multiple Databases

left by Van Insurance at 25/08/2011 7:55 AM Gravatar
Hey buddy, good morning. Interesting post. You have gained a new fan. Please keep up the good work and I look forward to more of your amazing posts.

# re: EF4 Part 5: Preparing for Multiple Databases

left by Property management UK at 15/09/2011 8:32 PM Gravatar
I really liked the post. This is Excellent one!

# re: EF4 Part 5: Preparing for Multiple Databases

left by HBK at 17/09/2011 2:32 AM Gravatar
I'm having SQL Server and Oracle databases having tables with same schema. I followed all the steps you have given. When I change the connection string to oracle db in Web.Config file of my ASP.NET Dynamic Data Entities application and ran it, I'm getting "An error occurred during model registration and this operation cannot be performed.".

However, when the connection string is pointing to SQL Server database, it works fine.

What could be the reason.

# re: EF4 Part 5: Preparing for Multiple Databases

left by click here at 21/09/2011 7:38 PM Gravatar
I was setting up my database solutions provider, but I have not specified the file. I am glad to see your message, now I've done by creating a framework for my database.

#  Preparing for Multiple Databases

left by Norgesautomaten at 23/09/2011 5:00 PM Gravatar
but at the moment is to create our model (to try to create a complex type of stores, where the name of the type and length of the domestic component more than 30 characters, and therefore violates the Oracles of the names Max 30 characters). I opened a support request for this, so hopefully we'll get the Resolution ...

# locksmith

left by Locksmiths Hartford at 26/09/2011 5:23 PM Gravatar
Top class support; I am going to certainly return to your blog for further related information.

# re: EF4 Part 5: Preparing for Multiple Databases

left by Essay help at 4/10/2011 10:20 PM Gravatar
So informative things are provided here,I really happy to read this post,I was just imagine about it and you provided me the correct information I really bookmark it,for further reading,So thanks for sharing the information.

# re: EF4 Part 5: Preparing for Multiple Databases

left by essays help at 4/10/2011 10:44 PM Gravatar
Microsoft SQL,is support multiple databases It's so interesting.Multiple database support with Entity Framework i inspire to find this information its helped me in my work.

# re: EF4 Part 5: Preparing for Multiple Databases

left by write my essay at 6/10/2011 11:52 PM Gravatar
I’m now following your blog & I‘ll follow your all blogs for that kind of interesting blogs.

# re: EF4 Part 5: Preparing for Multiple Databases

left by Online Listings at 12/10/2011 12:04 AM Gravatar
I must say that overall I am really impressed with this blog.It is easy to see that you are impassioned about your writing. I wish I had got your ability to write. I look forward to more updates and will be returning…

# re: EF4 Part 5: Preparing for Multiple Databases

left by Plumber Hitchin at 21/10/2011 5:14 PM Gravatar
I recommend my all fellows to read this post.It,s simplt the best.

# re: EF4 Part 5: Preparing for Multiple Databases

left by essay writing help at 29/10/2011 6:37 PM Gravatar
Its really very best and useful site for all,I am using it from a long time,I really got many information from this site.

# re: EF4 Part 5: Preparing for Multiple Databases

left by Plumber Baldock at 1/11/2011 4:38 PM Gravatar
Really nice and informative post.I really like to read such great and helpful posts.Keep sharing such posts

# re: EF4 Part 5: Preparing for Multiple Databases

left by kostenlose automatenspiele at 5/11/2011 1:23 AM Gravatar
What a wonderful tutorial. This was just what I have been looking for.

# re: EF4 Part 5: Preparing for Multiple Databases

left by kostenlose automatenspiele at 5/11/2011 2:41 AM Gravatar
excellent tutorial mucho appreciated!

# re: EF4 Part 5: Preparing for Multiple Databases

left by Frameless Glass Office Partition at 25/12/2011 2:11 PM Gravatar
So nice and helping solution.

# re: EF4 Part 5: Preparing for Multiple Databases

left by water heater in at 12/01/2012 12:53 PM Gravatar
Oh I just want to ask if how many database are we going to use for a big company like ours?

# eBay: Toy Store Online

left by Toys at 1/03/2012 8:43 PM Gravatar
Thanks for the information, I'll visit the site again to get update information

# re: EF4 Part 5: Preparing for Multiple Databases

left by tennis divider netting at 11/03/2012 3:14 PM Gravatar
This is a great tutorial for Framework platform and I'm really learning a lot

# re: EF4 Part 5: Preparing for Multiple Databases

left by PMP Training at 12/03/2012 6:18 AM Gravatar
thanks for the help. I was about to prepare my own databases. Thanks a lot for that.

# shox pas cher

left by benty at 12/03/2012 6:12 PM Gravatar
C'est très étonnant et blog de l'information fournie par l'article de ce blog est vraiment sympa et utile et je voudrais visiter le blog à nouveau....

# shox pas cher

left by benty at 12/03/2012 6:37 PM Gravatar
Utilisez EF4 peut facilement prendre en charge plusieurs bases de données dans une application en utilisant le Microsoft SQL maintenant, tellement plus commode, plus efficace d'utiliser un ordinateur.

# re: EF4 Part 5: Preparing for Multiple Databases

left by lunettes de soleil chanel at 13/04/2012 7:23 PM Gravatar
C'est très étonnant et blog de l'information fournie par l'article de ce blog est vraiment sympa et utile et je voudrais visiter le blog à nouveau.... wow...

# Chaussures nike shox femme

left by sweeterjan at 26/05/2012 3:18 PM Gravatar
Utilisez EF4 peut facilement prendre en charge plusieurs bases de données dans une application en utilisant le Microsoft SQL maintenant, tellement plus commode, plus efficace d'utiliser un ordinateur.

# re: EF4 Part 5: Preparing for Multiple Databases

left by YMCMB kappe at 31/05/2012 1:07 PM Gravatar
database defaults, triggers, etc. Anything we could do within the conceptual model, we did…keep the actual database itself simple!

# re: EF4 Part 5: Preparing for Multiple Databases

left by Forfait mobile at 1/06/2012 7:18 AM Gravatar
I had really liked the substance very much. I had also book marked this site for my potential use. Thanks a lot for the superb information. I had liked it very much.

# re: EF4 Part 5: Preparing for Multiple Databases

left by jak poderwac dziewczyne at 21/06/2012 11:59 PM Gravatar
Thanks for this tutorial, it helped me solve my problem that I have been struggling with for 2 days now

# 1

left by term-paper-writer review at 10/07/2012 7:59 PM Gravatar
great) liked everything very much) keep it up and dont stop)

# re: EF4 Part 5: Preparing for Multiple Databases

left by house and lot for sale in cebu at 29/07/2012 10:45 PM Gravatar
Actually that currently use Microsoft SQL, was important and i really appreciate those because it been help me a lot. Now a days its better to use our technology because they are the one who help us in our daily needs. We can work much easier and faster and we can also make money through them.

# re: EF4 Part 5: Preparing for Multiple Databases

left by montreal web design company at 30/07/2012 12:26 PM Gravatar
This will improved my skills in operating a multiple database because you provide here a manual for the moment that a student like me, find difficulties in this content. Thank you for making us easy to understand.

# re: EF4 Part 5: Preparing for Multiple Databases

left by pmp exam at 31/07/2012 5:03 AM Gravatar
creating of multiple databases seems to be very easy but it's absolutely difficult. I have already tried making these but i couldn't really make it without the help of the other people. Now I already understand some too.

# re: EF4 Part 5: Preparing for Multiple Databases

left by loi ethylotest obligatoire at 21/08/2012 7:50 PM Gravatar
Excellent post mate! I can't believe I didn't read that earlier. It really helped me for my database. Thank you!

# re: EF4 Part 5: Preparing for Multiple Databases

left by 5 star hotel paris at 28/08/2012 8:19 PM Gravatar
This was really nice ! thank you for sharing this post with us.

# re: EF4 Part 5: Preparing for Multiple Databases

left by luxury hotel at 29/08/2012 9:19 PM Gravatar
I think your post is great !

# re: EF4 Part 5: Preparing for Multiple Databases

left by PMP Seattle at 30/08/2012 3:57 PM Gravatar
Thank you for giving as tips. These little tips will be the way to go through this briefly instructions on how to use the productivity features of window.

# re: EF4 Part 5: Preparing for Multiple Databases

left by london 5 star hotel at 30/08/2012 9:10 PM Gravatar
Thank you for your professional approach !

# re: EF4 Part 5: Preparing for Multiple Databases

left by pmp exam sample question at 3/09/2012 3:17 PM Gravatar
These are perfect instructions that people expected to have. Thank you for keeping this secured, updated and personalizes the install that ready to add some working software.

# re: EF4 Part 5: Preparing for Multiple Databases

left by help with sas at 5/09/2012 4:13 PM Gravatar
Thanks for advising and getable post, acutely in your web log accumulated is good. I do acquire your authentic the deftness to argue this tender of article.

# chaussures nike shox

left by chaussures at 7/09/2012 7:03 PM Gravatar
Gen divès kalite posts nan egzistans tou pre sa a, mwen kwè pran gen referans ta ka eksperyans te fè plas sa a oswa atik nan vrèman enfòmatif. Ekspresyon pratik objektif sa a post se bon jan kalite pòv yo. Senpleman mwen ka fè konnen nan lefèt ke info bay la te inik, senpleman vrèman fè li pi pre konplè, sipòte ak enfòmasyon ansyen pral jwenn te aktyèlman bon. Pwen yo ou te manyen ki nan lis isit la yo se vital, konsa Kite m 'tach anpil nan enfòmasyon an isit la yo bati sa sa a aktyèlman gwo pou antyèman Senior Member la nan isit la. Mèsi anpil pou enfòmasyon sa. Aktyèlman itil!

# chaussures nike shox

left by liu at 7/09/2012 7:05 PM Gravatar
Dankie vir die maak van die betroubare poging om 'n verduideliking te gee vir hierdie. Ek voel baie sterk ongeveer en wil meer ingelig. As dit is OK, as jy ekstra intensiewe wysheid bereik, kan jy gedagtes voeg ekstra poste baie soortgelyk aan hierdie een met meer inligting? Dit kan wees buitengewoon nuttig en nuttig is vir my en my kollegas.

# chaussures nike shox

left by liu at 7/09/2012 7:07 PM Gravatar
Ideerne du gav her vil jeg diskutere yderst værdifuld. Det viste sig en virkelig behagelig overraskelse at erhverve, at foregribe mig efter jeg vågnede i dag. De er hele tiden på scenen og let at være opmærksom på. Tak masser at de værdifulde ideer, du har fået delt nedenfor.

# chaussures nike shox

left by liu at 7/09/2012 7:24 PM Gravatar
On mitmeid postitusi olemasolu lähedal Usun, võttes seal viide võiks saadud kogemusi selle koha või eseme tõesti informatiivne. Praktiline eesmärk väljendus see postitus on halva kvaliteediga. Lihtsalt ma ei hääldada asjaolu, et info siin esitatud oli ainulaadne, vaid tõesti muuta see lähedal lõpule, toetades endiste infot saada olnud tegelikult hea.Punktid, mida on puudutanud siin loetletud on oluline, seega lubage mul märgata paljud info siin ehitada see tegelikult suurepärane täiesti algaja on siin. Suur tänu selle info. Tegelikult kasulik!

# re: EF4 Part 5: Preparing for Multiple Databases

left by http://www.lumina-fx.com/ at 12/09/2012 7:56 PM Gravatar
That was a great article. I think it seems to be interesting this kind of topic. You have smart idea about that software and nice information. Thank's for your blog.

# re: EF4 Part 5: Preparing for Multiple Databases

left by thania at 24/09/2012 6:09 PM Gravatar
I feel like I’m often looking for interesting things to read about a variety of subjects, but I manage to include your blog among my reads every day because you have interesting entries that I look forward to.
tefl courses

# re: EF4 Part 5: Preparing for Multiple Databases

left by elisha at 25/09/2012 6:54 PM Gravatar
Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative.
PMP Seattle

# re: EF4 Part 5: Preparing for Multiple Databases

left by alvin at 2/10/2012 6:35 PM Gravatar
Good – I should definitely say I’m impressed with your blog. I had no trouble navigating through all the tabs as well as related info. The site ended up being truly simple to access. Excellent job..

pdu project management

# re: EF4 Part 5: Preparing for Multiple Databases

left by dorchester hotel at 4/10/2012 7:54 PM Gravatar

Thank you for every other informative website. Where else may I am getting that type of info written in such a perfect way? I’ve a mission that I am simply now operating on, and I’ve been on the glance out for such information.

# re: EF4 Part 5: Preparing for Multiple Databases

left by alvin at 8/10/2012 6:52 PM Gravatar
Thanks, its a very very good information of sharing and carrying out folks learn about the routines which can be currently being completed.I experience I can uncover further useful data appropriate right here, several thank you.
http://www.lumina-fx.com/

# re: EF4 Part 5: Preparing for Multiple Databases

left by caleb at 9/10/2012 7:52 PM Gravatar
That is quite especial and quite great to notice that we have such a chance to get over all the obstacles and enjoy interpreting one’s ideas and thoughts which could be of eminent help and value in our day-after-day life.

Visit their website

# re: EF4 Part 5: Preparing for Multiple Databases

left by air max chaussures at 10/10/2012 6:25 PM Gravatar
The ideas you provided here i will discuss extremely precious. It proved a really pleasurable surprise to acquire that anticipating me after i awoke today. They’re constantly to the stage and easy to be aware of. Thanks plenty to the valuable ideas you’ve got shared below.

# re: EF4 Part 5: Preparing for Multiple Databases

left by Hydroxycut Reviews at 23/10/2012 3:01 AM Gravatar
This is beyond doubt a blog significant to follow. You’ve dig up a great deal to say about this topic, and so much awareness. I believe that you recognize how to construct people pay attention to what you have to pronounce, particularly with a concern that’s so vital. I am pleased to suggest this blog.

# re: EF4 Part 5: Preparing for Multiple Databases

left by Visit the site at 7/11/2012 10:30 PM Gravatar
I whole multiple database idea and the driving factors towards success seems really nice to me .You have provided the summary in an easily understandable and yet informative way.I really liked your way of presentation.

# re: EF4 Part 5: Preparing for Multiple Databases

left by ac repair los angeles at 9/11/2012 4:06 AM Gravatar
triggers, etc. Anything we could do within the conceptual model, we did…keep the actual database itself simple!

# re: EF4 Part 5: Preparing for Multiple Databases

left by logo design uk at 10/11/2012 7:00 AM Gravatar
This is a really good site post, im delighted I came across it.

# re: EF4 Part 5: Preparing for Multiple Databases

left by dramatic turn at 12/11/2012 8:18 PM Gravatar
Assuming you already have a solution setup aimed at one particular database provider, only minor adjustment is required to ‘slot in’ the extra provider support.

# re: EF4 Part 5: Preparing for Multiple Databases

left by 5 star hotel new york at 4/12/2012 5:41 PM Gravatar
Thanks plenty to the valuable ideas you’ve got shared below.

# re: EF4 Part 5: Preparing for Multiple Databases

left by pc graphics card at 16/12/2012 8:41 AM Gravatar
This is such a great website that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource for free.

# re: EF4 Part 5: Preparing for Multiple Databases

left by my chota bheem games at 2/04/2013 1:32 AM Gravatar
Thank you for taking the time to discuss this, I feel strongly about it and love learning more on this topic. If possible, as you gain expertise, would you mind updating your blog with more information?

# Cheap Nike Free Run

left by Cheap Nike Free Run at 3/04/2013 11:06 AM Gravatar
Your blog is indeed intriguing, notable and genuinely educational merit!

# re: EF4 Part 5: Preparing for Multiple Databases

left by medlifepro at 13/07/2013 6:02 PM Gravatar
I have the same factor as you using EF4 in my application )

# re: EF4 Part 5: Preparing for Multiple Databases

left by Catholic marriage preparation co at 25/07/2013 10:30 PM Gravatar
This blog proved very helpful for me indeed.

# oil boiler

left by oil boiler at 24/08/2013 7:40 PM Gravatar
Well, very good post with informative information. I really appreciate the fact that you approach these topics from a stand point of knowledge and information. This is the first time, I visited at your site and became your fan. You are bookmarked. Please keep on posting.

# re: EF4 Part 5: Preparing for Multiple Databases

left by Steel Distributors at 20/11/2013 8:12 PM Gravatar
I'm excited to discover this page. I want to to thank you for ones time just for this wonderful read!! I definitely really liked every part of it and I have you saved as a favorite to look at new stuff on your blog
Title  
Name
Email (never displayed)
Url
Comments   
Please add 8 and 2 and type the answer here: