使用 SQL 大批量复制与您的 LINQ-to-Entities 数据上下文 - 第二部分

本技巧是对 先前技巧 的更新，适用于实体框架版本 5 和 POCO 对象。通过扩展生成的上下文类，使用部分类，我们使用 sql 大批量复制大大提高了插入速度。由于此版本的 EF 不像以前那样在生成的类上使用属性，因此我们必须深入到元数据工作区以查找列映射。而且，由于元数据工作区的属性和方法大多是非公共的，因此我们必须使用反射和 Invoke 方法来获取我们想要的内容。但最终，它仍然可以正常工作，所以这就是它。和之前一样，这篇文章也发布在 我的博客 上。

public partial class MyEntities
{
    public override int SaveChanges()
    {
        var objectContext = ((IObjectContextAdapter)this).ObjectContext;
        objectContext.CommandTimeout = 5 * 60;

        return base.SaveChanges();
    }

        
    public void BulkInsertAll<t>(T[] entities) where T : class
    {
        var conn = (SqlConnection)Database.Connection;

        conn.Open();
            
        Type t = typeof(T);
        Set(t).ToString();
        var objectContext = ((IObjectContextAdapter)this).ObjectContext;
        var workspace = objectContext.MetadataWorkspace;
        var mappings = GetMappings(workspace, objectContext.DefaultContainerName, typeof(T).Name);

        var tableName = GetTableName<t>();
        var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableName};

        // Foreign key relations show up as virtual declared 
        // properties and we want to ignore these.
        var properties = t.GetProperties().Where(p => !p.GetGetMethod().IsVirtual).ToArray();
        var table = new DataTable();
        foreach (var property in properties)
        {
            Type propertyType = property.PropertyType;

            // Nullable properties need special treatment.
            if (propertyType.IsGenericType &&
                propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                propertyType = Nullable.GetUnderlyingType(propertyType);
            }

            // Since we cannot trust the CLR type properties to be in the same order as
            // the table columns we use the SqlBulkCopy column mappings.
            table.Columns.Add(new DataColumn(property.Name, propertyType));
            var clrPropertyName = property.Name;
            var tableColumnName = mappings[property.Name]; 
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(clrPropertyName, tableColumnName));
        }

        // Add all our entities to our data table
        foreach (var entity in entities)
        {
            var e = entity;
            table.Rows.Add(properties.Select(property => 
               GetPropertyValue(property.GetValue(e, null))).ToArray());
        }

        // send it to the server for bulk execution
        bulkCopy.WriteToServer(table);

        conn.Close();
    }

    private string GetTableName<t>() where T : class
    {
        var dbSet = Set<t>();
        var sql = dbSet.ToString();
        var regex = new Regex(@"FROM (?.*) AS");
        var match = regex.Match(sql);
        return match.Groups["table"].Value;
    }

    private object GetPropertyValue(object o)
    {
        if (o == null)
            return DBNull.Value;
        return o;
    }

    private Dictionary<string,> GetMappings(MetadataWorkspace workspace, string containerName, string entityName)
    {
        var mappings = new Dictionary<string,>();
        var storageMapping = workspace.GetItem<globalitem>(containerName, DataSpace.CSSpace);
        dynamic entitySetMaps = storageMapping.GetType().InvokeMember(
            "EntitySetMaps",
            BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance,
            null, storageMapping, null);

        foreach (var entitySetMap in entitySetMaps)
        {
            var typeMappings = GetArrayList("TypeMappings", entitySetMap);
            dynamic typeMapping = typeMappings[0];
            dynamic types = GetArrayList("Types", typeMapping);

            if (types[0].Name == entityName)
            {
                var fragments = GetArrayList("MappingFragments", typeMapping);
                var fragment = fragments[0];
                var properties = GetArrayList("AllProperties", fragment);
                foreach (var property in properties)
                {
                    var edmProperty = GetProperty("EdmProperty", property);
                    var columnProperty = GetProperty("ColumnProperty", property);
                    mappings.Add(edmProperty.Name, columnProperty.Name);
                }
            }
        }

        return mappings;
    }

    private ArrayList GetArrayList(string property, object instance)
    {
        var type = instance.GetType();
        var objects = (IEnumerable)type.InvokeMember(property, BindingFlags.GetProperty | 
          BindingFlags.NonPublic | BindingFlags.Instance, null, instance, null);
        var list = new ArrayList();
        foreach (var o in objects)
        {
            list.Add(o);
        }
        return list;
    }

    private dynamic GetProperty(string property, object instance)
    {
        var type = instance.GetType();
        return type.InvokeMember(property, BindingFlags.GetProperty | 
          BindingFlags.NonPublic | BindingFlags.Instance, null, instance, null);
    }
}