Seeding large lookup table data with Entity Framework Code First migrations

Question

I'm about to start a new project and I'd like to use Entity Framework Code First migrations; i.e., write the database in code and have it all auto-generated for me and the schema updated etc.

However, my stumbling block is that I have one lookup table which I need to import and has over 2 million records (it's a post code lookup table).

My question is, how do you deal with such large pre-populated lookup tables within Entity Framework Code First migrations?

Answer 1

Your migration doesn't actually have to drop/recreate the whole table (and won't unless you specify that it should). Normally, the migrations just do the Up/Down methods to alter the table with additional columns, etc.

Do you actually need to drop the table? If so, do you really need to seed it from EF? The EF cost for doing 2 million inserts will be astounding, so if you could do it as a manual step with something more efficient (that will use bulk inserts), it would be very much preferred.

If I had to do that many inserts, I would probably break it out into SQL files and do something like what's mentioned here: EF 5 Code First Migration Bulk SQL Data Seeding