I’m currently working on a .NET web application with Oracle as my database backend. This is a legacy application that I have chosen to use Entity Framework with in order to remove some dependencies on other 3rd party application.
I am not the sole developer on this project, and so we use Team Foundation Server with Visual Studio 2010 and multiple schemas. Currently we each have our local schema to develop in, a schema to merge all of our changes into, a user acceptance testing schema, a pre deployment schema, and a production schema. These schemas obviously have matching environments.
The problem I am being face with is that I created my Entity Data Model (EDMX) pointing at my own local schema, and now that I have checked this file in, all developers who “get latest” on the file will end up pointing at my schema. I have read articles about editing the EDMX file with the XML viewer and removing the “schema” tag attribute and value; this poses a problem as when you right click in the EDMX file (when in the normal view and not the XML view) and select “Update Model from Database”, none of the entity sets are linked up to any of the tables in the schema that is listed (the schema being based on the Oracle user used in that developer’s app.Config connection string.)
The above is only a problem when the developer is trying to add new tables (or existing tables) that have dependencies (foreign keys and such) on tables that are already represented as entity sets. I understand that the developer can edit the EDMX file in XML view again and add the “schema” tag with a value back to all tables that need to be modified, but then you have to do that to all of the other entity sets that it is related to. This poses a problem when you have over 100 tables in your schema.
My last problem (that I believe I have solved) is that once you build the code and deploy it and verify it in one environment, we move it to the next. Currently to solve the issue with pointing at a different schema I have changed the “Metadata Artifact Processing” property for the EDMX file to “Copy to Output Directory” so that the .csdl, .msl, and .ssdl files get copied to the bin folder of that project rather than inserted into the project’s resources (default behavior.) I then have the web application project’s post-build event do a copy of those newly created files and place them in the web project’s bin. This lets me modify the file that contains all of the “schema” tags and prevent errors from happening.
All of this seems like such a lengthy process for configuring all environments. Normally this wouldn’t be a problem if you weren’t creating lots of tables, but it really is time consuming to do this each and every time.
Perhaps I am missing something and there is an easier way to deal with this? Is there a way to get around having to manually change the “schema” tag value via XML each and every time I want to point at a difference instance of my environment?
Well, this is great ...
I was origionally pointing at schema "A" when I created my modal in the EDMX. The schema I was working with was going to be destroyed and so we pointed the connection string to schema "B", our integration environment. To do this, we edited the EDMX file with the XML editor and removed every "schema='A'" tag that there was.
When executing the program, it worked great. No issues. Now, when we went to add another entity to the model, none of the tables were linked to that new schema, which was fine for the time being as we didnt need any relationship between this new entity and the pre-existing ones.
When we tried to run the proram this time, it errored out due to some functions not being mapped anymore. I checked the XML of the EDMX file and ALL of the previous EntitySet information and such is GONE.
How do I wire my entities back up?
Why can't Oracle fix this so that you can easily swap schema names?
I soon might be trying to convert my database over to SQL Server where everything will play nice together again.
Where is the 'Me too' button?
We open the edmx file and strip out the Schema="xxx" before we check in.
Then when we want to update from database we edit the file and (using find and replace) put the Schema="xxx" back in first. Then update from DB. Then strip out Schema="xxx" finally before checking back in.
Wondering if anyone else has a script to do the same?