How to configure DbContext to work with Oracle ODP.Net and EF CodeFirst?

Question

I'm trying to work with EF CodeFirst under Oracle with ODP.net. This is my DbContext class:

    public class MyCEContext : DbContext {

    public DbSet<Person> Persons { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        modelBuilder.Entity<Person>().ToTable("PERSONS","myce");

    }

    public MyCEContext() : 
        base(new OracleConnection(
            "Data Source=cebd; User ID=myce; Password=****;"), true) {}

}

Problem is that when I try to do something like this:

MyCEContext context = new MyCEContext();
Person p = context.Persons.Find(1);

I get this inner error:

{"ORA-00942: table or view does not exist"}

And the table exists.

What am I doing wrong?

Solution 1

As Nick wrote in his answer, the issue is related with the quotes and case of the generated query, but not with the table's names but with schema's name:

SELECT * 
FROM "myce"."PERSONS" "Extent1"

So the solution is very simple, just to uppercase the user id and the schema name:

modelBuilder.Entity<Person>().ToTable("PERSONS","MYCE");

In general, all must be in uppercase: tables, schema and field's names. But it is better annotate each mapped property with the Column attribute instead of uppercase the property name:

    [Column("FIRST_NAME")]
    public string FirstName { get; set; }

Thus the names will be easier to read in both database and classes.

Solution 2

Your issue is most likely because EF passes the query to Oracle in quotes, which means that the case on your tables and your fields has to match that of the database.

So if you had the following:

select name from persons;

The EF code will probably be firing the following SQL:

select "NAME" from "PERSONS";

Add this to your OnModelCreating function:

modelBuilder.Conventions.Remove<ColumnTypeCasingConvention>();

...and construct your POCO object with uppercase property names rather than the normal sentance case.

If you want to see the SQL, break the code and take a look at the DbContext.Persons object. You should see the actual sql command it will use to query the whole table (quite larger)

Note

We use Oracle EF Code First in production. Although not officially supported, there doesn't seem to be anything missing from the latest ODAC release that will prevent you.

Solution 3

You can call ToString on the linq query that you run against your dbcontext object. This will show you the SQL being generated. That should help you find the problem

My problem was two fold:

1. My table names were being pluralized
2. My table names were being prefaced with "dbo."