Advanced Techniques with Database Migrations

While Entity Framework's database migrations will automatically pick up on structure changes, there are times where we want to do a little bit more. Consider the following model:

public class Attachment
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime CreatedOn { get; set; }
    public DateTime ModifiedOn { get; set; }
    public string Path { get; set; }
    public long Size { get; set; }
    public string MimeType { get; set; }
}

We'll assume that this model has already been added to the database via a previous migration. Let's say that we'd like to give users the ability to change the file in a given attachment. More than that, let's say that instead of just keeping track of the current file, we would like to have a history of all files that a user has uploaded for versioning. We'll modify our model:

public class Attachment
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime CreatedOn { get; set; }
    public DateTime ModifiedOn { get; set; }

    public virtual List<AttachmentFile> AttachmentFiles { get; set; }
}

public class AttachmentFile
{
    public int Id { get; set; }
    public DateTime CreatedOn { get; set; }
    public string Path { get; set; }
    public long Size { get; set; }
    public string MimeType { get; set; }

    public Attachment { get; set; }
    public int AttachmentId { get; set; }
}

Instead of the Attachment model containing information about the file, it has a one-to-many relationship with AttachmentFiles. We will assume that the AttachmentFile with the latest date will be used as the "primary" attachment.

Now we need to carry this change to our database. Using the package manager console, we run "add-migration AddAttachmentFiles", and we come up with this:

public partial class AddAttachmentFiles : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "AttachmentFiles",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    CreatedOn = c.DateTime(nullable: false),
                    Path = c.String(),
                    Size = c.Single(nullable: false),
                    MimeType = c.String()
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("Attachments", t => t.AttachmentId, cascadeDelete: true)
            .Index(t => t.AttachmentId);
        DropColumn("Attachments", "Path");
        DropColumn("Attachments", "Size");
        DropColumn("Attachments", "MimeType");
    }

    public override void Down()
    {
        AddColumn("Attachments", "MimeType", c => c.String());
        AddColumn("Attachments", "Size", c => c.Single(nullable: false));
        AddColumn("Attachments", "Path", c => c.String());
        DropIndex("AttachmentFiles", new[] { "AttachmentId" });
        DropForeignKey("AttachmentFiles", "AttachmentId", "Attachments");
        DropTable("AttachmentFiles");
    }
}

This looks acceptable. This migration will create the new table and remove the desired columns from the Attachments table. But what about our attachment data in the Attachments table? There is no way for Entity Framework to know that we want to do anything with the data in the columns we're deleting from the Attachments table. So, we do it manually. The DbMigration class has a Sql() method we can use to execute raw SQL against our database. If we modify our migration:

public partial class AddAttachmentFiles : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "AttachmentFiles",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    CreatedOn = c.DateTime(nullable: false),
                    Path = c.String(),
                    Size = c.Single(nullable: false),
                    MimeType = c.String()
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("Attachments", t => t.AttachmentId, cascadeDelete: true)
            .Index(t => t.AttachmentId);

        Sql("");

        DropColumn("Attachments", "Path");
        DropColumn("Attachments", "Size");
        DropColumn("Attachments", "MimeType");
    }

    public override void Down()
    {
        AddColumn("Attachments", "MimeType", c => c.String());
        AddColumn("Attachments", "Size", c => c.Single(nullable: false));
        AddColumn("Attachments", "Path", c => c.String());

        Sql("");

        DropIndex("AttachmentFiles", new[] { "AttachmentId" });
        DropForeignKey("AttachmentFiles", "AttachmentId", "Attachments");
        DropTable("AttachmentFiles");
    }
}

we can write SQL queries that will be executed in the flow of commands. The first query will move data from the to-be-deleted columns in the Attachments table to the AttachmentFiles table:

INSERT INTO AttachmentFiles (CreatedOn, Path, Size, MimeType, AttachmentId)
SELECT CreatedOn, Path, Size, MimeType, Id AS AttachmentId FROM Attachments

This query selects only the relevant fields from the Attachments table and inserts them into the AttachmentFiles. Since there will be exactly one set of file data per Attachment record, there will be exactly one AttachmentFile per Attachment. This means that the "primary" AttachmentFile for each Attachment will by default be the previous contents of the Attachments table simply because it will be the only record.

The second query will move data from the AttachmentFiles table back into the re-created columns in the Attachments table:

UPDATE Attachments
SET Attachments.Path = af1.Path, Attachments.Size = af1.Size, 
    Attachments.MimeType = af1.MimeType
FROM Attachments
INNER JOIN AttachmentFiles af1 on af1.Id =
    (SELECT TOP 1 Id FROM AttachmentFiles af2
    WHERE af2.AttachmentId = Attachments.Id 
        ORDER BY af2.CreatedOn DESC)

This query is much more complicated, because we have many AttachmentFiles per Attachment, and we need to select out only one per Attachment. We do this by using a subquery that selects out the most recent AttachmentFile for a given AttachmentId.

Our final migration looks like this:

public partial class AddAttachmentFiles : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "AttachmentFiles",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    CreatedOn = c.DateTime(nullable: false),
                    Path = c.String(),
                    Size = c.Single(nullable: false),
                    MimeType = c.String()
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("Attachments", t => t.AttachmentId, cascadeDelete: true)
            .Index(t => t.AttachmentId);

        Sql("INSERT INTO AttachmentFiles " +
            "(CreatedOn, Path, Size, MimeType, AttachmentId) " +
            "SELECT CreatedOn, Path, Size, MimeType, Id AS AttachmentId " +
            "FROM Attachments");

        DropColumn("Attachments", "Path");
        DropColumn("Attachments", "Size");
        DropColumn("Attachments", "MimeType");
    }

    public override void Down()
    {
        AddColumn("Attachments", "MimeType", c => c.String());
        AddColumn("Attachments", "Size", c => c.Single(nullable: false));
        AddColumn("Attachments", "Path", c => c.String());

        Sql("UPDATE Attachments " +
            "SET Attachments.Path = af1.Path, Attachments.Size = af1.Size, " +
                "Attachments.MimeType = af1.MimeType " +
            "FROM Attachments " +
            "INNER JOIN AttachmentFiles af1 on af1.Id = " +
                "(SELECT TOP 1 Id FROM AttachmentFiles af2 " +
                "WHERE af2.AttachmentId = Attachments.Id " +
                    "ORDER BY af2.CreatedOn DESC)");"

        DropIndex("AttachmentFiles", new[] { "AttachmentId" });
        DropForeignKey("AttachmentFiles", "AttachmentId", "Attachments");
        DropTable("AttachmentFiles");
    }
}

For more information, check out our website.