public async Task CheckHasIdentityAsync(DbContext context)
        {
            int hasIdentity = 0;

            if (HasSinglePrimaryKey)
            {
                var sqlConnection      = context.Database.GetDbConnection();
                var currentTransaction = context.Database.CurrentTransaction;
                try
                {
                    if (currentTransaction == null)
                    {
                        if (sqlConnection.State != ConnectionState.Open)
                        {
                            await sqlConnection.OpenAsync().ConfigureAwait(false);
                        }
                    }
                    using (var command = sqlConnection.CreateCommand())
                    {
                        if (currentTransaction != null)
                        {
                            command.Transaction = currentTransaction.GetDbTransaction();
                        }
                        command.CommandText = SqlQueryBuilder.SelectIsIdentity(FullTableName, PropertyColumnNamesDict[PrimaryKeys[0]]);
                        using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
                        {
                            if (reader.HasRows)
                            {
                                while (await reader.ReadAsync().ConfigureAwait(false))
                                {
                                    hasIdentity = (int)reader[0];
                                }
                            }
                        }
                    }
                }
                finally
                {
                    if (currentTransaction == null)
                    {
                        sqlConnection.Close();
                    }
                }
            }
            HasIdentity = hasIdentity == 1;
        }
示例#2
0
        public void CheckHasIdentity(DbContext context)
        {
            int hasIdentity = 0;

            if (HasSinglePrimaryKey)
            {
                var sqlConnection      = context.Database.GetDbConnection();
                var currentTransaction = context.Database.CurrentTransaction;
                try
                {
                    if (currentTransaction == null)
                    {
                        if (sqlConnection.State != ConnectionState.Open)
                        {
                            sqlConnection.Open();
                        }
                    }
                    using (var command = sqlConnection.CreateCommand())
                    {
                        if (currentTransaction != null)
                        {
                            command.Transaction = currentTransaction.GetDbTransaction();
                        }
                        command.CommandText = SqlQueryBuilder.SelectIsIdentity(FullTableName, PropertyColumnNamesDict[PrimaryKeys[0]]);
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    hasIdentity = reader[0] == DBNull.Value ? 0 : (int)reader[0];
                                }
                            }
                        }
                    }
                }
                finally
                {
                    if (currentTransaction == null)
                    {
                        sqlConnection.Close();
                    }
                }
            }
            HasIdentity = hasIdentity == 1;
        }
        public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
        {
            tableInfo.InsertToTempTable = true;
            await tableInfo.CheckHasIdentityAsync(context).ConfigureAwait(false);

            await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)).ConfigureAwait(false);

            if (tableInfo.CreatedOutputTable)
            {
                await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true)).ConfigureAwait(false);

                if (tableInfo.TimeStampColumnName != null)
                {
                    context.Database.ExecuteSqlCommand(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType));
                }
            }
            try
            {
                await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false);

                await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false);

                if (tableInfo.CreatedOutputTable)
                {
                    try
                    {
                        await tableInfo.LoadOutputDataAsync(context, entities).ConfigureAwait(false);
                    }
                    finally
                    {
                        if (!tableInfo.BulkConfig.UseTempDB)
                        {
                            await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
                        }
                    }
                }
            }
            finally
            {
                if (!tableInfo.BulkConfig.UseTempDB)
                {
                    await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);
                }
            }
        }
        public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
        {
            tableInfo.InsertToTempTable = true;
            if (tableInfo.BulkConfig.UpdateByProperties == null || tableInfo.BulkConfig.UpdateByProperties.Count() == 0)
            {
                tableInfo.CheckHasIdentity(context);
            }

            context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
            if (tableInfo.CreatedOutputTable)
            {
                context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true));
                if (tableInfo.TimeStampColumnName != null)
                {
                    context.Database.ExecuteSqlCommand(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType));
                }
            }
            try
            {
                Insert(context, entities, tableInfo, progress);
                context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo, operationType));

                if (tableInfo.CreatedOutputTable)
                {
                    try
                    {
                        tableInfo.LoadOutputData(context, entities);
                    }
                    finally
                    {
                        if (!tableInfo.BulkConfig.UseTempDB)
                        {
                            context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
                        }
                    }
                }
            }
            finally
            {
                if (!tableInfo.BulkConfig.UseTempDB)
                {
                    context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
                }
            }
        }
        public void UpdateOutputIdentity <T>(DbContext context, IList <T> entities) where T : class
        {
            var entitiesWithOutputIdentity = context.Set <T>().FromSql(SqlQueryBuilder.SelectFromTable(this.FullTempOutputTableName, this.PrimaryKeyFormated)).ToList();

            if (this.BulkConfig.PreserveInsertOrder) // Updates PK in entityList
            {
                var accessor = TypeAccessor.Create(typeof(T));
                for (int i = 0; i < this.NumberOfEntities; i++)
                {
                    accessor[entities[i], this.PrimaryKey] = accessor[entitiesWithOutputIdentity[i], this.PrimaryKey];
                }
            }
            else // Clears entityList and then refill it with loaded entites from Db
            {
                entities.Clear();
                ((List <T>)entities).AddRange(entitiesWithOutputIdentity);
            }
        }
        public async Task <bool> CheckTableExistAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken)
        {
            bool tableExist         = false;
            var  sqlConnection      = context.Database.GetDbConnection();
            var  currentTransaction = context.Database.CurrentTransaction;

            try
            {
                if (currentTransaction == null)
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        await sqlConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
                    }
                    ;
                }
                using (var command = sqlConnection.CreateCommand())
                {
                    if (currentTransaction != null)
                    {
                        command.Transaction = currentTransaction.GetDbTransaction();
                    }
                    command.CommandText = SqlQueryBuilder.CheckTableExist(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
                    using (var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
                    {
                        if (reader.HasRows)
                        {
                            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
                            {
                                tableExist = (int)reader[0] == 1;
                            }
                        }
                    }
                }
            }
            finally
            {
                if (currentTransaction == null)
                {
                    sqlConnection.Close();
                }
            }
            return(tableExist);
        }
