Oracle ODP.Net与实体框架6 - ORA-00955从表视图中选择 [英] Oracle ODP.Net With Entity Framework 6 - ORA-00955 on Select from Table View

问题描述

  static void Main（string [] args）
 {
 OracleConnection con = new OracleConnection（）; 
 
 //使用连接字符串属性连接到Oracle数据库
 con.ConnectionString =user id = ****; password = ****; data source =+ $ b $ CONNECT_DATA =（SERVER = dedicated）（SERVICE_NAME = ora1）））;$（DESCRIPTION =（ADDRESS =（PROTOCOL = TCP）（HOST = server.org.net）（PORT = 1521） 
 con.Open（）; 
 Console.WriteLine（Connected to Oracle+ con.ServerVersion）; 
 
 OracleCommand command = con.CreateCommand（）; 
 command.CommandText =SELECT ITEM FROM TEST.ORDERS; 
 
 OracleDataReader reader = command.ExecuteReader（）; 
 
 while（reader.Read（））
 {
 Console.WriteLine（\t {0}，
 reader [0]）; 
} 
 reader.Close（）; 
 
 //关闭并处理OracleConnection对象
 con.Close（）; 
 con.Dispose（）; 
 Console.WriteLine（Disconnected）; 
 Console.ReadKey（）; 
} 
  

TEST.ORDERS是一个视图。

第二个程序正在使用ODP.Net和EntityFramework和manaly创建的DbSet类（已经在Npgsql.EntityFramework上进行了测试，并且从Oracle完美的视图拷贝）。应用程序返回错误：ORA-00955。
我注意到，当Schema的更改名称没有ORDER视图时，程序将创建具有相同名称的表。
这是我开始的DbSet：

  [Table（ORDERS，Schema =TEST）] 
  

可能是错误的。但是我不知道如何构建可以访问这个视图的实体。用户只有SELECT权限。我想做只读操作。

解决方案

实体框架提供程序的Oracle实现非常差，但有一些方法如何使这个工作。

1. 简单但令人讨厌 - 使用NULL或自己的数据库初始化程序实现：

     Database.SetInitializer< DatabaseContext>（null）; 
     

或

  class DatabaseInitializer：IDatabaseInitializer< DatabaseContext> 
 {
 public void InitializeDatabase（DatabaseContext context）
 {
 //您的实现
} 
} 
 
 Database.SetInitializer （新的DatabaseInitializer（））; 
  

在首次访问数据库之前设置初始化。

2. 如果要使用迁移创建视图，然后添加迁移，忽略更改，例如使用包控制台 add-migration initial -ignorechanges 。这将使EF忽略DB模式和模型之间的不一致（因为它仅检查 ALL_TABLES 中的表，而不是视图），因此不会尝试创建表。 Oracle EF实现中存在一个错误，如果初始迁移为空，它将丢弃并重新创建 __ MigrationHistory 表，否则您的初始迁移必须包含至少一个表，然后再添加查看迁移或您需要添加表。

I have created to apps, first with ODP.Net and without Entity - works great.

static void Main(string[] args)
{
    OracleConnection con = new OracleConnection();

    //using connection string attributes to connect to Oracle Database
    con.ConnectionString = "user id=****;password=****;data source=" +
        "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server.org.net)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=ora1)))";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);

    OracleCommand command = con.CreateCommand();
    command.CommandText = "SELECT ITEM FROM TEST.ORDERS";

    OracleDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine("\t{0}",
            reader[0]);
    }
    reader.Close();

    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
    Console.ReadKey();
}

TEST.ORDERS is a View.

Second program is using ODP.Net and EntityFramework and manaly created DbSet class (which was tested on Npgsql.EntityFramework and works great, perfect copy of the view from Oracle). The application returns error: ORA-00955. I noticed that when the change name of the "Schema" to one that does not have a view 'ORDER', the program creates my table with the same name. This is my begining of DbSet:

[Table("ORDERS", Schema = "TEST")]

It is possible that it is wrong. But I do not know how to build the entities that will have access to this view. The user has permission only to SELECT. I want do read-only operations.

解决方案

Oracle implementation of Entity framework provider is very poor but there are some ways how to make this working.

1. Simple but annoying - using NULL or own database initializer implementation:

   Database.SetInitializer<DatabaseContext>(null);
   

or

class DatabaseInitializer : IDatabaseInitializer<DatabaseContext>
{
    public void InitializeDatabase(DatabaseContext context)
    {
        // your implementation
    }
}

Database.SetInitializer(new DatabaseInitializer());

Set the initialized before first access to your database.

2. If you want to use migrations create your views and then add migration with ignoring changes, for instance using package console add-migration initial -ignorechanges. This will make EF ignoring the inconsistencies between the DB schema and model (because it checks only tables from ALL_TABLES, not views) so it will not try to create table. There is a bug in Oracle EF implementation that if the initial migration is empty it drops and recreates the __MigrationHistory table so either your initial migration must containt at least one table before you add the view migration or you need to add a table afterwards.

这篇关于Oracle ODP.Net与实体框架6 - ORA-00955从表视图中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！