MVC4 + EntityFramework: metadata only found from within web.config - why is that?

Question

I want to change our EF-driven database-first ASP.NET MVC4 web application in such a way that I can specify the database to connect to at runtime. For the beginning, I simply wanted to substitute the entry in the connectionStrings section of the web.config with a coded version. But first things first. Here's my web.config connection section:

  <connectionStrings>
    <add name="WEB_Entities" connectionString="metadata=~/bin/Models\WEB_Models.csdl|~/bin/Models\WEB_Models.ssdl|~/bin/Models\WEB_Models.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=testsvr;initial catalog=DEMO;persist security info=True;user id=sa;password=xxxxxxxx;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

When using this setting, everything runs just fine. Now I tried to comment out this entry and hard-code it, thereby moving the setting from the web.config to the global.asax.cs.

I read about the EntityConnectionStringBuilder, but for the beginning I simply want to give the whole connection string as the constructor parameter of an EntityConnection:

    string CS =
    @"metadata=~/bin/Models\WEB_Models.csdl|
    ~/bin/Models\WEB_Models.ssdl|
    ~/bin/Models\WEB_Models.msl;
    provider=System.Data.SqlClient;
    provider connection string=""Data Source=testsvr\sqlexpress;
    Initial Catalog=DEMO;
    Integrated Security=True;MultipleActiveResultSets=True""";

    conn = new EntityConnection(CS);
    conn.Open();

The conn object is a static object that lives in my application class:

public static EntityConnection conn;

In order to use this connection object, I changed my DBContext code to use the aforementioned connection object as constructor parameter, rather than the Name of an entry in the web.config:

public partial class WEB_Entities : DbContext
{
    public WEB_Entities()
        : base(PAMVCTEST.MvcApplication.conn,true)
        //: base("name=WEB_Entities")
    {
    }

Now when I compile an run the whole thing, the connection to the db server seems to be possible (because I get some network related errors when e.g. changing the datasource to something wrong), but the application does not find the given metadata files. This is the error:

The supplied connection string is not valid, because it contains insufficient mapping or metadata information. Parameter name: connection

I don't understand why the metadata files cannot be found, they are definitely present in the given location. As soon as I change everything back to using the web.config connection entry, everything works as expected.

I also tried changing the metadata files location to this:

res://*/Models.WEB_Models.csdl|res://*/Models.WEB_Models.ssdl|res://*/Models.WEB_Models.msl

I made sure that the resource names are correct with ILMerge. THe result is the same: when I use the web.config way, it works - when I set it by code, I get the same error as mentioned above.

What can I do to resolve this issue? Are there any workarounds? And why in the world do we have to cope with such awful and error-prone connection strings with nested escapings and stuff? It's 2013!!! :-]

Thanks for your help!

Answer 1

Call it from DbContext. Change your DbContext constructor to the following:

public class MyDbContext : DbContext
{
    public MyDbContext()
        : base("DefaultConnection")
    {

    }

    public MyDbContext(string conStr)
        : base(conStr)
    {

    }

    // ...
}

Then add your desired ConStrs to the web config. Finally, when you want another ConStr than the DefaultConnection pass its name to the DbContext() constructor:

Models.MyDbContext db = new Models.MyDbContext("MyConStr");