示例#7
0
        public static async Task TruncateAsync(DbContext context, TableInfo tableInfo)
        {
            string providerName = context.Database.ProviderName;

            // -- SQL Server --
            if (providerName.EndsWith(DbServer.SqlServer.ToString()))
            {
                await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.TruncateTable(tableInfo.FullTableName));
            }
            // -- Sqlite --
            else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
            {
                context.Database.ExecuteSqlRaw(SqlQueryBuilder.DeleteTable(tableInfo.FullTableName));
            }
            else
            {
                throw new SqlProviderNotSupportedException(providerName);
            }
        }
        public bool CheckTableExist(DbContext context, TableInfo tableInfo)
        {
            bool tableExist         = false;
            var  sqlConnection      = context.Database.GetDbConnection();
            var  currentTransaction = context.Database.CurrentTransaction;

            try
            {
                if (currentTransaction == null)
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        sqlConnection.Open();
                    }
                }
                using (var command = sqlConnection.CreateCommand())
                {
                    if (currentTransaction != null)
                    {
                        command.Transaction = currentTransaction.GetDbTransaction();
                    }
                    command.CommandText = SqlQueryBuilder.CheckTableExist(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                tableExist = (int)reader[0] == 1;
                            }
                        }
                    }
                }
            }
            finally
            {
                if (currentTransaction == null)
                {
                    sqlConnection.Close();
                }
            }
            return(tableExist);
        }
示例#9
0
        public async Task LoadOutputDataAsync <T>(DbContext context, IList <T> entities) where T : class
        {
            if (BulkConfig.SetOutputIdentity && HasSinglePrimaryKey)
            {
                string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
                var    entitiesWithOutputIdentity = await QueryOutputTableAsync <T>(context, sqlQuery).ToListAsync().ConfigureAwait(false);

                UpdateEntitiesIdentity(entities, entitiesWithOutputIdentity);
            }
            if (BulkConfig.CalculateStats)
            {
                int numberUpdated = await GetNumberUpdatedAsync(context);

                BulkConfig.StatsInfo = new StatsInfo
                {
                    StatsNumberUpdated  = numberUpdated,
                    StatsNumberInserted = entities.Count - numberUpdated
                };
            }
        }
