EntityFramework Code First Oracle

Question

I have the follow context, mapping and class that uses for work with oracle:

public class Context : DbContext
    {
        public string Schema { get; set; }

        public Context(string connectionString)
            : base(connectionString)
        {
        }

        public DbSet<User> UserDbSet { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new UserMapping(Schema));
        }
    }

    public class User
    {
        public decimal Id { get; set; }
        public string Name { get; set; }
        public string LastName { get; set; }
    }

    public class UserMapping : EntityTypeConfiguration<User>
    {
        public UserMapping(string schema = "dbo")
        {
            HasKey(t => t.Id);
            ToTable(schema + ".TBLUSER");

            Property(t => t.Id).HasColumnName("Id");
            Property(t => t.Name).HasColumnName("NAME");
            Property(t => t.LastName).HasColumnName("LASTNAME");
        }
    }

But when i call the:

var context = new Context("name=ORACLE")
            {
                Schema = "USERTABLESPACEDEFAULT"
            };

        var users = context.UserDbSet.ToList();

The oracle Privider shows me the follow error:

{"ORA-00904: \"Extent1\".\"Id\": invalid identifier"}

And thats is why EntityFrameworks try to execute the follow query:

SELECT 
"Extent1"."Id" AS "Id", 
"Extent1"."NAME" AS "NAME", 
"Extent1"."LASTNAME" AS "LASTNAME"
FROM "USERTABLESPACEDEFAULT"."TBLUSER" "Extent1"

Any .dll its necesary ??

Here is my connectionString:

<add name="ORACLE" connectionString="DATA SOURCE=localhost:1521/XE;PASSWORD=Isolucion2015*;USER ID=SYSTEM" providerName="Oracle.DataAccess.Client" />

So, what is the column name? Is it really Id? Or is it ID? Because once the column names are wrapped in double quotes, the name is case sensitive, so you have to get the casing right. — sstan
– sstan, Commented Jul 13, 2015 at 17:17
@sstan Yes, the problem was in oracle is ID and I was mapping Id. There is a way to Oracle works the same name columns than sql server ?? — Freddy Castelblanco Macias
– Freddy Castelblanco Macias, Commented Jul 13, 2015 at 17:18
This is quite unrelated, but you may want to remove your password in your connection string. — Ian
– Ian, Commented Feb 15, 2016 at 9:25

sstan · Accepted Answer · 2015-07-13 17:24:05Z

5

Ty changing this line:

Property(t => t.Id).HasColumnName("Id");

...to this:

Property(t => t.Id).HasColumnName("ID"); // Upper case ID.

By default, Oracle's column names are in upper case. And when EF generates the names wrapped in double quotes, you have to make sure you get the casing right.

If you really want to keep using "Id", then you either have to find a way to have EF not place the double quotes around Id so that the name check is not case sensitive (I don't know how to do that).

Or, you have to rename the column in Oracle to be exactly Id.

alter table tbluser rename column id to "Id";

But really, I think you should just change your string to "ID" and be done with it.

answered Jul 13, 2015 at 17:24

sstan

36.8k7 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Freddy Castelblanco Macias Over a year ago

Ok, and I suppose that at the moment of create is the same "rule"

sstan Over a year ago

You mean when you create the table? Yes, if you really want to force a certain casing of a table name or column name, then you need to surround it with double quotes.

Freddy Castelblanco Macias Over a year ago

Ok, thanks .. Another question a little diferent, have you a script where you create a tablespace with user login and grant privilegies to user tablespace ...

sstan Over a year ago

Sorry, that's out of scope for this site.

Marko M. · Accepted Answer · 2017-01-13 15:36:37Z

2

I was recently faced with the same problem of upper case names and Oracle so there is now Nuget package to take care of that.

Including EntityFramework.OracleHelpers and doing oneliner will apply uppercase table, column and foreign key conventions.

  using EntityFramework.OracleHelpers;

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
        this.ApplyAllConventionsIfOracle(modelBuilder);
  }

More info and examples are at GitHub page.

answered Jan 13, 2017 at 15:36

Marko M.

7893 silver badges9 bronze badges

Collectives™ on Stack Overflow

EntityFramework Code First Oracle

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related