C# entity framework entity framework core efcore migration custom migrations sql sqlserver database

Entity Framework Core - Custom Migrations

Posted by ryansouthgate on 22 May 2017

In this post I’m going to cover the steps needed when setting up Entity Framework Core Custom Migrations. Custom Migrations are not always needed in an application. Entity Framework Core will get most people/businesses quite far with their standard “out-of-the-box” functionality. However there are times where EntityFramework does not support a specific scenario, as I came to find out recently.

Example code for this post can be found here: https://github.com/ry8806/Blog-EFCore-CustomMigration

Spatial Data Types

Currently, Entity Framework Core does not have “built-in” support for SQL Server Spatial Data Types. This is midly inconvenient, however they do have this issue tracked - and are currently laying the ground work for this work to be completed (after EF Core v 2.0 is released)

I’ve been wanting to use SQL Spatial types as they allow you to perform distance calculations (nearest, furthest) as part of your SQL Query. This is great, as the other option would be to store Lat/Long as double in your db and load all your locations into memory and query them there, which is not ideal. Along with SQL supporting these types of queries, you get all the normal; ordering, filtering goodness that SQL is so good at.

Don’t Re-invent the wheel

It’s clear the EF Core team have this on their roadmap, but I need this functionality now. I’m not a fan of “re-inveting the wheel”, so I investigated a pretty clean (IMO) way to perform a “Custom Migration” which creates the table with the GeoTypes. I’m going to detail the process of performing Custom Migrations (with the SQL Spatial Types above) as an example use.

Let’s do it

First I’m going to assume you have Entity Framework installed in your project and you can successfully run dotnet ef from the command line and see the unicorn. If not - this guide will help you get up to speed.

Next up, we’re going to add a simple Data Model. This sample application is going to store Customers and their location (latitude and longitude).

The customer model:

public class Customer
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

The Application Db Context:

public class ApplicationDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public ApplicationDbContext() : base() { }
    public ApplicationDbContext(DbContextOptions options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        // Customize the ASP.NET Identity model and override the defaults if needed.
        // For example, you can rename the ASP.NET Identity table names and more.
        // Add your customizations after calling 
        base.OnModelCreating(builder);
        base.OnModelCreating(builder);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=localhost;Database=CustomerApp;Trusted_Connection=True;");
    }
}

If you go ahead and run dotnet ef migrations add Initial it’ll create a migration for your Customer class.

Running dotnet ef database update will create the Db and you’ll be able to see the Customer table.

The Custom Migration

As you can see from the GitHub Sample, I created a folder called Db>CustomMigrations This is where I intend to put all of the custom migrations for an application. This is just the way I envisage it being laid out, obviously you’re free to put them wherever you want.

The Custom Migration class looks like this:

namespace EFCustomMigrations.Db.Migrations
{
    [DbContext(typeof(ApplicationDbContext))]
    [Migration("CustomMigration_CustomerLocation")]
    public class CustomerLocationMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "CustomerLocation",
                columns: table => new
                {
                    ID = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_CustomerLocation", x => x.ID);
                });

            // Custom SQL here which creates the SQL Spatial Types
            migrationBuilder.Sql("ALTER TABLE CustomerLocation " +
                                 "ADD GeogCol1 geography, " +
                                 "GeogCol2 AS GeogCol1.STAsText()");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // TODO: Implemnent this how you see fit
            base.Down(migrationBuilder);
        }
    }
}

The Migration Attribute on the Class is the name which will appear in the table: dbo.__EFMigrationsHistory - This is just a unique identifier for the custom migration.

Important-Notice - the eagle-eyed among you will notice that the namespace differs from what Visual Studio would generate when you created the class through the Add>New Class dialog. I have changed the namespace manually, to match the namespace that EF Core generates for it’s migrations. This ensures that the Migrations get put in the usual folder (Migrations) and this custom migration can stay in it’s own folder. If you didn’t change the namespace to match the EF-Generated-Migration-Namespace (namespace EFCustomMigrations.Db.Migrations) and it was namespace EFCustomMigrations.Db.CustomMigrations, then you’d see all the EF Core Generated classes being dumped in that folder. Which is not what I want as I explicitly have tried to keep them apart.

Migrate Away!

Now, run dotnet ef migrations add Geo Then dotnet ef database update

The screen-grab below shows the Custom Migration key in the migrations table

Migrations table showing Custom Migration key

You’ll also see the new table CustomerLocations in SQL Server Management Studio. You can see the data types on the columns too:

Customer Location Column spatial data types

Where do we go now?

The next steps are to implement a (migrate) “Down” method, which, if we ever had to roll back the migration, would allow us to drop the table and remove any references to it.

I’ve not demonstrated querying for this data yet, please stay tuned as I’ll cover this in an upcoming post

If you have any questions/improvements get in touch on Twitter :)

comments powered by Disqus