示例#10
0
        // Compiled queries created manually to avoid EF Memory leak bug when using EF with dynamic SQL:
        // https://github.com/borisdj/EFCore.BulkExtensions/issues/73
        // Once the following Issue gets fixed(expected in EF 3.0) this can be replaced with code segment: DirectQuery
        // https://github.com/aspnet/EntityFrameworkCore/issues/12905
        #region CompiledQuery
        public void LoadOutputData <T>(DbContext context, IList <T> entities) where T : class
        {
            if (BulkConfig.SetOutputIdentity && HasSinglePrimaryKey)
            {
                string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
                var    entitiesWithOutputIdentity = QueryOutputTable <T>(context, sqlQuery).ToList();
                UpdateEntitiesIdentity(entities, entitiesWithOutputIdentity);
            }
            if (BulkConfig.CalculateStats)
            {
                string sqlQueryCount = SqlQueryBuilder.SelectCountIsUpdateFromOutputTable(this);

                int numberUpdated = GetNumberUpdated(context);
                BulkConfig.StatsInfo = new StatsInfo
                {
                    StatsNumberUpdated  = numberUpdated,
                    StatsNumberInserted = entities.Count - numberUpdated
                };
            }
        }
        public void CheckHasIdentity(DbContext context)
        {
            var sqlConnection      = context.Database.GetDbConnection();
            var currentTransaction = context.Database.CurrentTransaction;

            try
            {
                if (currentTransaction == null)
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        sqlConnection.Open();
                    }
                }
                using (var command = sqlConnection.CreateCommand())
                {
                    if (currentTransaction != null)
                    {
                        command.Transaction = currentTransaction.GetDbTransaction();
                    }
                    command.CommandText = SqlQueryBuilder.SelectIdentityColumnName(TableName, Schema);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                IdentityColumnName = reader.GetString(0);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (currentTransaction == null)
                {
                    sqlConnection.Close();
                }
            }
        }
        public async Task CheckHasIdentityAsync(DbContext context, CancellationToken cancellationToken)
        {
            var sqlConnection      = context.Database.GetDbConnection();
            var currentTransaction = context.Database.CurrentTransaction;

            try
            {
                if (currentTransaction == null)
                {
                    if (sqlConnection.State != ConnectionState.Open)
                    {
                        await sqlConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
                    }
                }
                using (var command = sqlConnection.CreateCommand())
                {
                    if (currentTransaction != null)
                    {
                        command.Transaction = currentTransaction.GetDbTransaction();
                    }
                    command.CommandText = SqlQueryBuilder.SelectIdentityColumnName(TableName, Schema);
                    using (var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
                    {
                        if (reader.HasRows)
                        {
                            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
                            {
                                IdentityColumnName = reader.GetString(0);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (currentTransaction == null)
                {
                    sqlConnection.Close();
                }
            }
        }
示例#13
0
 internal async Task LoadOutputDataAsync<T>(DbContext context, Type type, IList<T> entities, CancellationToken cancellationToken) where T : class
 {
     bool hasIdentity = OutputPropertyColumnNamesDict.Any(a => a.Value == IdentityColumnName);
     if (BulkConfig.SetOutputIdentity && hasIdentity)
     {
         string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
         //var entitiesWithOutputIdentity = await QueryOutputTableAsync<T>(context, sqlQuery).ToListAsync(cancellationToken).ConfigureAwait(false); // TempFIX
         var entitiesWithOutputIdentity = (typeof(T) == type) ? QueryOutputTable<T>(context, sqlQuery).ToList() :
             QueryOutputTable(context, type, sqlQuery).Cast<T>().ToList();
         UpdateEntitiesIdentity(type, entities, entitiesWithOutputIdentity);
     }
     if (BulkConfig.CalculateStats)
     {
         int numberUpdated = await GetNumberUpdatedAsync(context, cancellationToken).ConfigureAwait(false);
         BulkConfig.StatsInfo = new StatsInfo
         {
             StatsNumberUpdated = numberUpdated,
             StatsNumberInserted = entities.Count - numberUpdated
         };
     }
 }
        public static void Read <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class
        {
            Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);

            context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));

            try
            {
                Insert(context, entities, tableInfo, progress);

                tableInfo.PropertyColumnNamesDict = tableInfo.OutputPropertyColumnNamesDict;

                var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);

                tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;

                //var existingEntities = context.Set<T>().FromSql(q).AsNoTracking().ToList(); // Not used because of EF Memory leak bug
                Expression <Func <DbContext, IQueryable <T> > > expression = null;
                if (tableInfo.BulkConfig.TrackingEntities)
                {
                    expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery);
                }
                else
                {
                    expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery).AsNoTracking();
                }

                var compiled         = EF.CompileQuery(expression); // instead using Compiled queries
                var existingEntities = compiled(context).ToList();

                tableInfo.UpdateReadEntities(entities, existingEntities);
            }
            finally
            {
                if (!tableInfo.BulkConfig.UseTempDB)
                {
                    context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
                }
            }
        }
        // Compiled queries created manually to avoid EF Memory leak bug when using EF with dynamic SQL:
        // https://github.com/borisdj/EFCore.BulkExtensions/issues/73
        // Once the following Issue gets fixed(expected in EF 3.0) this can be replaced with code segment: DirectQuery
        // https://github.com/aspnet/EntityFrameworkCore/issues/12905
        #region CompiledQuery
        public void LoadOutputData <T>(DbContext context, IList <T> entities) where T : class
        {
            bool hasIdentity = PropertyColumnNamesDict.Any(a => a.Value == IdentityColumnName);

            if (BulkConfig.SetOutputIdentity && hasIdentity)
            {
                string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
                var    entitiesWithOutputIdentity = QueryOutputTable <T>(context, sqlQuery).ToList();
                UpdateEntitiesIdentity(entities, entitiesWithOutputIdentity);
            }
            if (BulkConfig.CalculateStats)
            {
                string sqlQueryCount = SqlQueryBuilder.SelectCountIsUpdateFromOutputTable(this);

                int numberUpdated = GetNumberUpdated(context);
                BulkConfig.StatsInfo = new StatsInfo
                {
                    StatsNumberUpdated  = numberUpdated,
                    StatsNumberInserted = entities.Count - numberUpdated
                };
            }
        }
