public static int BulkDelete <T>(this DbContext context, IEnumerable <T> entities, BulkDeleteOptions <T> options)
        {
            int rowsAffected = 0;
            var tableMapping = context.GetTableMapping(typeof(T));

            using (var dbTransactionContext = new DbTransactionContext(context))
            {
                var dbConnection = dbTransactionContext.Connection;
                var transaction  = dbTransactionContext.CurrentTransaction;
                try
                {
                    string   stagingTableName     = CommonUtil.GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
                    string   destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
                    string[] keyColumnNames       = options.DeleteOnCondition != null ? CommonUtil <T> .GetColumns(options.DeleteOnCondition, new[] { "s" })
                        : tableMapping.GetPrimaryKeyColumns().ToArray();

                    if (keyColumnNames.Length == 0 && options.DeleteOnCondition == null)
                    {
                        throw new InvalidDataException("BulkDelete requires that the entity have a primary key or the Options.DeleteOnCondition must be set.");
                    }

                    context.Database.CloneTable(destinationTableName, stagingTableName, keyColumnNames);
                    BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, keyColumnNames, SqlBulkCopyOptions.KeepIdentity, false);
                    string deleteSql = string.Format("DELETE t FROM {0} s JOIN {1} t ON {2}", stagingTableName, destinationTableName,
                                                     CommonUtil <T> .GetJoinConditionSql(options.DeleteOnCondition, keyColumnNames));
                    rowsAffected = context.Database.ExecuteSql(deleteSql, options.CommandTimeout);

                    context.Database.DropTable(stagingTableName);
                    dbTransactionContext.Commit();
                }
                catch (Exception)
                {
                    dbTransactionContext.Rollback();
                    throw;
                }
                return(rowsAffected);
            }
        }
示例#2
0
        public static int BulkDelete <T>(this DbContext context, IEnumerable <T> entities, BulkDeleteOptions <T> options)
        {
            int rowsAffected = 0;
            var tableMapping = context.GetTableMapping(typeof(T));

            Validate(tableMapping);

            var dbConnection = context.GetSqlConnection();

            if (dbConnection.State == ConnectionState.Closed)
            {
                dbConnection.Open();
            }

            using (var transaction = dbConnection.BeginTransaction())
            {
                try
                {
                    string   stagingTableName          = GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
                    string   destinationTableName      = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
                    string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray();
                    string   deleteCondition           = string.Join(" AND ", storeGeneratedColumnNames.Select(o => string.Format("s.{0}=t.{0}", o)));

                    SqlUtil.CloneTable(destinationTableName, stagingTableName, storeGeneratedColumnNames, dbConnection, transaction);
                    BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, storeGeneratedColumnNames, SqlBulkCopyOptions.KeepIdentity);
                    string deleteSql = string.Format("DELETE t FROM {0} s JOIN {1} t ON {2}", stagingTableName, destinationTableName, deleteCondition);
                    rowsAffected = SqlUtil.ExecuteSql(deleteSql, dbConnection, transaction);
                    SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    dbConnection.Close();
                }
                return(rowsAffected);
            }
        }