INSERTing many rows with Entity Framework 6 beta 1 and SQL Server Compact

In this blog post I will demonstrate a couple of improvements for adding many entities to a Entity Framework based database. You can read more about the beta 1 release here, and Julie Lerman highlights some of the features that were available in the alpha here. For all full list of EF 6 features, see the list here.

Here we will look at getting started with Entity Framework 6 beta 1, and a couple of improvements that makes adding many rows to a SQL Server Compact database via Entity Framework feasible, and also have look at using my SqlCeBulkCopy library to do the same.

I will use a console app for this project in order to focus on the Entity Framework code. To get started, launch Visual Studio, and create a new Console Application. Lets call it EF6Test.

Now let’s add Entity Framework 6 beta 1 SQL Server Compact package. Launch the NuGet Package Mangager Console (from Tools, Other Windows) and run this command:

PM> Install-Package EntityFramework.SqlServerCompact -Pre

(The –Pre switch allows you to install pre-release packages)

You should now see several messages in the window, the last one being:

Successfully added 'EntityFramework.SqlServerCompact 6.0.0-beta1-20603' to EF6Test.

This process has added a number of DLL references to the project, and added an app.config file to the project, with an entityFramework section that specifies the SQL Server Compact default connection factory:

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
    <parameters>
      <parameter value="System.Data.SqlServerCe.4.0" />
    </parameters>
  </defaultConnectionFactory>
  <providers>
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
  </providers>
</entityFramework>

Now add a using statement:

using System.Data.Entity;

And add the following 2 classes before “class Program”, these define our single test table and our DbContext:

    public class Student
    {
        public int StudentId { get; set; }
        public string Name { get; set; }
    }

    public class StudentContext : DbContext
    {
        public DbSet<Student> Students { get; set; }
    }

Now add the following code to the Main method:

   1:              Stopwatch sw = new Stopwatch();

   2:              bool useSqlCeBulkCopy = false;

   3:              var students = CreateStudents();

   4:   

   5:              Database.SetInitializer(new DropCreateDatabaseAlways<StudentContext>());

   6:              

   7:              using (var db = new StudentContext())

   8:              {

   9:                  db.Database.Initialize(true);

  10:                  if (!useSqlCeBulkCopy)

  11:                  {

  12:                      sw.Restart();

  13:                      //AddRange rulez, no need for db.Configuration.AutoDetectChangesEnabled = false;

  14:                      db.Students.AddRange(students);

  15:                      sw.Stop();

  16:                      

  17:                      Console.WriteLine(

  18:                          "Added 8000 entities in {0}", sw.Elapsed.ToString());

  19:                      

  20:                      sw.Restart();

  21:                      int recordsAffected = db.SaveChanges();

  22:                      sw.Stop();

  23:   

  24:                      Console.WriteLine(

  25:                          "Saved {0} entities in {1}", recordsAffected, sw.Elapsed.ToString());

  26:                      

  27:                  }

  28:                  Console.ReadKey();

  29:              }

  30:          }

  31:   

  32:          private static List<Student> CreateStudents()

  33:          {

  34:              var students = new List<Student>();

  35:              for (int i = 0; i < 8000; i++)

  36:              {

  37:                  var student = new Student { Name = Guid.NewGuid().ToString() };

  38:                  students.Add(student);

  39:              }

  40:              return students;

  41:          }

The CreateStudents method simply creates a List object with 8000 Student objects. A new database is created on each run (line 5) and the students are added to the StudentContext DbContext, using the excellent new AddRange method, similar to the LINQ to SQL InsertAllOnSubmit method. With EF5 you only had the Add method, and to get reasonable performance, you had to use the cryptic db.Configuration.AutoDetectChangesEnabled = false statement.

With SQL Server Compact and EF5, inserting 8000 rows takes about 58 seconds on my PC, and it may even time out on yours… Thanks to the fact that Entity Framework is now open source on CodePlex I was able to submit  a bug fix, which  got accepted for EF6, so the process now takes about 8 seconds on my PC.

To compare, let’s add the SqlCeBulkCopy NuGet package and perform the same process using that. In the Package Manager Console. type

PM> Install-Package ErikEJ.SqlCeBulkCopy

Before Console.ReadKey, paste the following code:

                else
                {
                    using (SqlCeBulkCopy bcp = new SqlCeBulkCopy(db.Database.Connection.ConnectionString))
                    {
                        bcp.DestinationTableName = "Students";
                        sw.Restart();
                        bcp.WriteToServer(students);
                        sw.Stop();

                        Console.WriteLine(
                            "Saved {0} entities using SqlCeBulkCopy in {1}", students.Count, sw.Elapsed.ToString());
                    }
                }

And set useSqlCeBulkCopy = true.On my machine this takes about 150 ms! So despite the improvements made in EF6 beta 1, for larger data loads, I suggest you use SqlCeBulkCopy, and as you can see from the code above, it is very easy to integrate in an Entity Framework context.

You can download the completed project from here: http://sdrv.ms/18NaRmW