示例#16
0
        internal void LoadOutputData <T>(DbContext context, Type type, IList <T> entities) where T : class
        {
            bool hasIdentity = OutputPropertyColumnNamesDict.Any(a => a.Value == IdentityColumnName);

            if (BulkConfig.SetOutputIdentity && hasIdentity)
            {
                string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
                var    entitiesWithOutputIdentity = (typeof(T) == type) ? QueryOutputTable <T>(context, sqlQuery).ToList() :
                                                    QueryOutputTable(context, type, sqlQuery).Cast <T>().ToList();
                UpdateEntitiesIdentity(type, entities, entitiesWithOutputIdentity);
            }
            if (BulkConfig.CalculateStats)
            {
                int numberUpdated = GetNumberUpdated(context);
                int numberDeleted = GetNumberDeleted(context);
                BulkConfig.StatsInfo = new StatsInfo
                {
                    StatsNumberUpdated  = numberUpdated,
                    StatsNumberDeleted  = numberDeleted,
                    StatsNumberInserted = entities.Count - numberUpdated - numberDeleted
                };
            }
        }
示例#17
0
        public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
        {
            tableInfo.InsertToTempTable = true;
            await tableInfo.CheckHasIdentityAsync(context).ConfigureAwait(false);

            await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName)).ConfigureAwait(false);

            if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
            {
                await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
            }
            try
            {
                await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false);

                await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false);

                await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);

                if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
                {
                    await tableInfo.UpdateOutputIdentityAsync(context, entities).ConfigureAwait(false);

                    await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
                }
            }
            catch (Exception ex)
            {
                if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
                {
                    await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
                }
                await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);

                throw ex;
            }
        }
