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:

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:

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. 

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

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:

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.