Entity Framework Core and SQLBulkCopy

Question

I have for a long time used SqlBulkCopy with Entity Framework. I have just migrated to Entity Framework Core. Before I did something like:

var itemsDT = jobs.ToDataTable<ProfileJob>();

using (var connection = new SqlConnection(_connStr))
{
    connection.Open();

    using (var bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.BatchSize = 1000;
        bulkCopy.DestinationTableName = "dbo.JobRemoveUniquePermissionsFailed";
        bulkCopy.WriteToServer(itemsDT);
    }
}

The ToDataTable was a large extension class build on the old Entity Framework. I haven't found any samples on something similar with core.

Does anyone have a small sample how to use SqlBulkCopy with EF Core?

David Browne - Microsoft · Accepted Answer · 2019-12-08 23:58:01Z

6

I have a small code sample that covers this here. There's just a single code file you add to your project, giving you .AsDataReader() and .ToDataTable() extension methods. EG:

    static int SendOrders(int totalToSend)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        con.Open();
        using (SqlTransaction tran = con.BeginTransaction())
        {
          var newOrders =
                  from i in Enumerable.Range(0, totalToSend)
                  select new Order
                  {
                    customer_name = "Customer " + i % 100,
                    quantity = i % 9,
                    order_id = i,
                    order_entry_date = DateTime.Now
                  };
      SqlBulkCopy bc = new SqlBulkCopy(con,
        SqlBulkCopyOptions.CheckConstraints |
        SqlBulkCopyOptions.FireTriggers |
        SqlBulkCopyOptions.KeepNulls, tran);

      bc.BatchSize = 1000;
      bc.DestinationTableName = "order_queue";
      bc.WriteToServer(newOrders.AsDataReader()); 

      tran.Commit();
    }
    con.Close();

  }

  return totalToSend;

}

edited Dec 8, 2019 at 23:58

answered Dec 8, 2019 at 22:40

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas Segato Over a year ago

Tested and worked! Thanks alot - highly appreciated.

Thomas Segato Over a year ago

Is it possible to get identity column somehow with this code?

Collectives™ on Stack Overflow

Entity Framework Core and SQLBulkCopy

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related