示例#18
0
        public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
        {
            tableInfo.InsertToTempTable = true;
            tableInfo.CheckHasIdentity(context);

            context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName));
            if (tableInfo.BulkConfig.SetOutputIdentity)
            {
                context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName));
            }
            try
            {
                Insert(context, entities, tableInfo, progress);
                context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo, operationType));
                context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));

                if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasSinglePrimaryKey)
                {
                    try
                    {
                        tableInfo.UpdateOutputIdentity(context, entities);
                        var dp = SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName);
                        context.Database.ExecuteSqlCommand(dp);
                    }
                    catch (Exception ex)
                    {
                        context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
                        throw ex;
                    }
                }
            }
            catch (Exception ex)
            {
                context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
                throw ex;
            }
        }
        public static async Task ReadAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class
        {
            Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);

            await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));

            try
            {
                await InsertAsync(context, entities, tableInfo, progress);

                tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;

                var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);

                //var existingEntities = await context.Set<T>().FromSql(sqlQuery).ToListAsync();
                Expression <Func <DbContext, IQueryable <T> > > expression = null;
                if (tableInfo.BulkConfig.TrackingEntities)
                {
                    expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery);
                }
                else
                {
                    expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery).AsNoTracking();
                }
                var compiled         = EF.CompileAsyncQuery(expression);
                var existingEntities = (await compiled(context).ToListAsync().ConfigureAwait(false));

                tableInfo.UpdateReadEntities(entities, existingEntities);
            }
            finally
            {
                if (!tableInfo.BulkConfig.UseTempDB)
                {
                    await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
                }
            }
        }
示例#20
0
        public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
        {
            string providerName = context.Database.ProviderName;             // "Microsoft.EntityFrameworkCore.*****"

            // -- SQL Server --
            if (providerName.EndsWith(DbServer.SqlServer.ToString()))
            {
                var connection  = OpenAndGetSqlConnection(context, tableInfo.BulkConfig);
                var transaction = context.Database.CurrentTransaction;
                try
                {
                    using (var sqlBulkCopy = GetSqlBulkCopy((SqlConnection)connection, transaction, tableInfo.BulkConfig))
                    {
                        bool useFastMember = tableInfo.HasOwnedTypes == false &&                                           // With OwnedTypes DataTable is used since library FastMember can not (https://github.com/mgravell/fast-member/issues/21)
                                             tableInfo.ColumnNameContainsSquareBracket == false &&                                             // FastMember does not support escaped columnNames  ] -> ]]
                                             tableInfo.ShadowProperties.Count == 0 &&                                      // With Shadow prop. Discriminator (TPH inheritance) also not used because FastMember is slow for Update (https://github.com/borisdj/EFCore.BulkExtensions/pull/17)
                                             !tableInfo.ConvertibleProperties.Any() &&                                     // With ConvertibleProperties FastMember is slow as well
                                             !tableInfo.HasAbstractList &&                                                 // AbstractList not working with FastMember
                                             !tableInfo.BulkConfig.UseOnlyDataTable;
                        bool setColumnMapping = useFastMember;
                        tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
                        try
                        {
                            if (useFastMember)
                            {
                                using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
                                {
                                    sqlBulkCopy.WriteToServer(reader);
                                }
                            }
                            else
                            {
                                var dataTable = GetDataTable <T>(context, entities, sqlBulkCopy, tableInfo);
                                sqlBulkCopy.WriteToServer(dataTable);
                            }
                        }
                        catch (InvalidOperationException ex)
                        {
                            if (ex.Message.Contains(ColumnMappingExceptionMessage))
                            {
                                if (!tableInfo.CheckTableExist(context, tableInfo))
                                {
                                    context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));                                     // Will throw Exception specify missing db column: Invalid column name ''
                                    context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
                                }
                            }
                            throw ex;
                        }
                    }
                }
                finally
                {
                    if (transaction == null)
                    {
                        connection.Close();
                    }
                }
            }
            // -- SQLite --
            else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
            {
                var connection  = OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
                var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
                try
                {
                    var command = GetSqliteCommand(context, entities, tableInfo, connection, transaction);

                    var typeAccessor = TypeAccessor.Create(typeof(T), true);
                    int rowsCopied   = 0;
                    foreach (var item in entities)
                    {
                        LoadSqliteValues(tableInfo, typeAccessor, item, command);
                        command.ExecuteNonQuery();
                        SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
                    }
                }
                finally
                {
                    if (tableInfo.BulkConfig.SqliteTransaction == null)
                    {
                        transaction.Commit();
                        transaction.Dispose();
                    }
                    if (tableInfo.BulkConfig.SqliteConnection == null)
                    {
                        connection.Close();
                    }
                }
            }
            else
            {
                throw new SqlProviderNotSupportedException(providerName);
            }
        }
