Is Entity Framework a good choice for an asp.net application that has to connect to MsSql,Mysql and Oracle databases?
I want to know if Entity Framework can be used to create an architecture such that the asp.net application connects to MSSql,Mysql and Oracle database just by changing the connection string. If it is possible how do I proceed with it? If not what is the next best alternative? I am working on a product that is used by different customers - some prefer MsSql, some prefer Mysql and very few prefer Oracle. This means all three databases will have identical schema.
I have used EF with both Oracle and SQL Server. It works really well with SQL Server and reasonably well with Oracle.
However, I wouldn't have the application talk directly to EF itself. There are bound to be subtle differences between the databases that you don't want your core application to deal with. Instead, use something like the Repository Pattern and have your app talk to a repository interface. Create a repository for each database that you can plug into your app. You can certainly use EF in your repositories to map data between the database and your domain classes; but as good practice, your application shouldn't know about your data access or database technology.
If you are planning to use entity framework 6, currently not supported by oracle. But EF 5 would work right now. I am very optimistic that oracle would provide support for ef 6 in future. I have used EF5 to connect to mysql, oracle and sql server successfully using repository pattern and unit of work pattern and running successfully in production. Read also domain driven design (domain driven pattern/table pattern) and onion architecture