private static BulkMergeResult <T> InternalBulkMerge <T>(this DbContext context, IEnumerable <T> entities, BulkMergeOptions <T> options)
        {
            int rowsAffected = 0;
            var outputRows   = new List <BulkMergeOutputRow <T> >();
            var tableMapping = context.GetTableMapping(typeof(T));
            int rowsInserted = 0;
            int rowsUpdated  = 0;
            int rowsDeleted  = 0;

            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[] columnNames               = tableMapping.GetNonValueGeneratedColumns().ToArray();
                    string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray();

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

                    context.Database.CloneTable(destinationTableName, stagingTableName, null, Common.Constants.InternalId_ColumnName);
                    var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true);

                    IEnumerable <string> columnsToInsert = CommonUtil.FormatColumns(columnNames.Where(o => !options.GetIgnoreColumnsOnInsert().Contains(o)));
                    IEnumerable <string> columnstoUpdate = CommonUtil.FormatColumns(columnNames.Where(o => !options.GetIgnoreColumnsOnUpdate().Contains(o))).Select(o => string.Format("t.{0}=s.{0}", o));
                    List <string>        columnsToOutput = new List <string> {
                        "$Action", string.Format("{0}.{1}", "s", Constants.InternalId_ColumnName)
                    };
                    List <PropertyInfo> propertySetters = new List <PropertyInfo>();
                    Type entityType = typeof(T);

                    foreach (var storeGeneratedColumnName in storeGeneratedColumnNames)
                    {
                        columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName));
                        columnsToOutput.Add(string.Format("deleted.[{0}]", storeGeneratedColumnName));
                        propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName));
                    }

                    string mergeSqlText = string.Format("MERGE {0} t USING {1} s ON ({2}) WHEN NOT MATCHED BY TARGET THEN INSERT ({3}) VALUES ({3}) WHEN MATCHED THEN UPDATE SET {4}{5}OUTPUT {6};",
                                                        destinationTableName, stagingTableName, CommonUtil <T> .GetJoinConditionSql(options.MergeOnCondition, storeGeneratedColumnNames, "s", "t"),
                                                        SqlUtil.ConvertToColumnString(columnsToInsert),
                                                        SqlUtil.ConvertToColumnString(columnstoUpdate),
                                                        options.DeleteIfNotMatched ? " WHEN NOT MATCHED BY SOURCE THEN DELETE " : " ",
                                                        SqlUtil.ConvertToColumnString(columnsToOutput)
                                                        );

                    var bulkQueryResult = context.BulkQuery(mergeSqlText, dbConnection, transaction, options);
                    rowsAffected = bulkQueryResult.RowsAffected;

                    foreach (var result in bulkQueryResult.Results)
                    {
                        string id     = string.Empty;
                        object entity = null;
                        string action = (string)result[0];
                        if (action != SqlMergeAction.Delete)
                        {
                            int entityId = (int)result[1];
                            id     = (storeGeneratedColumnNames.Length > 0 ? Convert.ToString(result[2]) : "PrimaryKeyMissing");
                            entity = bulkInsertResult.EntityMap[entityId];
                            if (options.AutoMapOutputIdentity && entity != null)
                            {
                                for (int i = 2; i < 2 + storeGeneratedColumnNames.Length; i++)
                                {
                                    propertySetters[0].SetValue(entity, result[i]);
                                }
                            }
                        }
                        else
                        {
                            id = Convert.ToString(result[2 + storeGeneratedColumnNames.Length]);
                        }
                        outputRows.Add(new BulkMergeOutputRow <T>(action, id));

                        if (action == SqlMergeAction.Insert)
                        {
                            rowsInserted++;
                        }
                        else if (action == SqlMergeAction.Update)
                        {
                            rowsUpdated++;
                        }
                        else if (action == SqlMergeAction.Delete)
                        {
                            rowsDeleted++;
                        }
                    }
                    context.Database.DropTable(stagingTableName);

                    //ClearEntityStateToUnchanged(context, entities);
                    dbTransactionContext.Commit();
                }
                catch (Exception)
                {
                    dbTransactionContext.Rollback();
                    throw;
                }

                return(new BulkMergeResult <T>
                {
                    Output = outputRows,
                    RowsAffected = rowsAffected,
                    RowsDeleted = rowsDeleted,
                    RowsInserted = rowsInserted,
                    RowsUpdated = rowsUpdated,
                });
            }
        }
