The value's length for key 'data source' exceeds it's limit of '128'

Question

I know that a very similar question has been asked here, but the answer didn't help me.

I am using Entity Framework 6 with the Oracle.ManagerDataAccess.Client.

If I define the connection string in app.config, then the connection works. If I specify the identical connection string in code, then I get the error

The value's length for key 'data source' exceeds it's limit of '128'.

which is correct.

This is my connection string (with some names removed):

"User Id=xxxxxxxxxxx;Password=xxxx;Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = (PROTOCOL = TCP)(HOST = VS-ORACLE.xxxxxxx.de)(PORT = 1521) ) ) ( CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl.xxxxxxxx.de) ) )"

I know that there are a bunch of spaces which could be removed, but I am still not going to get the string down below 128 characters.

How come it works when the connection string is in app.config, but not when it is in code?

Is there any trick that I can use, by offloading some of the parameters to another string?

I am already using a DBConfiguration object. Is there any way to set some of the parameters in that object?

If I use the full oracle client, I guess that I could reference a configuration in the file tnsnames.ora, but it would be a great bonus if we could talk to an oracle database without the full client.

Update

This is what the connection string looks like in app.config

<connectionStrings>
  <add name="OracleDbContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="User Id=xxxxxxxxxxx;Password=xxxx;Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = (PROTOCOL = TCP)(HOST = VS-ORACLE.xxxxxxxx.de)(PORT = 1521) ) ) ( CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl.xxxxxxxx.de) ) )" />
</connectionStrings>

In code I have defined the context class as follows:

[DbConfigurationType(typeof(OracleDBConfiguration))]
public class GlobalAttributeContext : DbContext
{
  public DbSet<GlobalAttribute>  GlobalAttributes { get; set; }

  static GlobalAttributeContext()
  {
    Database.SetInitializer<GlobalAttributeContext>(null);
  }

  public GlobalAttributeContext(string nameOrConnectionString) : base(nameOrConnectionString)
  {
  }

  public GlobalAttributeContext() : this ( "Name=OracleDbContext" )
  {
  }

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    // We have to pass the schema name into the configuration. (Is there a better way?)
    modelBuilder.Configurations.Add(new GlobalAttribute_Config_Oracle("SchemaName")) ;
  }
}

I have defined a DbConfiguration class as follows:

class OracleDBConfiguration : DbConfiguration
{
  public OracleDBConfiguration()
  {
    this.SetDefaultConnectionFactory ( new System.Data.Entity.Infrastructure.LocalDbConnectionFactory("v12.0") ) ;
    this.SetProviderServices ( "Oracle.ManagedDataAccess.Client", Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices.Instance ) ;
    this.SetProviderFactory  ( "Oracle.ManagedDataAccess.Client", Oracle.ManagedDataAccess.Client.OracleClientFactory.Instance ) ;
  }
}

Finally, I create the context like this

string ConnectionString = "User Id=xxxxxxxxxxx;Password=xxxx;Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = (PROTOCOL = TCP)(HOST = VS-ORACLE.xxxxxxxx.de)(PORT = 1521) ) ) ( CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = orcl.xxxxxxx.de) ) )" ;

using (var ctx = new GlobalAttributeContext(ConnectionString))
{
  var globalAttributes = ctx.GlobalAttributes.ToList() ;
  foreach ( GlobalAttribute ga in globalAttributes )
  {
    Console.WriteLine ( "Name: {0}, Value: {1}", ga.Attribute, ga.Value ) ;
  }
}

The connection strings used in the two methods are identical.

Solution

My colleague has found an answer to this problem as follows:

Add another constructor to the context class to use an existing connection.

public GlobalAttributeContext(DbConnection existingConnection, bool contextOwnsConnection) 
       : base(existingConnection, true)
{
}

This is the complete context class

namespace OracleTestExeConfigAndConnStr
{
  [DbConfigurationType(typeof(OracleDBConfiguration))]
  public class GlobalAttributeContext : DbContext
  {
    public DbSet<GlobalAttribute>  GlobalAttributes { get; set; }

    static GlobalAttributeContext()
    {
      Database.SetInitializer<GlobalAttributeContext>(null);
    }

    public GlobalAttributeContext() : base("OracleDbContext")
    {
    }

    public GlobalAttributeContext(string nameOrConnectionString)
           : base(nameOrConnectionString)
    {
    }

    public GlobalAttributeContext(DbConnection existingConnection, bool contextOwnsConnection)
           : base(existingConnection, true)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      // We have to pass the schema name into the configuration. (Is there a better way?)
      modelBuilder.Configurations.Add(new GlobalAttribute_Config_Oracle("SchemaName")) ;
    }
  }
}

Create the database connection as a separate step and pass the connection into the context object.

string connStr = @"User Id=xxxxxxxxxxx;Password=xxxx;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VS-ORACLE.xxxxxxxx.de)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl.xxxxxxxx.de)))";

using (var connection = new OracleConnection() { ConnectionString = connStr })
{
  connection.Open();
  using (var ctx = new GlobalAttributeContext(connection, true))
  {
    var globalAttributes = ctx.GlobalAttributes.ToList();
    foreach (GlobalAttribute ga in globalAttributes)
    {
      Console.WriteLine("Name: {0}, Value: {1}", ga.Attribute, ga.Value);
    }
  }
}

For completeness, this is the DBConfiguration class, which is specified as an attribute on the context class.

class OracleDBConfiguration : DbConfiguration
{
  public OracleDBConfiguration()
  {
    this.SetDefaultConnectionFactory ( new System.Data.Entity.Infrastructure.LocalDbConnectionFactory("v12.0") ) ;
    this.SetProviderServices ( "Oracle.ManagedDataAccess.Client", Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices.Instance ) ;
    this.SetProviderFactory  ( "Oracle.ManagedDataAccess.Client", Oracle.ManagedDataAccess.Client.OracleClientFactory.Instance ) ;
  }
}

This method works from a DLL without requiring any values in app.config.

Comments

[Phil Jollans]

I want to avoid using the app.config file, because I want to encapsulate the data access in class library which will be used by multiple applications. The database connection is configured and stored independently.

[Wernfried Domscheit]

You don't have to use the app.config file, it is just an option. Specify location of tnsnames.ora file by one of the given possibilities and you are done.

[Phil Jollans]

Thanks, I tried it briefly and failed. I will try again.

[Wernfried Domscheit]

Try environment variable TNS_ADMIN,this should take precedence over all other values

[Phil Jollans]

In the debugger output window I get a lot of messages Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll which make me think it is trying to connect to SQL server and not to Oracle. The version with connection string in app.config does specify the provider name, which is missing when I just use the connection string in code. I thought it would come from the DBConfiguration which I have specified as an attribute on the context class.