Entity Framework Seed AddOrUpdate Issue

, I’ve been using EF Code First since it was in CTP and hopped onto manual migrations bandwagon as soon as I could. I’m still on the bandwagon by the way and we use EF Code First and Manual Migrations to manage production (Microsoft Azure SQL), test and dev deployments of Resgrid. But this is not to say there aren’t issues, one of the big ones has been with EF’s Seed method.

For the uninitiated, EF has a method called Seed that’s part of the code configuration when your using EF Migrations (Automatic and Manual). This method allows EF to ensure the baseline required data is inside your database. For example type tables, test users\companies, etc. Every time EF starts up it runs through the Seed method to ensure your baseline data is correct. The recommend method for this is AddOrUpdate. Which allows you to set a unique key (or compound/complex key) and insert the record of it’s missing or update it if it’s changed (i.e. you fixed a spelling mistake in the type name field). Pretty cool huh?

Boy oh boy can that bite you in the ass. I’ve had seed records inserted thousands of times due to AddOrUpdate. The issue seems to arise when you use the following syntax:

context.Customers.AddOrUpdate(a => a.CustomerId,
    new Customer
    {
        CustomerId = 1,
        Name = "Test Customer",
        ShowWelcome = true,
        TimeZone = "Eastern Standard Time"
    });

CustomerId is a Primary Key, most of the time this will insert just fine. But then there are the fun times, where Customer won’t have an Id of 1, but instead 2, or 3 or 100. EF just gives up, for whatever reason and silently inserts a record that you didn’t want into the the table and can keep inserting it. This seems to happen when you have multiple inserts of the same record type.

In addition to having many records inserted you can get inserts completely out of order. Take for example this code.

context.Customers.AddOrUpdate(a => a.CustomerId,
    new Customer
    {
        CustomerId = 1,
        Name = "Test Customer",
        ShowWelcome = true,
        RefId = "50DEC5DB-2612-4D6A-97E3-2F04B7228C85",
        TimeZone = "Eastern Standard Time"
    },
    new Customer
    {
        CustomerId = 2,
        Name = "Test Customer 2",
        ShowWelcome = true,
        RefId = "AFA1E979-FCA9-417B-A03D-69C0588FAD71",
        TimeZone = "Pacific Standard Time"
    },
    new Customer
    {
        CustomerId = 3,
        Name = "Test Customer 3",
        ShowWelcome = true,
        RefId = "1AA0DCF8-4220-4C86-A10B-B6F8B3C0CA44",
        TimeZone = "Central Standard Time"
    }};

Based on it we would expect Test Customers to be record 1, Test Customer 2 to be record 2 and Test Customer 3 to be record 3. Would you believe me if I told you Test Customer 3 got CustomerId 1, Test Customer got CustomerId 2 and Test Customer 2 got CustomerId 3. WTF?!?!

Some solutions involve using a non-Primary Key field and instead focusing on another field in this example Name, or a combination like Name and Timezone (obviously I wouldn’t use Name and Timezone, you would want to use truly unique values). The syntax for a compound key looks like this:

context.Customers.AddOrUpdate(a => new { a.Name, a.SSN },
    new Customer
    {
        Name = "Test Customer",
        SSN = "555-55-5555",
        ShowWelcome = true,
        RefId = "50DEC5DB-2612-4D6A-97E3-2F04B7228C85",
        TimeZone = "Eastern Standard Time"
    }};

So the issue seems that before the end of the Seed method (for at least the mix up of the inserted objects) it re’orders them internal before they are sent to the server to be executed.

How did I get around this? I added context.SaveChanges(); in between each of the inserts that were causing issues.

context.Customers.AddOrUpdate(a => a.CustomerId,
    new Customer
    {
        CustomerId = 1,
        Name = "Test Customer",
        ShowWelcome = true,
        RefId = "50DEC5DB-2612-4D6A-97E3-2F04B7228C85",
        TimeZone = "Eastern Standard Time"
    }};
    
context.SaveChanges();    

context.Customers.AddOrUpdate(a => a.CustomerId,
    new Customer
    {
        CustomerId = 2,
        Name = "Test Customer 2",
        ShowWelcome = true,
        RefId = "AFA1E979-FCA9-417B-A03D-69C0588FAD71",
        TimeZone = "Pacific Standard Time"
    }};
    
context.SaveChanges();
    
context.Customers.AddOrUpdate(a => a.CustomerId,
    new Customer
    {
        CustomerId = 3,
        Name = "Test Customer 3",
        ShowWelcome = true,
        RefId = "1AA0DCF8-4220-4C86-A10B-B6F8B3C0CA44",
        TimeZone = "Central Standard Time"
    }};
    
context.SaveChanges();

Now my records are created properly. Somewhere in the chain of adding more objects to the database these referenced objects get re-ordered in the execution chain.