表不存在

表不存在

关注
发信
关注(28)粉丝(399)

使用EF 6和Oracle.ManagedDataAccess时，表不存在

本文介绍了使用EF 6和Oracle.ManagedDataAccess时，表不存在的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用EF 6.0.0.0 和ODP.Net 创建一个MVC应用程序Oracle.ManagedDataAccess version 4.121.2.0 用于数据访问。



在我的控制器称为 EmployeeController ，我有以下代码片段：

  public ActionResult Details （int id）{
 try {
 EmployeeContext employeeContext = new EmployeeContext（）; 
 Employee employee = employeeContext.Employees.Single（x => x.Id == id）; //这里发生异常！ 
 return View（employee）; 
} catch（Exception e）{
 return View（e）; 
} 
}

当我加载 Employee / Details.cshtml 页面我得到以下异常：

在内部异常中，它说：

这使我困惑，因为在我的Oracle数据库中，该表绝对存在（我检查使用Toad for Oracle）：



在评论中，是由于使用错误的 Schema 。默认情况下，EF 6使用 dbo 作为默认模式，而我的模式不是 dbo 。要使模型具有默认模式，需要覆盖 OnModelCreating 事件：

  public class EmployeeContext：DbContext {
 public DbSet< Employee>员工{get;组; } 
 protected override void OnModelCreating（DbModelBuilder modelBuilder）{
 modelBuilder.HasDefaultSchema（myschema）; 
} 
}

另外，感谢他的建议，以检查由EF生成的SQL。


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:

And in the inner exception, it says:

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)
解决方案

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.

这篇关于使用EF 6和Oracle.ManagedDataAccess时，表不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

07-23 13:04
Powered by LMLPHP ©2024 表不存在 0.041635