示例#21
0
        private static void Merge <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
        {
            string providerName = context.Database.ProviderName;

            // -- SQL Server --
            if (providerName.EndsWith(DbServer.SqlServer.ToString()))
            {
                tableInfo.InsertToTempTable = true;
                tableInfo.CheckHasIdentity(context);

                context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
                if (tableInfo.CreatedOutputTable)
                {
                    context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true));
                    if (tableInfo.TimeStampColumnName != null)
                    {
                        context.Database.ExecuteSqlRaw(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType));
                    }
                }

                bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
                try
                {
                    Insert(context, type, entities, tableInfo, progress);

                    if (keepIdentity && tableInfo.HasIdentity)
                    {
                        context.Database.OpenConnection();
                        context.Database.ExecuteSqlRaw(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true));
                    }

                    context.Database.ExecuteSqlRaw(SqlQueryBuilder.MergeTable(tableInfo, operationType));

                    if (tableInfo.CreatedOutputTable)
                    {
                        tableInfo.LoadOutputData(context, type, entities);
                    }
                }
                finally
                {
                    if (!tableInfo.BulkConfig.UseTempDB)
                    {
                        if (tableInfo.CreatedOutputTable)
                        {
                            context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
                        }
                        context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
                    }

                    if (keepIdentity && tableInfo.HasIdentity)
                    {
                        context.Database.ExecuteSqlRaw(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false));
                        context.Database.CloseConnection();
                    }
                }
            }
            // -- SQLite --
            else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
            {
                var connection  = OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
                var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
                try
                {
                    var command = GetSqliteCommand(context, type, entities, tableInfo, connection, transaction);

                    var typeAccessor = TypeAccessor.Create(type, true);
                    int rowsCopied   = 0;
                    foreach (var item in entities)
                    {
                        LoadSqliteValues(tableInfo, typeAccessor, item, command);
                        command.ExecuteNonQuery();
                        SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
                    }

                    if (operationType != OperationType.Delete && tableInfo.BulkConfig.SetOutputIdentity && tableInfo.IdentityColumnName != null)
                    {
                        command.CommandText = SqlQueryBuilderSqlite.SelectLastInsertRowId();
                        long   lastRowIdScalar         = (long)command.ExecuteScalar();
                        var    identityPropertyInteger = false;
                        var    accessor             = TypeAccessor.Create(type, true);
                        string identityPropertyName = tableInfo.PropertyColumnNamesDict.SingleOrDefault(a => a.Value == tableInfo.IdentityColumnName).Key;
                        if (accessor.GetMembers().FirstOrDefault(x => x.Name == identityPropertyName)?.Type == typeof(int))
                        {
                            identityPropertyInteger = true;
                        }
                        for (int i = entities.Count - 1; i >= 0; i--)
                        {
                            if (identityPropertyInteger)
                            {
                                accessor[entities[i], identityPropertyName] = (int)lastRowIdScalar;
                            }
                            else
                            {
                                accessor[entities[i], identityPropertyName] = lastRowIdScalar;
                            }

                            lastRowIdScalar--;
                        }
                    }
                }
                finally
                {
                    if (tableInfo.BulkConfig.SqliteTransaction == null)
                    {
                        transaction.Commit();
                        transaction.Dispose();
                    }
                    if (tableInfo.BulkConfig.SqliteConnection == null)
                    {
                        connection.Close();
                    }
                }
            }
            else
            {
                throw new SqlProviderNotSupportedException(providerName);
            }
        }
