Using database migration insert fixed records in particular table using entity framework

Question

I have table properties in my MySQL database and my requirement is it should have fixed records with some default values. I want that table should get auto filled with the help of migration in code first approch of Entity Framework. I am dealing with migration first time so I want help in the same.

My current migration is as follows:

public override void Up()
        {
            CreateTable(
                "dbo.properties",
                c => new
                    {
                        id = c.Int(nullable: false, identity: true),
                        property_name = c.Int(nullable: false),
                        value = c.String(nullable: false, maxLength: 255, storeType: "nvarchar"),
                        type = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.id)
                .Index(t => t.property_name, unique: true, name: "uk_property_name");

        }

        public override void Down()
        {
            DropIndex("dbo.properties", "uk_property_name");
            DropTable("dbo.properties");
        }

score 1 · Accepted Answer · edited May 23 '17 at 11:46

Migrations have a Seeding Mechanism to populate data. Since this method runs every time you update-database you want to make sure the data doesn't get inserted multiple times. This is what the AddOrUpdate command is for, although in more complex situations you could just use standard LINQ:

if (!context.MyTable.Any(t => t.Id == "seedId") { // insert };

But I would just go with the AddOrUpdate in your scenario:

protected override void Seed(MyContext context)
{
    context.Properties.AddOrUpdate(x => x.Value,
        new Property() { Value = "My Prop Name 1", Property_Name = 1, Type = 1 },
        new Property() { Value = "My Prop Name 2", Property_Name = 2, Type = 2 },
        new Property() { Value = "My Prop Name 3", Property_Name = 3, Type = 1 }
        );
}

Note that since your id field is identity I don't include it but instead use Value to check if it already exists. If value can be duplicated or you need to control the Id you may need a different technique like this.

Using database migration insert fixed records in particular table using entity framework

1 Answers1