CODEWITHSUNDEEP
c#databaseoracle-databaseentity-frameworkentity-framework-6
MrFox
MrFox

Reputation: 5106

Avoid Oracle schema name with Entity Framework

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:

  1. Manipulate Entity Framework to exclude schema names from queries
  2. Give my user access to the schema
  3. 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.

Upvotes: 3

Views: 5405

Answers (1)

IronAces
IronAces

Reputation: 1883

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");
    } 
}

Upvotes: 5

Related Questions