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)

Have you tried to see the SQL query generated by EF? for instance `employeeContext.Database.Log = s => { };` and put a breakpoint inside the lambda body. — Ivan Stoev, Feb 15 '16 at 07:49
@IvanStoev no, But I will give it a try if that can help me find the problem... Give me a minute — Ian, Feb 15 '16 at 07:50
@IvanStoev ok, I have just posted the SQl query generated by EF by using the method as you suggested. — Ian, Feb 15 '16 at 08:02
@DevilSuichiro since the error shows that the table does not exist, I am afraid that I may point to wrong database. But I do not know how to check if I do. All I know is that if I use the same connection string and I do not use entity framework, but simply use that connection string to connect to my database, it is all fine... So I suspect I miss something in my EF configuration, but I just do not know what and how to check — Ian, Feb 15 '16 at 08:11
the connection string should actually be enough. are you sure you are using the DbContext connected to the right connection string? how does your constructor/app.config/web.config look like? — DevilSuichiro, Feb 15 '16 at 08:15
@DevilSuichiro "are you sure you are using the DbContext connected to the right connection string" How do I check this? In my web.config, I have two connections. I will post them if needed be. And what else could help me to see the issue? — Ian, Feb 15 '16 at 08:19
@DevilSuichiro connection string added. I have a simple question: "dbo"."TBLEMPLOYEE" what is "dbo" there? — Ian, Feb 15 '16 at 08:31
dbo is the default schema name. which connection string are you using? which connection string does your DbContext constructor use? — DevilSuichiro, Feb 15 '16 at 08:48
@DevilSuichiro I use the `Oracle.ManagedDataAccess` connection string. How do I change the default schema "dbo" to something else for my query? — Ian, Feb 15 '16 at 08:50

score 13 · Accepted Answer · edited May 23 '17 at 12:24

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.

score 2 · Answer 2 · edited Feb 12 '20 at 10:50

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" />

edited Feb 12 '20 at 10:50

THess

1,003
1
13
21

answered Feb 12 '20 at 08:42

Shoshana Schechter

21
1

score 0 · Answer 3 · answered Nov 03 '22 at 08:08

In my case I solved the issue by execute scaffold again the table and this is the steps:

1- Click Tools menu and select NuGet Package Manager then Package Manager Console.

2- Execute the scaffold command and use the connection string as the following script to create the DBcontext and Models and import the tables again with full settings :

Scaffold-DbContext "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521)))(CONNECT_DATA=(SID=orcl)));User ID=scott;Password=tiger" 

-Provider Oracle.EntityFrameworkCore 

-OutputDir    Models    

-Tables table1,table2,table3 -force

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

3 Answers3

Linked