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?

score 1 · Accepted Answer · answered Jun 08 '15 at 10:13

1

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

answered Jun 08 '15 at 10:13

ErikEJ

36,894
5
64
101

The problem I have with SqlBulkCopy is that this class has no WriteToServer method which takes an IEnumerable like my List. The schema in Database1 is entirely different from the schema in Database2 so that I can't define ColumnMappings between the two (in my case it's more than just a mapping). At the moment I can't see how to use DataRow[], DataTable or DataReader as possible parameters for WriteToServer in SqlBulkCopy for my specific problem. Can you please help me with that? – user2653422 Jun 08 '15 at 11:29
You can Google for some solution to that, I use a library called Sailent.Data that translates a List to a DbDataReader – ErikEJ Jun 08 '15 at 14:49
Thank you, I found a solution [here](http://stackoverflow.com/questions/564366/convert-generic-list-enumerable-to-datatable/564373#564373). It's damn fast :) – user2653422 Jun 08 '15 at 19:12

Entity Framework Seeding with SqlCeBulkCopy returns error

1 Answers1