Entity Framework Seeding with SqlCeBulkCopy returns error

Question

I'm trying to seed my database with ~50K entries. I have two databases. The 50K entries will be generated by some queries/calculations on database2 (Database2, based on Database first approach) and will then be inserted into database1 (Database1, based on code first approach). The process is relatively slow, even with the hints of regular commits and contexts recreations (see here).

Remark: I know I could use some SQL constructs to copy the desired data from Database2 to Database1 directly, but the creation of the entities is a bit complicated (exclusions, random generation, etc.) and the SQL constructs would be very complex if even possible I think. Thus the detour over EF.

So I tried to use SqlCeBulkCopy (see here) like that:

public class DropCreateDatabaseAlwaysWithSeedData : DropCreateDatabaseAlways<MyContext>
{
    protected override void Seed(MyContext context)
    {
        using (var context2 = new MyContext2())
        {
            var testList = new List<MyEntity>();

            for (var i=0; i < 50000; i++)
            {
                // Some queries on context2 and calculations to get data
                testList.Add(new MyEntity());
            }
            using (var bcp = new SqlCeBulkCopy(context.Database.Connection.ConnectionString))
            {
                bcp.DestinationTableName = "MyEntity";
                bcp.WriteToServer(testList);
            }
        }
    }
}

The connection strings are the following:

< add name="MyDatabase" connectionString="Data Source=PC\SQLSERVER;Initial Catalog=Database;integrated security=True;pooling=False;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />

< add name="MyDatabase2" connectionString="metadata=res:///Initializers.DataModel.csdl|res:///Initializers.DataModel.ssdl|res://*/Initializers.DataModel.msl;provider=System.Data.SqlClient;provider connection string="data source=PC\SQLSERVER;initial catalog=Database2;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />

The problem is that I get a "System.ArgumentException" in EntityFramework.dll, because the key word "initial catalog" isn't supported. So there seems to be an issue with the initial catalog keyword within the connection string MyDatabase, but I don't know what's wrong. Any ideas?

ErikEJ ErikEJ · Accepted Answer · 2015-06-08T10:13:19

Use SqlBulkCopy (part of the built-in System.Data.SqlClient) rather than SqlCeBulkCopy. SqlCeBulkCopy only works for populating SQL Server Compact database files.

Entity Framework Seeding with SqlCeBulkCopy returns error

1 Answers