エンティティフレームワークと外部キーオブジェクトを使用したSQLBulkCopy挿入

c# entity-framework sqlbulkcopy sql-server

質問

私は、ETLツールの負荷層としてEF6を使用しています。ただし、変換の複雑さのためにコードで実行する必要があり、ターゲットDBはEFモデルから構築されています(SSIS、直接SQLクエリなど)。バッチに挿入されるレコードの数は、 100,000レコードを超えることができますDBContext.AddRange()メソッドを使用すると信じられないほど遅いわけではありませんが、メモリ使用量が非常に高い( 1GBを超える)

例として、私は次のデータクラスを持っています(メモリに作成されています)

    public class Foo
    {
        public long FooID { get; set; }
        public string SomeProperty { get; set; }
        public decimal AverageFlightSpeedOfUnladenSwallow { get; set; }
        public IEnumerable<Bar> Bars { get; set; }
    }

    public class Bar
    {
        public long BarID { get; set; }
        public Foo Foo { get; set; }
        public long FooID { get; set; }
        public string FavoriteColour { get; set; }
    }
dbContext.Foos.AddRange(ListOfFoos); //Pre constructed list of Foos
dbContext.Bars.AddRange(ListOfBars); //Pre constructed list of Bars (parent Foo items populated, FooID is not)
dbContext.SaveChanges();

私は、SQLBulkCopy( SqlBulkCopyとEntity Frameworkhttp: //archive.msdn.microsoft.com/LinqEntityDataReader/)を使用してインポートできるように、LINQエンティティデータリーダーを使用してIList<Foo>をデータリーダーに変換できるようにしていIList<Foo> Release / ProjectReleases.aspx?ReleaseId = 389 )。

要件
List<Bar>は親FooクラスのIDを持ちません。 Entityフレームワークはこれをうまく処理しますが、SqlBulkCopyで同じ機能を取得する方法がわかりません。それを行うためのいくつかの方法はありますか?

受け入れられた回答

そう、

EFユーザーがデータベースのスキーマを変更できる場合は、この問題解決方法を実行することができます。

  1. テーブルにGUID列を追加する
  2. メモリ内の各オブジェクトをGUIDで識別する
  3. 識別GUIDを含む一括挿入による値の挿入
  4. それらを選択して、挿入されたGUIDにマップします
  5. GUID列を削除する

これを行うためのコードがいくつかあります。少し汚れていて最適化されていませんが、オリジナルのタスクは30MBのメモリと1分の処理まで最小限に抑えられています

public static class ForeignKeyBulkInsert
{
    private const string GUID_COLUMN_NAME = "GUID_SURROGATE_KEY";

    public static string GetTableName<T>(this ObjectContext context) where T : class
    {
        string sql = context.CreateObjectSet<T>().ToTraceString();
        Regex regex = new Regex("FROM (?<table>.*) AS");
        Match match = regex.Match(sql);

        string table = match.Groups["table"].Value;
        return table;
    }

    public static void AddRange<TEntity>(this DbContext db, IEnumerable<TEntity> range, bool importForeignKeyIDs = false)
        where TEntity : class
    {
        Dictionary<Guid, TEntity> lookup = new Dictionary<Guid, TEntity>();

        var objectContext = ((IObjectContextAdapter)db).ObjectContext;
        var os = objectContext.CreateObjectSet<TEntity>();
        bool hasAutoGeneratedKey = os.EntitySet.ElementType.KeyProperties.Any();
        Type entityType = typeof(TEntity);

        if (importForeignKeyIDs)
        {
            var foreignKeyProperties = os.EntitySet.ElementType.NavigationProperties.Where(x => x.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One);
            foreach (var foreignKeyProperty in foreignKeyProperties)
            {
                var foreignKeyIdProperty = foreignKeyProperty.GetDependentProperties().First();
                var parentKeyProperty = foreignKeyProperty.ToEndMember.GetEntityType().KeyMembers.First();
                PropertyInfo foreignKeyPropertyInfo = null;
                Type parentType = null;
                PropertyInfo parentKeyPropertyInfo = null;
                PropertyInfo foreignKeyIdPropertyInfo = null;
                foreach (var item in range)
                {
                    entityType.GetProperty(foreignKeyProperty.Name).GetValue(item);
                    if (foreignKeyPropertyInfo == null)
                        foreignKeyPropertyInfo = entityType.GetProperty(foreignKeyProperty.Name);
                    if (parentType == null)
                        parentType = foreignKeyPropertyInfo.GetValue(item).GetType();
                    if (parentKeyPropertyInfo == null)
                        parentKeyPropertyInfo = parentType.GetProperty(parentKeyProperty.Name);
                    if (foreignKeyIdPropertyInfo == null)
                        foreignKeyIdPropertyInfo = entityType.GetProperty(foreignKeyIdProperty.Name);

                    var foreignKey = foreignKeyPropertyInfo.GetValue(item);
                    if (foreignKey == null)
                        break;

                    var parentKey = parentKeyPropertyInfo.GetValue(foreignKey);
                    foreignKeyIdPropertyInfo.SetValue(item, parentKey);
                }
            }
        }

        string tableName = objectContext.GetTableName<TEntity>();
        var entityReader = range.AsDataReader(GUID_COLUMN_NAME, lookup);

        if (hasAutoGeneratedKey)
        {
            try
            {
                db.Database.ExecuteSqlCommand(string.Format("ALTER TABLE {0} ADD [{1}] uniqueidentifier null", tableName, GUID_COLUMN_NAME));
            }
            catch (Exception)
            {
                db.Database.ExecuteSqlCommand(string.Format("ALTER TABLE {0} DROP COLUMN [{1}]", tableName, GUID_COLUMN_NAME));
                db.Database.ExecuteSqlCommand(string.Format("ALTER TABLE {0} ADD [{1}] uniqueidentifier null", tableName, GUID_COLUMN_NAME));
            }
        }
        try
        {
            var connection = db.Database.Connection as SqlConnection;
            connection.Open();
            using (SqlBulkCopy cpy = new SqlBulkCopy(connection))
            {
                cpy.BulkCopyTimeout = 0;
                cpy.DestinationTableName = tableName;
                cpy.WriteToServer(entityReader);
                connection.Close();
            }

            if (hasAutoGeneratedKey)
            {
                db.Database.Connection.Open();
                var comm = db.Database.Connection.CreateCommand();
                comm.CommandText = string.Format("SELECT * FROM {0} WHERE [{1}] is not null", tableName, GUID_COLUMN_NAME);
                try
                {
                    using (var reader = comm.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Guid surrogateKey = Guid.Parse(reader[GUID_COLUMN_NAME].ToString());
                            TEntity entity = lookup[surrogateKey];
                            var keyProperty = entityType.GetProperty(os.EntitySet.ElementType.KeyMembers.First().Name);
                            keyProperty.SetValue(entity, reader[keyProperty.Name]);
                        }
                    }
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    //This should never occur
                    db.Database.Connection.Close();
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (hasAutoGeneratedKey)
                db.Database.ExecuteSqlCommand(string.Format("ALTER TABLE {0} DROP COLUMN [{1}]", tableName, GUID_COLUMN_NAME));
        }

    }
}

人気のある回答

いいえ、SQLバルクコピーでこれを行う直接的な方法はありません。

SQL Bulkcopyはデータベースに非常に近いため、非常に高速です。 ORMはFK / PK関係を処理するが、遅いという欠点がある。

あなたのデータモデルに応じて、この質問のようなことをすることができます:データテーブルのバッチを作成する

SQLの一括コピーYYYYMMDDの問題



ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