示例#22
0
        public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) where T : class
        {
            string providerName = context.Database.ProviderName;

            // -- SQL Server --
            if (providerName.EndsWith(DbServer.SqlServer.ToString()))
            {
                tableInfo.InsertToTempTable = true;
                await tableInfo.CheckHasIdentityAsync(context, cancellationToken).ConfigureAwait(false);

                await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);

                if (tableInfo.CreatedOutputTable)
                {
                    await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true), cancellationToken).ConfigureAwait(false);

                    if (tableInfo.TimeStampColumnName != null)
                    {
                        await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType), cancellationToken).ConfigureAwait(false);
                    }
                }

                bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
                try
                {
                    await InsertAsync(context, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);

                    if (keepIdentity && tableInfo.HasIdentity)
                    {
                        await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);

                        await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true), cancellationToken).ConfigureAwait(false);
                    }

                    await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType), cancellationToken).ConfigureAwait(false);

                    if (tableInfo.CreatedOutputTable)
                    {
                        await tableInfo.LoadOutputDataAsync(context, entities, cancellationToken).ConfigureAwait(false);
                    }
                }
                finally
                {
                    if (!tableInfo.BulkConfig.UseTempDB)
                    {
                        if (tableInfo.CreatedOutputTable)
                        {
                            await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName), cancellationToken).ConfigureAwait(false);
                        }
                        await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName), cancellationToken).ConfigureAwait(false);
                    }

                    if (keepIdentity && tableInfo.HasIdentity)
                    {
                        await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false), cancellationToken).ConfigureAwait(false);

                        context.Database.CloseConnection();
                    }
                }
            }
            // -- SQLite --
            else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
            {
                var connection = await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);

                var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
                try
                {
                    var command = GetSqliteCommand(context, entities, tableInfo, connection, transaction);

                    var typeAccessor = TypeAccessor.Create(typeof(T), true);
                    int rowsCopied   = 0;
                    foreach (var item in entities)
                    {
                        LoadSqliteValues(tableInfo, typeAccessor, item, command);
                        await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);

                        SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
                    }

                    if (operationType != OperationType.Delete && tableInfo.BulkConfig.SetOutputIdentity && tableInfo.IdentityColumnName != null)
                    {
                        command.CommandText = SqlQueryBuilderSqlite.SelectLastInsertRowId();
                        long lastRowIdScalar = (long)await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);

                        int    lastRowId            = (int)lastRowIdScalar;
                        var    accessor             = TypeAccessor.Create(typeof(T), true);
                        string identityPropertyName = tableInfo.PropertyColumnNamesDict.SingleOrDefault(a => a.Value == tableInfo.IdentityColumnName).Key;
                        for (int i = entities.Count - 1; i >= 0; i--)
                        {
                            accessor[entities[i], identityPropertyName] = lastRowId;
                            lastRowId--;
                        }
                    }
                }
                finally
                {
                    if (tableInfo.BulkConfig.SqliteTransaction == null)
                    {
                        transaction.Commit();
                        transaction.Dispose();
                    }
                    if (tableInfo.BulkConfig.SqliteConnection == null)
                    {
                        connection.Close();
                    }
                }
            }
            else
            {
                throw new SqlProviderNotSupportedException(providerName);
            }
        }
