Hello,
I am currently evaluating Entity Framework for migrating a commercial large-scale application to it. The application should be able to work against SQL Server and Oracle using the same code base.
Entity Framework seemed ideal in theory separating conceptual and data models until I realized that an EDMX file contains both models and each database provider generates its own code.
What I need?
1) Create an SQL Server EDMX file using the standard SQL Server driver that does not rely on the build-time schema owner. For god's shake, we are talking about commercial applications, its value can vary across customers! I read somewhere that it is enough to delete the attribute from the generated EDMX file, have not tested yet though.
2) I want to generate a dotConnect compatible EDMX file using the SQL Server EDMX file as a template. The idea is to use the same CSDL file (thus my code works for both databases) and load the appropriate SSDL and mapping files based on the database being used. Could this possibly be automated through an appropriate XSLT conversion?
Some rules for the XSLT that I have though of are:
a) Delete attributes with name schema (to solve 1).
b) Convert entity, property and association names in SSDL to uppercase.
c) Convert data types in SSDL to Oracle equivalent (e.g. datetime to Date, nvarchar to nvarchar2 etc).
d) Update stuff in mapping section according to new names in SSDL.
e) Of course, update Provider attributes in SSDL to dotConnect ones.
Has anyone already attempted the above? Is it going to work or not? I have not found many related resources in internet. Nobody has ever needed to tackle the problem of cross-database support using EF?
Consolidated EDMX for SQL Server and Oracle
First, don't forget about the MSL layer (it will not be enough to change the case only in SSDL, you should either
change the names of your DB objects accordingly, or change both SSDL and MSL).
You can find a simple example of the application using one conceptual model with several storage models here:
http://www.devart.com/dotconnect/efquerysamples.html
EF is a cross-database ORM in theory, but really one needs to make a significant number of tests
(in fact, as many as possible) for the model and the LINQ code with each DBMS because of some specific problems.
As an example of such problem I can mention the OUTER ALPPLY problem (in some situation EF generates
this clause in the expression tree and there is no possibility to convert for non-MS SQL Server database) is described here:
http://msdn.microsoft.com/en-us/library/bb896273.aspx
Another example is associated with the SqlClient type conversion:
http://social.msdn.microsoft.com/Forums ... cf472fb4d5
In case you plan to use stored procedures returning a result set (entity collection) in your model, be aware
of the fact that Oracle and SQL Server treat this situation differently. Oracle uses cursors for it,
we have a blog post concerning it here:
http://www.devart.com/blogs/dotconnect/?p=5
change the names of your DB objects accordingly, or change both SSDL and MSL).
You can find a simple example of the application using one conceptual model with several storage models here:
http://www.devart.com/dotconnect/efquerysamples.html
EF is a cross-database ORM in theory, but really one needs to make a significant number of tests
(in fact, as many as possible) for the model and the LINQ code with each DBMS because of some specific problems.
As an example of such problem I can mention the OUTER ALPPLY problem (in some situation EF generates
this clause in the expression tree and there is no possibility to convert for non-MS SQL Server database) is described here:
http://msdn.microsoft.com/en-us/library/bb896273.aspx
Another example is associated with the SqlClient type conversion:
http://social.msdn.microsoft.com/Forums ... cf472fb4d5
In case you plan to use stored procedures returning a result set (entity collection) in your model, be aware
of the fact that Oracle and SQL Server treat this situation differently. Oracle uses cursors for it,
we have a blog post concerning it here:
http://www.devart.com/blogs/dotconnect/?p=5