protected override void ExecuteNoRet()
{
StringBuilder sql = new StringBuilder();
int notNullFields = 0;
var valueKeyList = new List <string>();
var columnList = new List <string>();
var valueList = new List <object>();
foreach (var p in ColumnProperties)
{
columnList.Add(p.Name);
var val = p.GetValue(Entity, null);
if (val != null)
{
valueKeyList.Add("{" + (notNullFields++) + "}");
valueList.Add(val);
}
else
{
valueKeyList.Add("null");
}
}
var columns = columnList.ToArray();
sql.Append("merge into ");
sql.Append(TableName);
sql.Append(" as T ");
sql.Append("using (values (");
sql.Append(string.Join(",", valueKeyList.ToArray()));
sql.Append(")) as S (");
sql.Append(string.Join(",", columns));
sql.Append(") ");
sql.Append("on (");
var mergeCond = string.Join(" and ", KeyNames.Select(kn => "T." + kn + "=S." + kn));
sql.Append(mergeCond);
sql.Append(") ");
sql.Append("when matched then update set ");
sql.Append(string.Join(",", columns.Select(c => "T." + c + "=S." + c).ToArray()));
sql.Append(" when not matched then insert (");
sql.Append(string.Join(",", columns));
sql.Append(") values (S.");
sql.Append(string.Join(",S.", columns));
sql.Append(");");
Context.Database.ExecuteSqlCommand(sql.ToString(), valueList.ToArray());
}
protected override void ExecuteNoRet()
{
var currentCommandTimeout = Context.Database.CommandTimeout;
try
{
// Up the timeout as this may take a while for scheduled batch jobs.
Context.Database.CommandTimeout = 300;
StringBuilder sql = new StringBuilder();
int notNullFields = 0;
var valueKeyList = new List <string>();
var columnList = new List <string>();
var columnProperties = ColumnProperties.ToArray();
foreach (var p in columnProperties)
{
if (!p.IsDefined(typeof(DoNotIncludeInUpsertAttribute), false))
{
columnList.Add(p.Name);
valueKeyList.Add("{" + (notNullFields++) + "}");
}
}
var columns = columnList.ToArray();
sql.Append("merge into ");
sql.Append(TableName);
sql.Append(" as T ");
sql.Append("using (values (");
sql.Append(string.Join(",", valueKeyList.ToArray()));
sql.Append(")) as S (");
sql.Append(string.Join(",", columns.Select(x => $"[{x}]")));
sql.Append(") ");
sql.Append("on (");
var mergeCond = string.Join(" and ", KeyNames.Select(kn => "T.[" + kn + "]=S.[" + kn + "]"));
sql.Append(mergeCond);
sql.Append(") ");
sql.Append("when matched then update set ");
// If Identity Insert is on, we will have been supplied the PK in the list of columns. Wish to exclude this from the update statement as updating a PK is not allowed.
var colsToUpdate = _identityInsertOn ? columns.Where(pr => pr != EntityPrimaryKeyName) : columns;
sql.Append(string.Join(",", colsToUpdate.Select(c => "T.[" + c + "]=S.[" + c + "]").ToArray()));
sql.Append(" when not matched then insert (");
sql.Append(string.Join(",", columns.Select(x => $"[{x}]")));
sql.Append(") values (S.");
sql.Append(string.Join(",S.", columns.Select(x => $"[{x}]")));
sql.Append(");");
var command = sql.ToString();
foreach (var entity in EntityList)
{
var valueList = new List <object>();
foreach (var p in columnProperties)
{
if (!p.IsDefined(typeof(DoNotIncludeInUpsertAttribute), false))
{
var val = p.GetValue(entity, null);
valueList.Add(val ?? DBNull.Value);
}
}
Context.Database.ExecuteSqlCommand(command, valueList.ToArray());
}
}
finally
{
// Reset the timeout to the original value.
Context.Database.CommandTimeout = currentCommandTimeout;
}
}