Creating SQL schemas using Entity Framework Code First

As you might have read in my previous post, I used SQL schemas to partition data between tenants. Being part of the true definition of SaaS, a new tenant should be up and running within minutes without any human intervention. With my suggested approach, we need to do two things:

• Registering a new tenant
• Attaching users to the tenant

In this article, I am going to focus on the first item whereas the second one shouldn’t be a big deal if you are planning to create software as a service.

As soon as a new user subscribes to the application, the back-end needs to verify whether there is a schema for this tenant. Using a unique code, the following code excerpt will do just so:

If this method returns true, then the DbContext for this schema can be created at runtime (see my previous post). If this method returns false, it means there is no schema with this code just yet. In that case, we need to create the schema and all code first tables. The trick here is to retrieve the SQL scripts for creating objects from the DbContext schema, which unfortunately can only be retrieved from the ObjectContext class. Therefore you’ll need to cast the DbContext class to the ObjectContext (in order to do this, don’t forget to call the SetInitializer method). The returning value is a string with SQL code, which can be executed using the DbContext’s ExecuteSQLCommand method, as you can see in the method below:

Agreed, it’s not exactly a very clean way to achieve it but it does work fine – for now at least. In terms of upgrading, this approach has an interesting side effect: in combination with SSDT you can manage your versioning really well. If you are following the best practices in ALM (using source control systems such as VSTS for example), you could easily update the targeted schemas and ensure that schema is matched to the rest of the applications (including the models).