Entity Framework 批量添加 MySQL 数据

通常我们在使用EntityFramework 自带的方法插入数据时，EF 自身的缺陷，通常只有 100 / ms 以内，当数据量达到数万条，甚至几十万条时，性能相当可怕，甚至可能会拖累整个站点或者App。生产批量插入脚本，然后导入数据库，性能会相当感人。下面是我的一些解决方法。

1，获取实体表名

/// <summary>
        /// 获取实体表名，默认使用Table特性上的名称，没有就取类名
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private string GetTableName(Type type)
        {
            string _type_name = String.Empty;

            object _table_attr_obj = type.GetCustomAttributes(typeof(TableAttribute), false).FirstOrDefault();
            if (_table_attr_obj != null)
            {
                TableAttribute _table_attr = _table_attr_obj as TableAttribute;
                _type_name = _table_attr.Name;
            }

            if (String.IsNullOrEmpty(_type_name))
                _type_name = type.Name;

            return _type_name;
        }

2，字段过滤和格式化

/// <summary>
        /// 字段数据转换和安全性处理
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        string SqlFilter(object value)
        {
            if (value == null)
                return "null";

            if (value is DateTime?)
            {
                DateTime? _value_datetime_nullable = value as DateTime?;
                return _value_datetime_nullable.HasValue ? SqlFilter(_value_datetime_nullable.Value.ToString("yyyy-MM-dd HH:mm:ss")) : "null";
            }
            else if (value is DateTime)
            {
                return SqlFilter(((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"));
            }
            else if (value is int?)
            {
                int? _value_int_nullable = value as int?;
                return _value_int_nullable.HasValue ? _value_int_nullable.Value.ToString() : "null";
            }
            else if (value is int)
            {
                return ((int)value).ToString();
            }
            else if (value is Decimal?)
            {
                Decimal? _value_decimal_nullable = value as Decimal?;
                return _value_decimal_nullable.HasValue ? _value_decimal_nullable.Value.ToString() : "null";
            }
            else if (value is decimal)
            {
                return ((decimal)value).ToString();
            }
            else if (value is bool?)
            {
                bool? _value_bool_nullable = value as bool?;
                if (!_value_bool_nullable.HasValue)
                    return "null";
                return _value_bool_nullable.Value ? "1" : "0";
            }
            else if (value is bool)
            {
                bool _value_bool = (bool)value;
                return _value_bool ? "1" : "0";
            }

            string text = value.ToString();

            text = text.Replace("'", "''");
            return $"'{text}'";
        }

3，批量生成SQL并执行

DbContext dba { get; set; }

        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="entities">要插入的数据集合</param>
        public void BulkInsert<T>(IList<T> entities) where T : class
        {
            if (entities == null
                || entities.Count == 0)
                return;

            //获取表名
            Type _type = typeof(T);
            string _table_name = this.GetTableName(_type);
            PropertyInfo[] _properties = _type.GetProperties();
             
            //获取需要插入的列的集合
            IDictionary<string, PropertyInfo> _insert_property_map = new Dictionary<string, PropertyInfo>(StringComparer.OrdinalIgnoreCase); 
            foreach (var _property in _properties)
            {
                IEnumerable<Attribute> _attributes = _property.GetCustomAttributes();
                //屏蔽的列，忽略
                NotMappedAttribute _notMapped = _attributes.Where(x => x is NotMappedAttribute).FirstOrDefault() as NotMappedAttribute;
                if (_notMapped != null)
                    continue;

                //列名，默认取配置的列名，没有就取字段名称
                ColumnAttribute _column = _attributes.Where(x => x is ColumnAttribute).FirstOrDefault() as ColumnAttribute;
                string _columnName = _property.Name;
                if (_column != null && !String.IsNullOrEmpty(_column.Name))
                {
                    _columnName = _column.Name;
                }

                KeyAttribute _key = _attributes.Where(x => x is KeyAttribute).FirstOrDefault() as KeyAttribute;
                if (_key != null)
                { 
                    DatabaseGeneratedAttribute _generated = _attributes.Where(x => x is DatabaseGeneratedAttribute).FirstOrDefault() as DatabaseGeneratedAttribute;
                    if (_generated != null)
                    {
                        //自动增长的主键列忽略，计算的主键列忽略
                        if (_generated.DatabaseGeneratedOption == DatabaseGeneratedOption.Identity)
                        {
                            continue;
                        }
                        else if (_generated.DatabaseGeneratedOption == DatabaseGeneratedOption.Computed)
                        { 
                            continue;
                        } 
                    }
                }


                _insert_property_map.Add(_columnName, _property);
            }

            //循环批量生成SQL
            StringBuilder _sql = new StringBuilder();
            int _entity_batch_size = 0;
            object[] parameters = new object[0];
            foreach (var item in entities)
            {
                if (_entity_batch_size == 0)
                {
                    _sql.Append($"INSERT INTO {_table_name}");
                    string _column_name_str = String.Join(",", _insert_property_map.Select(x => x.Key));
                    _sql.Append($"({_column_name_str})");
                    _sql.Append("VALUES "); 
                }
                else
                {
                    _sql.Append(",");
                }

                IList<string> _cell_value_list = new List<string>();
                foreach (var _property_item in _insert_property_map)
                {
                    object _property_value = _property_item.Value.GetValue(item);
                    string _property_value_str = SqlFilter(_property_value);
                    _cell_value_list.Add(_property_value_str);
                }

                _sql.Append("(");
                _sql.Append(String.Join(",", _cell_value_list));
                _sql.Append(")");

                _entity_batch_size++;

                //每1000条数据一个SQL包
                if (_entity_batch_size >= 1000)
                {
                    string _sql_text = _sql.ToString();
                    this.dba.Database.ExecuteSqlCommand(_sql_text, parameters);
                    _entity_batch_size = 0;
                    _sql.Clear();
                }
            }

            if (_sql.Length > 0)
            {
                string _sql_text = _sql.ToString();
                this.dba.Database.ExecuteSqlCommand(_sql_text, parameters);
                _sql.Clear();
            }

        }

 

转载于:https://my.oschina.net/u/4103146/blog/3026802