Avoid Oracle schema name with Entity Framework

Question

I'm using the Official Oracle SQL and Entity Framework driver to read the database. But when reading the database it prefixes the table name with "dbo".:

SELECT
*
FROM "dbo"."Woningen"

Without the "dbo". prefix the code works fine, with, it causes the error "table or view does not exist". This is probably because the user isn't 'dbo', so it does not have access to that schema. This is the Entity Framework code that I'm using:

[Table("Woningen")]
public class Woningen

I've tried updating the Oracle nuget package but then it comes up with the error "Connection string is not well-formed". So it probably has the same error as before, it just failed sooner. This is the connectionString format I used:

<add name="DefaultConnection"
  providerName="Oracle.ManagedDataAccess.Client" 
  connectionString="USER ID=testUser;PASSWORD=password;  
  Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=serverUrl)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=Database)));"/>

I can see three possible solutions to this problem, but have no idea how to implement them:

Manipulate Entity Framework to exclude schema names from queries
Give my user access to the schema
Update the driver and fix the connectionstring format, if anyone knows how the format changed..

Note that current code already works in production so the current version should be fine. The database and it's user are new, so the problem could be with how they are created.

You can specify the appropriate schema to use in EF, if that helps? EF6+ modelBuilder.HasDefaultSchema(“your schema name”); — IronAces
– IronAces, Commented Sep 6, 2017 at 7:13
@DanielShillcock Thanks, that's the solution, using the username as the schema name. If you make it into an answer I'll accept it. — MrFox
– MrFox, Commented Sep 6, 2017 at 8:32

IronAces · Accepted Answer · 2017-09-06 08:38:17Z

5

You can specify the schema that Entity Framework uses. See below

If you are using Entity Framework 6+ you can use the following

public class Context : DbContext
{  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //Set a default schema for ALL tables
        modelBuilder.HasDefaultSchema("YourSchemaName");
    }
}

If you wish to set a schema on a specific table...

[Table("Woningen"), Schema = "YourSchemaName")]
public class Woningen { }

If you are using EF5

public class Context : DbContext
{    
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    { 
         // Unfortunately you have to specify each table you want to set a schema for...
         modelBuilder.Entity<Woningen>().ToTable("Woningen", "YourSchemaName");
    } 
}

answered Sep 6, 2017 at 8:38

IronAces

1,9011 gold badge28 silver badges37 bronze badges

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

6 Comments

Syed Ali Taqi Over a year ago

any recommendation for database first approach? as, in almost every case, schema of dev env is different than prod env.

IronAces Over a year ago

@SyedAliTaqi Perhaps store schema name in the webconfig/application settings?

Syed Ali Taqi Over a year ago

thank you for quick reply! I tried it but wasn't able to connect all parts well. would be really helpful if you can share a blog or link with code, as guide?

IronAces Over a year ago

@SyedAliTaqi Check this answer out. It tells you how to access the applicationsettings within web.config.

Syed Ali Taqi Over a year ago

problem is, there's no option, as far as I researched, in Entity Framework to pass schema at run time for DB first approach. Thank you for the help though, much appreciated.

|

Collectives™ on Stack Overflow

Avoid Oracle schema name with Entity Framework

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related