Good database design is when responsibilities are separated, or else your ends up with a monolith trash bin database. The best way to achieve separation of responsibilities, is to have each responsibility in its own database, and disable cross querying. That will be a database for Accounts, Products, Emails etc depending of your business and its domain. One database for each domain.
In these cloud days, that be quite expensive. So we can settle with the next best. Schemas. The must famous schema on SQL Server is dbo. It is the default schema, and sadly, it is used in 99% of the cases, when schemas is applied.
When using ASP.NET 5 Identity with Entity Framework 7 and with Migrations, you will see the tables are putted in the dbo schema. Changing this behavior, is not straight forward.
The solution
You might recognize it. It is the code from template when creating an ASP.NET 5 Web site. Thou I have removed a little. I want to have the identity tables in the schema Accounts.
1. First override the OnModelCreating(Modelbuilder builder) method.
using Microsoft.AspNet.Identity.EntityFramework;
using Microsoft.Data.Entity;
namespace Example.Models
{
public class ApplicationUser : IdentityUser
{
}
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext()
{
}
protected override void OnModelCreating(ModelBuilder builder)
{
// Remenber to Create Schema in DB, until EF7 can handle Schemas correctly
builder.Entity<ApplicationUser>().ForRelational().Table("AspNetUsers", "Accounts");
builder.Entity<IdentityUserClaim<string>>().ForRelational().Table("AspNetUserClaims", "Accounts");
builder.Entity<IdentityUserLogin<string>>().ForRelational().Table("AspNetUserLogins", "Accounts");
builder.Entity<IdentityUserRole<string>>().ForRelational().Table("AspNetUserRoles", "Accounts");
builder.Entity<IdentityRole>().ForRelational().Table("AspNetRoles", "Accounts");
builder.Entity<IdentityRoleClaim<string>>().ForRelational().Table("AspNetRoleClaims", "Accounts");
base.OnModelCreating(builder);
}
}
}
2. This step might not seem gracefully, because it should be fully handled by Migrations in Entity Framework. But schemas and Entity Framework 7, is currently not working as desired. And it is not working with Migrations
Go to SQL Server Management Studio. If your database is not created at this point, create it. Then run CREATE SCHEMA <schema name>. In this case it will be CREATE SCHEMA Accounts. As mentioned, this part should have been handled by Migrations.
3. Run Migrations
4. Continue with your project :-)
Taken this a step further, it could be considered; having a DbContext for each domain of you app, and each of these contexts could have their own schemas.
