EF大数据批量添加性能问题（续）

昨天在园子里发了一篇如题的文章EF大数据批量添加性能问题，就引来一大堆的吐槽，我认为知识就应该这样分享出来，不然总以为自己很了不起；再说说昨天那篇文章，很多自认为很牛逼的人都评论说把SaveChanges()放在for循环外面，我不知道他们有没有亲自去尝试过，反正我尝试了，然而并没什么卵用。

下面是我按照他们说的进行更改后的代码：

  public ActionResult Add(ItemDetails entity)

         {

             var sw = new Stopwatch();

             var count = ;

             //var counts = 0;

             sw.Start();

             using (var db = new ShoppingDBConn())

             {

                 for (var i = ; i < ; i++)

                 {

                     var data = new ItemDetails

                     {

                         AddedBy = entity.AddedBy,

                         Description = entity.Description,

                         Image_Name = entity.Image_Name,

                         Item_Name = entity.Item_Name,

                         Item_Price = entity.Item_Price

                     };

                     db.ItemDetails.Add(data);

                 }

                 count = db.SaveChanges();

             }

             sw.Stop();

             var date = sw.Elapsed;

             return Json(string.Format("总耗时：{0},添加数量：{1}", date, count));

         }

运行耗时：

再看看AddRange方式：

         public ActionResult Add(ItemDetails entity)

         {

             var sw = new Stopwatch();

             var count = ;

             //var counts = 0;

             sw.Start();

             using (var db = new ShoppingDBConn())

             {

                 var list = new List<ItemDetails>();

                 for (var i = ; i < ; i++)

                 {

                     list.Add(new ItemDetails

                     {

                         AddedBy = entity.AddedBy,

                         Description = entity.Description,

                         Image_Name = entity.Image_Name,

                         Item_Name = entity.Item_Name,

                         Item_Price = entity.Item_Price

                     });

                 }

                 db.ItemDetails.AddRange(list);

                 count = db.SaveChanges();

             }

             sw.Stop();

             var date = sw.Elapsed;

             return Json(string.Format("总耗时：{0},添加数量：{1}", date, count));

         }

耗时情况：

不过还好有几位给出了很好的建议，用SqlBulkCopy，下面是优化后的代码，比上面任何一种都要快好几倍：

         public void BulkInsertAll<T>(IEnumerable<T> entities)

         {

             entities = entities.ToArray();

             var cons=new ShoppingDBConn();

             string cs = cons.Database.Connection.ConnectionString;

             var conn = new SqlConnection(cs);

             conn.Open();

             Type t = typeof(T);

             var bulkCopy = new SqlBulkCopy(conn)

             {

                 DestinationTableName = t.Name

             };

             var properties = t.GetProperties().Where(EventTypeFilter).ToArray();

             var table = new DataTable();

             foreach (var property in properties)

             {

                 Type propertyType = property.PropertyType;

                 if (propertyType.IsGenericType &&

                     propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))

                 {

                     propertyType = Nullable.GetUnderlyingType(propertyType);

                 }

                 table.Columns.Add(new DataColumn(property.Name, propertyType));

             }

             foreach (var entity in entities)

             {

                 table.Rows.Add(properties.Select(

                   property => GetPropertyValue(

                   property.GetValue(entity, null))).ToArray());

             }

             bulkCopy.WriteToServer(table);

             conn.Close();

         }

         private bool EventTypeFilter(System.Reflection.PropertyInfo p)

         {

             var attribute = Attribute.GetCustomAttribute(p,

                 typeof(AssociationAttribute)) as AssociationAttribute;

             if (attribute == null) return true;

             if (attribute.IsForeignKey == false) return true;

             return false;

         }

         private object GetPropertyValue(object o)

         {

             if (o == null)

                 return DBNull.Value;

             return o;

         }

调用该方法：

  public ActionResult Add(ItemDetails entity)

         {

             var sw = new Stopwatch();

             var count = ;

             //var counts = 0;

             sw.Start();

             using (var db = new ShoppingDBConn())

             {

                 var list = new List<ItemDetails>();

                 for (var i = ; i < ; i++)

                 {

                     list.Add(new ItemDetails

                     {

                         AddedBy = entity.AddedBy,

                         Description = entity.Description,

                         Image_Name = entity.Image_Name,

                         Item_Name = entity.Item_Name,

                         Item_Price = entity.Item_Price

                     });

                     count++;

                 }

                 BulkInsertAll(list);

             }

             sw.Stop();

             var date = sw.Elapsed;

             return Json(string.Format("总耗时：{0},添加数量：{1}", date, count));

         }

总耗时情况：

比上一篇的拼接SQL都要快好几倍，在此很感谢@_April