C# - Entity Framework - Large seed data code-first

Question

I'm going to create an initial table in my app to store all the cities/states of my country. This is a relative large data set: 5k+ registries.

Reading this post enlightened to me a good way to do this, altough I think that leaving a sql file, that will be imported by the EF, in the open is a security flaw.

The file format is irrelevant: I can make it a XLS or a TXT if I want; instead of executing it as a SQL command as show in the post I can simply read it as a stream and generate the objects as shown in the tutorial of the next hiperlink.

Reading this tutorial about data seed in code-first, I saw that the seed method will be executed in the database initialization process and the seed objects are generated in the seed method.

My questions: About the seed methods, what is the best approach, the SQL-file approach or the object approach? I personally think that the object approach is more secure, but can be slower, possibility that generates my second question: The seed method that is executed in the database initialization process, is executed ONLY when the DB is created? This is a little unclear to me.

Thanks.

Answer 1

1. Reference System.Data in your database project and add the NuGet package EntityFramework.BulkInsert.

2. Insert your data in the seed method if you detect that it's not there yet:

   protected override void Seed(BulkEntities context)
   {
       if (!context.BulkItems.Any())
       {
           var items = Enumerable.Range(0, 100000)
                     .Select(s => new BulkItem
                     {
                         Name = s.ToString(),
                         Status = "asdf"
                     });
   
           context.BulkInsert(items, 1000);                
       }
   }
   

Inserting 100,000 items takes about 3 seconds over here.