SQLBulkCopy inserisce utilizzando Entity Framework con l'oggetto Chiave esterna

c# entity-framework sqlbulkcopy sql-server

Domanda

Sto usando EF6 come livello di carico di uno strumento ETL. Riconosco che ci sono strumenti migliori (come SSIS, query SQL dirette, ecc.) Tuttavia a causa della complessità della trasformazione, doveva essere fatto in codice, e il DB di destinazione è stato creato da un modello EF. Il numero di record inseriti in un batch può superare i 100.000 record. Questo non è incredibilmente lento da fare (usando il metodo DBContext.AddRange() ) ma l'utilizzo della memoria è estremamente alto (superiore a 1GB )

Per amor di esempi, ho le seguenti classi di dati (che vengono create in memoria)

    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();

Sto cercando di utilizzare il lettore LINQ Entity Data per abilitare la conversione di IList<Foo> in un lettore di dati in modo da poterlo importare utilizzando SQLBulkCopy ( SqlBulkCopy e Entity Framework , http://archive.msdn.microsoft.com/LinqEntityDataReader/ Release / ProjectReleases.aspx? ReleaseId = 389 ).

Requisiti
L' List<Bar> non avrà gli ID della classe padre Foo . Il framework Entity lo gestisce bene, ma non sono sicuro di come ottenere questa stessa funzionalità in SqlBulkCopy. C'è un modo per farlo?

Risposta accettata

Così,

Se il tuo utente EF ha la capacità di modificare lo schema nel database, puoi seguire questo metodo per risolvere il problema:

  1. Aggiungi una colonna GUID alla tabella
  2. Identificare ogni oggetto in memoria dal GUID
  3. Inserire i valori tramite l'inserimento in blocco, incluso il GUID di identificazione
  4. Selezionali nuovamente e mappali ai GUID inseriti
  5. Rilasciare la colonna GUID

Ecco un codice per fare proprio questo. È un po 'sporco e non ottimizzato, ma ha ridotto al minimo il compito originale fino a 30 MB di memoria e 1 minuto di elaborazione

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));
        }

    }
}

Risposta popolare

No, non esiste un modo diretto per farlo con SQL bulkcopy.

SQL Bulkcopy è molto vicino al database, quindi è molto veloce. L'ORM gestisce le relazioni FK / PK ma ha lo svantaggio di essere lento.

A seconda del tuo datamodel, potresti fare qualcosa di simile a questa domanda: popola batch di datatables

SQL Bulkcopy YYYYMMDD problema



Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow