LOGIC Library

This site is the Logic involvement in sharing expertise and skills acquired in daily work. The goal is to create a solid knowledge base and share best practices in software development and systems management.

More info about us can be found on logicsistemi.it.

ASP.NET MVC - Entity Framework - Oracle - ORA-00942: table or view does not exist

Entity Framework can be used with Oracle databases by downloading and installing some software as explained in this article on Oracle website. Perhaps, when your application is running on the development machine, everything works fine, but on production server it fails, giving you the error "ORA-00942: table or view does not exist". Let's see a possible reason and a workaround to solve the problem.

As you probably know, Visual Studio helps creating the model by means of the Entity Data Model Wizard. The wizard creates an XML file with extension .edmx where it stores the information structured in the Runtime Content (section "<edmx:Runtime>") and Designer Content (section "<Designer>"). Runtime Content  contains, in turn, "<edmx:StorageModels>", "<edmx:ConceptualModels>" and "<edmx:Mappings>" sections.

Our Oracle error could come from the "<edmx:StorageModels>" section:

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx";>
<!-- EF Runtime content -->
<edmx:Runtime>
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="Model.Store" Alias="Self" Provider="Oracle.DataAccess.Client" ProviderManifestToken="10.2" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"; xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl";>
<EntityContainer Name="ModelStoreContainer">
<EntitySet Name="YOUR_TABLE" EntityType="Model.Store.YOUR_TABLE" store:Type="Tables" Schema="YOUR_SCHEMA_DEVELOPMENT_MACHINE" />
...
...

In particular, let's see the yellow highlighted line. This is the EntitySet tag. We can (roughly) think that EntityContainer contains an EntitySet for each table in the database.

The attribute of EntitySet we focus on is Schema (red highlighted). Suppose we have a development machine we are working on with the Oracle Schema "YOUR_SCHEMA_DEVELOPMENT_MACHINE". Everything, here, works fine, but if we deploy the project to the production server, the Oracle Schema could be different (e.g.: "YOUR_SCHEMA_PRODUCTION_SERVER").

A possible, but very uncomfortable, solution is to create an .edmx file for the development machine with the schema "YOUR_SCHEMA_DEVELOPMENT_MACHINE" and another one for the production server with the schema "YOUR_SCHEMA_PRODUCTION_SERVER".

Our favourite solution (a workaround, indeed) is the following:

  • Open the .edmx file with a text editor
  • Delete every occurrence of the following string: Schema="YOUR_SCHEMA_DEVELOPMENT_MACHINE"
  • Save the file
  • Recompile the Project

This way, you can use the same .edmx file both in the development machine and the production server, without the "ORA-00942: table or view does not exist" error.

Pay attention to back-up the original .edmx file before doing the above steps. In fact, it's recommended to use the original file when you have to update it by means of the Entity Data Model Wizard. Then you can do the above steps, yet.

Comments   

 
#1 Fernando 2015-07-07 14:54
Thanks!. Very Helpful!
Quote