Manage Views with EF Code First + Migration Support

I had to search around for a while to find a solution to the problem of adding a view to an Entity Framework Code First Context. I wanted my solution to work with a view that does not yet exist. The view must be created (and removed if necessary) by a migration. I am fine with the fact that the view has the same name as the property in the context.

You can find an explanation about how to use pre-existing views in a context, as well as ideas on how to add a view via migrations. What was missing for me was a complete example that combines both aspects. So here is mine :-)

We start with a simple User class,

public class User
{
    public Guid Id { get; set; }
    public string Login { get; set; }
    public bool IsAdmin { get; set; }
}

and add this to a very simple context

public class ViewContext : DbContext
{

    public ViewContext()
        : base("ViewContext")
    {

    }
    
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasKey(u => u.Id)
            .Property(u => u.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        base.OnModelCreating(modelBuilder);
    }
}

Using the Package Manager Console we enable migrations and create an initial migration and apply it to the database:

enable-migrations
add-migration initial
update-database

Now I want to create two views, one that filters on admin users and the other that filters on normal users.

The first thing to to is, to create a new class that reflects the view to be created, adding a DbSet using that class to the context and create a migration for it.

public class AdminUser
{
    public Guid Id { get; set; }
    public string Login { get; set; }
}

public class ViewContext : DbContext
{
    public DbSet<AdminUser> AdminUsers { get; set; }
}

add-migration viewadminusers

The migration created after this change contains a CreateTable-Statement. This is not the desired thing to happen, so this has to be changed. It is possible to use the DbMigrations Sql-Method for this an pass in the SQL as a string. This is an easy solution, but it did not satisfy me. I wanted a solution that looks more like native migrations, like so:

public partial class viewadminusers : DbMigration
{
    public override void Up()
    {
        CreateView("dbo.AdminUsers", "SELECT * FROM Users WHERE IsAdmin = 1");
    }
    
    public override void Down()
    {
        DropView("dbo.AdminUsers");
    }
}

So I digged a little deeper, and found an approach to add new operations to DbMigrations. The example shows how to add check constraints, and it works all the same for views.

First of all we need a POCO class to describe our operation:

public class CreateViewOperation : MigrationOperation
{

    public CreateViewOperation(string name, string sql)
        : base(null)
    {
        this.Name = name;
        this.Sql = sql;
    }

    public string Name { get; set; }
    public string Sql { get; set; }

    public override bool IsDestructiveChange
    {
        get
        {
            return false;
        }
    }
}

The class must derive from MigrationOperation and holds properties for the name and the SQL-Body of the view. To be able to use this Operation in a Migration, we can use extension methods on DbMigration:

public static class CreateViewExtension
{
    public static void CreateView(this DbMigration migration, string name, string sql)
    {
        var createViewOperation = new CreateViewOperation(name, sql);
        ((IDbMigration)migration).AddOperation(createViewOperation);
    }

    public static void DropView(this DbMigration migration, string name)
    {
        var dropViewOperation = new DropViewOperation(name);
        ((IDbMigration)migration).AddOperation(dropViewOperation);
    }
}

With this code in place we can write the migration almost like stated in the above example, the only difference is that one must use the this-prefix in front of the method name which is close enough.

this.CreateView("dbo.AdminUsers", "SELECT * FROM Users WHERE IsAdmin = 1");

The thing that acutally translate the operation to SQL is a subclass of SqlServerMigrationSqlGenerator:

public class CustomSqlServerMigrationGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(MigrationOperation migrationOperation)
    {
        if (migrationOperation is CreateViewOperation)
        { 
            Generate(migrationOperation as CreateViewOperation);
            return;
        }

        base.Generate(migrationOperation);
    }

    protected virtual void Generate(CreateViewOperation createViewOperation)
    {
        using (var writer = Writer())
        {
            writer.WriteLine(
                "EXEC ('CREATE View {0} AS {1}')",
                Name(createViewOperation.Name),
                createViewOperation.Sql
            );
            Statement(writer);
        }
    }
}

The override of the general Generate(MigrationOperation) method checks if the operation is of type CreateViewOperation and hands over to the specialized Generate(CreateViewOperation) method. This method generates the SQL code using a special TextWriter and adds the resulting SQL using the Statement method.

Almost done! The only piece left is to add this SQL-Generator to the migrations configuration.

internal sealed class Configuration : DbMigrationsConfiguration<EFCodeFirstViews.ViewContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationGenerator());
    }
}

The SetSqlGenerator method of the DbMigrationsConfigurationType lets you add a SQL generator per provider type.

And this is it. The only thing that has to be done manually is to change the generated migrations file to use the CreateView method instead of create table.

Happy viewing!