Table Does Not Exist while using EF 6 and Oracle.ManagedDataAccess

Question

I am creating a MVC application using EF 6.0.0.0 and ODP.Net Oracle.ManagedDataAccess version 4.121.2.0 for the data access.

In my Controller called EmployeeController, I have the following code snippet:

public ActionResult Details(int id) {
    try {
        EmployeeContext employeeContext = new EmployeeContext();
        Employee employee = employeeContext.Employees.Single(x => x.Id == id); //Here the exception occurs!
        return View(employee);
    } catch (Exception e) {
        return View(e);
    }
}

And when I load the Employee/Details.cshtml page I got the following Exception:

"An error occurred while executing the command definition. See the inner exception for details."

And in the inner exception, it says:

ORA-00942: table or view does not exist

And this puzzles me, since in my Oracle Database, the table definitely exist (I checked using Toad for Oracle):

The connectionString for the database itself is the same connection string which I use for other project and where I was able to query the data from the database without difficulty.

Here is how my Employee class is declared in the Models/Employee.cs:

using System.ComponentModel.DataAnnotations.Schema;
.
.
.
[Table("TBLEMPLOYEE")] //the same table name
public class Employee {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Gender { get; set; }
    public DateTime DateOfBirth { get; set; }
    public int EmployeeType { get; set; }
    public double? AnnualSalary { get; set; }
    public double? HourlyPay { get; set; }
    public double? HoursWorked { get; set; }
    public string City { get; set; }
}

And my Models/EmployeeContext.cs is simply consisting of a single element:

using System.Data.Entity;
.
.
.
public class EmployeeContext : DbContext {
    public DbSet<Employee> Employees { get; set; }
}

And in the Global.asax.cs file, I have initialized the database for EmployeeContext model:

protected void Application_Start() { //executed at the very beginning               
    Database.SetInitializer<MvcWebApplication1.Models.EmployeeContext>(null); //null -> no initialization strategy
    AreaRegistration.RegisterAllAreas();
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
    RouteConfig.RegisterRoutes(RouteTable.Routes);
    BundleConfig.RegisterBundles(BundleTable.Bundles);
}

What am I still getting error should that the Table does not exist? What could possibly go wrong here? Any suggestion how to debug such case?

Edit:

When I evaluate the employeeContext.Employees, I got the following value:

{SELECT 
"Extent1"."Id" AS "Id", 
"Extent1"."Name" AS "Name", 
"Extent1"."Gender" AS "Gender", 
"Extent1"."DateOfBirth" AS "DateOfBirth", 
"Extent1"."EmployeeType" AS "EmployeeType", 
"Extent1"."AnnualSalary" AS "AnnualSalary", 
"Extent1"."HourlyPay" AS "HourlyPay", 
"Extent1"."HoursWorked" AS "HoursWorked", 
"Extent1"."City" AS "City"
FROM "dbo"."TBLEMPLOYEE" "Extent1"}

Edit 2:

Using:

employeeContext.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

I got the following in my Debug output window:

SELECT 
"Extent1"."Id" AS "Id", 
"Extent1"."Name" AS "Name", 
"Extent1"."Gender" AS "Gender", 
"Extent1"."DateOfBirth" AS "DateOfBirth", 
"Extent1"."EmployeeType" AS "EmployeeType", 
"Extent1"."AnnualSalary" AS "AnnualSalary", 
"Extent1"."HourlyPay" AS "HourlyPay", 
"Extent1"."HoursWorked" AS "HoursWorked", 
"Extent1"."City" AS "City"
FROM "dbo"."TBLEMPLOYEE" "Extent1"
WHERE ("Extent1"."Id" = :p__linq__0) AND (ROWNUM <= (2) )

Edit 3:

This is how my connection string looks like, just in case it is needed

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-MvcWebApplication1-20160212010850.mdf;Initial Catalog=aspnet-MvcWebApplication1-20160212010850;Integrated Security=True"
      providerName="System.Data.SqlClient" />
    <add name="EmployeeContext" connectionString="Data source=thisisfakedatasource;user id=thisisfakename;password=thisisfakepassword;persist security info=True"
      providerName="Oracle.ManagedDataAccess.Client"/>
  </connectionStrings>  

And the settings for the entityFramework is as follow:

  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </providers>
  </entityFramework>

Any clue where the issue could be?

Additional info:

Exception stack trace:

e.StackTrace

   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__3[TResult](IEnumerable`1 sequence)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Single[TSource](IQueryable`1 source, Expression`1 predicate)
   at MvcWebApplication1.Controllers.EmployeeController.Details(Int32 id) in c:\myapp\Controllers\EmployeeController.cs:line 25

Inner exception stack trace:

(e.InnerException).StackTrace

   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Solution 1

The problem why the Data Table was not found, as suggested by DevilSuichiro in the comment, was due to the wrong Schema used. By default, EF 6 use dbo as default schema while my schema is not dbo. To make the model having default schema, an overriding for OnModelCreating event is needed:

public class EmployeeContext : DbContext {
    public DbSet<Employee> Employees { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.HasDefaultSchema("myschema");
    }
}

Also, thanks to Ivan Stoev for his suggestion to check the SQL generated by the EF.

Solution 2

We had the same problem. What we found was a reference to the wrong database schema in the edmx file:

<EntitySet Name="MyTable" EntityType="Self.MyTable" Schema="**wrongSchemaName**" store:Type="Tables" />

By deleting the schema name our issue was resolved.

<EntitySet Name="MyTable" EntityType="Self.MyTable" Schema="" store:Type="Tables" />