Generating seed code from existing database in ASP.NET MVC

Question

I wondered if anyone has encountered a similar challenge:

I have a database with some data that was ETL'ed (imported and transformed) in there from an Excel file. In my ASP.NET MVC web application I'm using Code First approach and dropping/creating every time database changes:

#if DEBUG
  Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MyDataContext>());
#endif

However, since the data in the Database is lost, I have to ETL it again, which is annoying.

Since, the DB will be dropped only on model change, I will have to tweak my ETL anyway, I know that. But I'd rather change my DB seed code.

Does anyone know how to take the contents of the database and generate seed code, assuming that both Models and SQL Tables are up to date?

EDIT 1: I'm planning to use the auto-generated Configuration.cs, and its Seed method, and then use AddOrUpdate() method to add data into the database: Here is Microsoft's Tutorial on migrations (specifically the "Set up the Seed method" section).

What do you mean by "seed code"? – Daniel Hilgarth Oct 07 '13 at 14:57 — Daniel Hilgarth, Oct 07 '13 at 14:57
I added an explanation to my question. – Arman Bimatov Oct 08 '13 at 15:09 — Arman Bimatov, Oct 08 '13 at 15:09

Emre Bolat · Answer 1 · 2016-10-18T13:27:34.153

Lets say we have a simple database table with 3750 records in it;

| Id   | Age | FullName        |
|------|-----|-----------------|
| 1    | 50  | Michael Jackson |
| 2    | 42  | Elvis Presley   |
| 3    | 48  | Whitney Houston |
| ...  | ... | ...             |
| 3750 | 57  | Prince          |

We want to create this table in our database with using auto-generated Configuration.cs file and its Seed() method.

protected override void Seed(OurDbContainer context)
{
    context.GreatestSingers.AddOrUpdate(
            p => p.Id,
            new GreatestSinger { Id = 1, Age = 50, FullName = "Michael Jackson" },
            new GreatestSinger { Id = 2, Age = 42, FullName = "Elvis Presley" },
            new GreatestSinger { Id = 3, Age = 48, FullName = "Whitney Houston" }
            );
}

This is what you should do. 3750 times!

But you already have this data in your existing database table. So we can use this existing data to create Seed() codes.

With the help of SQL String Concatenation;

SELECT
CONCAT('new GreatestSinger { Id = ', Id ,', Age = ', Age ,', FullName = "', FullName ,'" },') 
FROM GreatestSinger

will give us all the code needed to create 3750 rows of data.

Just copy/paste it into Seed() method. And from Package Manager Console;

Add-Migration SeedDBwithSingersData

Update-Database

score 8 · Accepted Answer · edited May 23 '17 at 12:31

Another way of seeding data is to run it as sql in an Up migration.

I have code that will read a sql file and run it

using System;
using System.Data.Entity.Migrations;
using System.IO;

public partial class InsertStandingData : DbMigration
{
    public override void Up()
    {
        var baseDir = AppDomain.CurrentDomain
                               .BaseDirectory
                               .Replace("\\bin", string.Empty) + "\\Data\\Sql Scripts";

        Sql(File.ReadAllText(baseDir + "\\StandingData.sql"));
    }

    public override void Down()
    {
        //Add delete sql here
    }
}

So if your ETL generates sql for you then you could use that technique.

The advantages of doing it in the Up method are

It will be quicker than doing it using AddOrUpdate because AddOrUpdate queries the database each time it is called to get any already existing entity.
You are normally going from a known state (e.g. empty tables) so you probably don't need to check whether data exists already. NB to ensure this then you should delete the data in the Down method so that you can tear all the way down and back up again.
The Up method does not run every time the application starts.

The Seed method provides convenience - and it has the advantage (!?) that it runs every time the application starts

But if you prefer to run the sql from there use ExecuteSqlCommand instead of Sql:

string baseDir = AppDomain.CurrentDomain.BaseDirectory.Replace("\\bin", string.Empty) 
              + "\\Data\\Sql Scripts";
string path = Path.Combine(baseDir, "StandingData");
foreach (string file in Directory.GetFiles(path, "*.sql"))
{
    context.Database.ExecuteSqlCommand(File.ReadAllText(file));
}

References:

Best way to incrementally seed data

Preparing for database deployment

Database Initializer and Migrations Seed Methods

It's in the same namespace as `DbMigration`! Answer edited to show the class and using statements — Colin, Oct 10 '13 at 08:18
They run when you call Update-Database http://msdn.microsoft.com/en-US/data/jj591621 — Colin, Oct 12 '13 at 06:59
Will this work if you're only seeding one table from the database? — Robert M., Dec 15 '16 at 04:11

Generating seed code from existing database in ASP.NET MVC

2 Answers2

Linked