Example #2
0
        public static BulkMergeResult <T> BulkMerge <T>(this DbContext context, IEnumerable <T> entities, BulkMergeOptions <T> options)
        {
            int rowsAffected = 0;
            var outputRows   = new List <BulkMergeOutputRow <T> >();
            var tableMapping = context.GetTableMapping(typeof(T));
            var dbConnection = context.GetSqlConnection();
            int rowsInserted = 0;
            int rowsUpdated  = 0;
            int rowsDeleted  = 0;

            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[] columnNames               = tableMapping.GetNonValueGeneratedColumns().ToArray();
                    string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray();

                    SqlUtil.CloneTable(destinationTableName, stagingTableName, null, dbConnection, transaction, Common.Constants.Guid_ColumnName);
                    var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true);

                    IEnumerable <string> columnsToInsert = columnNames.Where(o => !options.GetIgnoreColumnsOnInsert().Contains(o));
                    IEnumerable <string> columnstoUpdate = columnNames.Where(o => !options.GetIgnoreColumnsOnUpdate().Contains(o)).Select(o => string.Format("t.{0}=s.{0}", o));
                    List <string>        columnsToOutput = new List <string> {
                        "$Action", string.Format("{0}.{1}", "s", Constants.Guid_ColumnName)
                    };
                    List <PropertyInfo> propertySetters = new List <PropertyInfo>();
                    Type entityType = typeof(T);

                    foreach (var storeGeneratedColumnName in storeGeneratedColumnNames)
                    {
                        //columnsToOutput.Add(string.Format("deleted.[{0}]", storeGeneratedColumnName)); Not Yet Supported
                        columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName));
                        propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName));
                    }

                    string mergeSqlText = string.Format("MERGE {0} t USING {1} s ON ({2}) WHEN NOT MATCHED BY TARGET THEN INSERT ({3}) VALUES ({3}) WHEN MATCHED THEN UPDATE SET {4} OUTPUT {5};",
                                                        destinationTableName, stagingTableName, options.MergeOnCondition.ToSqlPredicate("s", "t"),
                                                        SqlUtil.ConvertToColumnString(columnsToInsert),
                                                        SqlUtil.ConvertToColumnString(columnstoUpdate),
                                                        SqlUtil.ConvertToColumnString(columnsToOutput)
                                                        );

                    var bulkQueryResult = context.BulkQuery(mergeSqlText, dbConnection, transaction);
                    rowsAffected = bulkQueryResult.RowsAffected;

                    //var entitiesEnumerator = entities.GetEnumerator();
                    //entitiesEnumerator.MoveNext();
                    foreach (var result in bulkQueryResult.Results)
                    {
                        string action = (string)result[0];
                        int    id     = (int)result[1];
                        var    entity = bulkInsertResult.EntityMap[id];
                        outputRows.Add(new BulkMergeOutputRow <T>(action, entity));
                        if (options.AutoMapOutputIdentity && entity != null)
                        {
                            for (int i = 2; i < result.Length; i++)
                            {
                                propertySetters[0].SetValue(entity, result[i]);
                            }
                        }
                        if (action == SqlMergeAction.Insert)
                        {
                            rowsInserted++;
                        }
                        else if (action == SqlMergeAction.Update)
                        {
                            rowsUpdated++;
                        }
                        else if (action == SqlMergeAction.Detete)
                        {
                            rowsDeleted++;
                        }
                    }
                    SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction);

                    //ClearEntityStateToUnchanged(context, entities);
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    dbConnection.Close();
                }

                return(new BulkMergeResult <T>
                {
                    Output = outputRows,
                    RowsAffected = rowsAffected,
                    RowsDeleted = rowsDeleted,
                    RowsInserted = rowsInserted,
                    RowsUpdated = rowsUpdated,
                });
            }
        }