示例#23
0
        public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken)
        {
            string providerName = context.Database.ProviderName;             // "Microsoft.EntityFrameworkCore.*****"

            // -- SQL Server --
            if (providerName.EndsWith(DbServer.SqlServer.ToString()))
            {
                var connection = await OpenAndGetSqlConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);

                var transaction = context.Database.CurrentTransaction;
                try
                {
                    using (var sqlBulkCopy = GetSqlBulkCopy((SqlConnection)connection, transaction, tableInfo.BulkConfig))
                    {
                        bool useFastMember = tableInfo.HasOwnedTypes == false &&
                                             tableInfo.ColumnNameContainsSquareBracket == false &&
                                             tableInfo.ShadowProperties.Count == 0 &&
                                             !tableInfo.ConvertibleProperties.Any() &&
                                             !tableInfo.HasAbstractList &&
                                             !tableInfo.BulkConfig.UseOnlyDataTable;
                        bool setColumnMapping = useFastMember;
                        tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
                        try
                        {
                            if (useFastMember)
                            {
                                using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
                                {
                                    await sqlBulkCopy.WriteToServerAsync(reader, cancellationToken).ConfigureAwait(false);
                                }
                            }
                            else
                            {
                                var dataTable = GetDataTable <T>(context, entities, sqlBulkCopy, tableInfo);
                                await sqlBulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
                            }
                        }
                        catch (InvalidOperationException ex)
                        {
                            if (ex.Message.Contains(ColumnMappingExceptionMessage))
                            {
                                if (!await tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken).ConfigureAwait(false))
                                {
                                    await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);

                                    await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName), cancellationToken).ConfigureAwait(false);
                                }
                            }
                            throw ex;
                        }
                    }
                }
                finally
                {
                    if (transaction == null)
                    {
                        connection.Close();
                    }
                }
            }
            // -- SQLite --
            else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
            {
                var connection = await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);

                var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
                try
                {
                    var command = GetSqliteCommand(context, entities, tableInfo, connection, transaction);

                    var typeAccessor = TypeAccessor.Create(typeof(T), true);
                    int rowsCopied   = 0;
                    foreach (var item in entities)
                    {
                        LoadSqliteValues(tableInfo, typeAccessor, item, command);
                        await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);

                        SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
                    }
                }
                finally
                {
                    if (tableInfo.BulkConfig.SqliteTransaction == null)
                    {
                        transaction.Commit();
                        transaction.Dispose();
                    }
                    if (tableInfo.BulkConfig.SqliteConnection == null)
                    {
                        connection.Close();
                    }
                }
            }
            else
            {
                throw new SqlProviderNotSupportedException(providerName);
            }
        }
示例#24
0
        protected IQueryable <T> QueryOutputTable <T>(DbContext context) where T : class
        {
            var query = context.Set <T>().FromSql <T>(SqlQueryBuilder.SelectFromOutputTable(this));

            return(OrderBy(query, this.PrimaryKeys[0]));
        }
        private static async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken) where T : class
        {
            Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);

            var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB;

            if (dropTempTableIfExists)
            {
                await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB)).ConfigureAwait(false);
            }

            string providerName = context.Database.ProviderName;

            // -- SQL Server --
            if (providerName.EndsWith(DbServer.SqlServer.ToString()))
            {
                await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);

                try
                {
                    await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);

                    tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;

                    var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);

                    tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;

                    List <T> existingEntities;
                    if (typeof(T) == type)
                    {
                        Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlQuery, false);
                        var compiled = EF.CompileQuery(expression); // instead using Compiled queries
                        existingEntities = compiled(context).ToList();
                    }
                    else
                    {
                        Expression <Func <DbContext, IEnumerable> > expression = tableInfo.GetQueryExpression(type, sqlQuery, false);
                        var compiled = EF.CompileQuery(expression); // instead using Compiled queries
                        existingEntities = compiled(context).Cast <T>().ToList();
                    }

                    tableInfo.UpdateReadEntities(type, entities, existingEntities);
                }
                finally
                {
                    if (!tableInfo.BulkConfig.UseTempDB)
                    {
                        await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
                    }
                }
            }
            // -- Sqlite --
            else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
            {
                throw new NotImplementedException();
            }
            else
            {
                throw new SqlProviderNotSupportedException(providerName);
            }
        }
示例#26
0
 protected IQueryable <T> QueryOutputTable <T>(DbContext context) where T : class
 {
     return(context.Set <T>().FromSql(SqlQueryBuilder.SelectFromTable(this.FullTempOutputTableName, this.PrimaryKeys[0])));
 }