Using Breeze/EntityFramework/WebAPI with multiple databases

Question

We currently have a Silverlight app that uses DevForce 2012. Like much of the Silverlight world, we've started porting over to HTML5. We will be using Angular backed by Breeze coupled with EntityFramework/WebAPI.

Each of our customers has its own database, all sharing the same model. Since we have several hundred customers, our web.config contains several hundred connection strings. When a user logs in, they enter their account code, which is directly linked to a connection string. DevForce has a concept of "data source extensions" which is what our Silverlight app uses to get the correct connection. So an example of our config would be

<connectionStrings>
   <add name="Entities_123" connectionString="myConnectionString" />    
   <add name="Entities_456" connectionString="myConnectionString2" />
   ...
</connectionStrings>

So a user enters "456" as their account code when they log in, we pass that value as the "data source extension" to DevForce, and that connection is associated to the user for the rest of the session thanks to DevForce magic.

What I have a hard time wrapping my head around is how to do a similar thing with Breeze/EF. I've scoured the web and cannot find any examples of how to use Breeze to connect to multiple databases without having to create multiple Controller/Context classes. I'm guessing I will need to use a DBContextFactory in some fashion, but I don't even know where to start.

Answer 1

I think this is as much a security issue as a database selection issue. Therefore, I'd continue your practice of having the server determine the database id based on the authenticated user.

The client should not know or influence the choice of database id directly. That's a private matter that belongs on the server.

Therefore, you don't have to make any changes on the client-side. From the client perspective, there is a single endpoint and that endpoint is the same for everyone.

Server (Web API)

You do not need a controller-per-database. You may want multiple controllers for other reasons but that's driven by other concerns, not this one.

In your (perhaps one-and-only) Web API controller you obtain the database id in some fashion. I don't know how you do it today in Silverlight + DevForce; it's likely the same approach in your Web API controller.

Your controller will instantiate an EFContextProvider ... or, better, a repository/unit-of-work component that wraps an EFContextProvider, passing along the database id.

You may not be able to obtain the database id in the controller's constructor because the request object isn't available at that time. In this example we'll tell the repository about it inside the controller's Initialize method.

Here is the beginning of a Web API controller that may work for you:

[BreezeController]
public class YourController : ApiController {
    private readonly YourRepository _repository;

    // default ctor
    public YourController() : this(null) { }

    // Test / Dependency Injection ctor.
    // Todo: inject via an IYourRepository interface rather than "new" the concrete class
    public YourController(YourRepository repository) {
        _repository = repository ?? new YourRepository();
    }

    protected override void Initialize(HttpControllerContext controllerContext) {
        base.Initialize(controllerContext);
        _repository.SetDatabaseId(getDatabaseId());
    }

    /// <summary>
    /// Get the DatabaseId from ???
    /// </summary>
    private string getDatabaseId() {
        try {
            return ...; // your logic here. The 'Request' object is available now
        } catch  {
            return String.Empty;
        }
    }

    ...
}

Of course YourRepository delays instantiation of the EFContextProvider until someone calls SetDatabaseId

Now if you weren't changing connection strings on the fly, you'd be done. But because you are defining the connection string at the last moment, you'll need to create a subclass of EFContextProvider and override the CreateContext method whose default implementation is:

protected virtual T CreateContext() // 'T' is your DbContext type
{
  return Activator.CreateInstance<T>();
}

Clearly you'll have to do something else ... whatever is appropriate for instantiating your DbContext connected to the database matching the supplied database id. This is the place for the DBContextFactory you mentioned. I'll assume you know how to take care of this.