In EF4.1 code first, how to override ConnectionString name in Web.config

Question

I'm creating a multi-tenant Asp.Net MVC 3 Web app, and using EF4.1 code first for the db model.

For development I'm happy to use SqlServerCE in App_Data, and for production this will move to Sql Server 2008.

Say my context is called "MyModels", by default code-first looks for a connection string called "MyModels" in Web.config. This can be told to use a file in App_Data or changed to access a database in SQL2008. All fine so far.

But because of multi-tenancy, I'd like the SqlServerCE filename to match the unique id of the tenant (so App_Data would have "client_x.sdf", "client_y.sdf"; Sql Server 2008 would have separate databases). I can't work out how to direct to these different databases.

I've tried MyModels inheriting from DbContext and supplying a connection string (using a 'placeholder' conn string in Web.config and replacing "{clientId}" with the unique id), and I've also tried setting the connection string in the MyModels constructor:

base.Database.Connection.ConnectionString = xxx;

but this never seems to work. I always get the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

(This suggests it's not yet 'configured' to use SqlServerCE, and so is trying to connect to Sql Server. I think!)

Tracing the code, Database.Connection.ConnectionString hasn't been read from Web.config at this point, so I can't search and replace that, and, possibly, it's getting overwritten by the 'placeholder' conn string later in the pipeline.

I reckon this must be quite straightforward and I just can't find the 'hook'. Can anyone please help?

Answer 1

I'm not sure if it's a possible approach to solve your problem. My idea is to create a static "factory" class to instantiate your DbContext class. The code is like this:

public static class MyModelsFactory
{
    public static MyModels CreateMyModels()
    {
        string connectionString = string.Empty;
        // some code to retrieve your connectionString.
        return new MyModels(connectionString);
    }
}

And you may call CreateMyModels method wherever you need to instantiate MyModels class:

using (MyModels models = MyModelsFactory.CreateMyModels())
{
    //your code
}

It works for me in the case I need to change the connection string according to some logic at runtime. But my DbContext class is generated automatically by classic EF, not code first.