Oracle ODP.Net With Entity Framework 6 - ORA-00955 on Select from Table View

Question

I have created two 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.

Answer 1

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.

Answer 2

You can use Keyless Entity Types and use ToView fluent api in you DbContext. when you define an entity as ToView and do migrations, the EF assumes that view has already exist in your database and won't try to create it.

So first create model:

  public class KeyLessModel
    {
        public string Item { get; set; }
    }

And in DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<KeyLessModel>().ToView("ORDERS", "TEST");

        }

        public DbSet<KeyLessModel> KeyLessModels { get; set; }

For get data from view just call it like this:

var items = _dbContext.KeyLessModels.ToList();

I've tested in EF Core 3.1 and works fine.