Add or update data to existing database with entity framework

Question

I am trying to create a custom migration step to clean up some default values in our database. Basically we have some data that is either invalid and needs to be updated and in other cases it doesn't exist at all. The system is installed on several servers and a lot of this data was added manually at one point so its hard to know what server has what data.

What I want to do is create a migration step to clean it all up. If this value does not exist in the table i need to insert it. However if it does exist then either I updated it or just delete it then insert it. I am having a hard time figuring out how to do this.

Add-Migration DataCleanup

Create migration step

public partial class DataCleanup : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.InsertData(
            table: "Blogs",
            columns: new[] { "BlogId", "Url" },
            values: new object[] { 4, "http://sample4.com" });}

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DeleteData(
            table: "Blogs",
            keyColumn: "BlogId",
            keyValue: 4);
    }
}

This will work if the row didn't exist previously, but if the row did exist then I will need to update to be sure the value is the correct value. I am not to worried about the primary keys as this is a reference table they "should" be the same.

The only other option I can think of would be to run a truncate on these tables and then just run the inserts after.

migrationBuilder.Sql("TRUNCATE TABLE [Blogs]", true);

Note on down

I am leaning towards not being able to do the down at all. There will be no way of knowing what state this one server was in before i ran this.

Answer 1

First off i was unable to do a truncate on the tables there are foreign keys set up which meant that that was just not going to work.

I ended up doing a bunch of sql inserts that check first of the row exists and if it doesn then we insert it.

migrationBuilder.Sql("INSERT INTO IdentityResources (Description, DisplayName, Emphasize, Enabled, Name, Required, ShowInDiscoveryDocument) " +
                                 "SELECT 'Your email address', 'User email', 1, 1, 'email', 0, 1 " +
                                 "WHERE NOT EXISTS(SELECT * " +
                                                  "FROM IdentityResources " +
                                                  "WHERE name = 'email'); ", true);

Things got a little more complicated when i needed to add the key from the first insert.

migrationBuilder.Sql("INSERT INTO IdentityClaims (IdentityResourceId, Type) " +
                                         "SELECT id, 'email' " +
                                         "FROM IdentityResources " +
                                         "WHERE Name = 'email' " +
                                         "AND NOT EXISTS(SELECT * " +
                                         "FROM [IdentityClaims] " +
                                         "WHERE type = 'email')");

This all worked out in the end. A new install of the system builds the proper database and everyone gets updated to ensure that they have at least the required data.

Unfortunately removing the unneeded data will have to wait for another day.