private string CreateTempTable(IEnumerable <TEntity> entities, string tempTableName)
{
var entitiesArray = entities as TEntity[] ?? entities.ToArray();
if (!entitiesArray.Any())
{
throw new ArgumentException("collection is empty");
}
var entityType = entitiesArray[0].GetType();
var properties = (IsIdentity ? SqlProperties.Where(p => !p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase)) : SqlProperties).ToList();
var parameters = new Dictionary <string, object>();
var values = new List <string>();
for (var i = 0; i < entitiesArray.Length; i++)
{
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
}
if (HasModifiedAt)
{
ModifiedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
}
values.Add("" + string.Join(", ", properties.Select(p => "'" + entityType.GetProperty(p.PropertyName).GetValue(entitiesArray[i], null) + "' as " + p.ColumnName)));
}
var innerQuery = string.Join(" union ", values.Select(e => $"select {e} "));
var q = $"select * into {tempTableName} " +
$"from ({innerQuery}) t";
return(q);
}
/// <inheritdoc />
public virtual SqlQuery GetUpSert(TEntity entity)
{
var properties = (IsIdentity ? SqlProperties.Where(p => !p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase))
: SqlProperties).ToList();
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
if (HasModifiedAt)
{
ModifiedAtProperty.SetValue(entity, DateTime.Now);
}
var query = new SqlQuery(entity);
var valueFields = string.Join(", ", KeySqlProperties.Select(p => " @" + p.PropertyName));
var selectFields = string.Join(", ", KeySqlProperties.Select(p => p.PropertyName));
var where = string.Join(" AND ", KeySqlProperties.Select(p => "target." + p.ColumnName + " = @" + p.PropertyName));
//override where if we have attributes
if (KeyUpsertSqlProperties.Any())
{
where = string.Join(" AND ", KeyUpsertSqlProperties.Select(p => "target." + p.ColumnName + " = @" + p.PropertyName));
valueFields = string.Join(", ", KeyUpsertSqlProperties.Select(p => " @" + p.PropertyName));
selectFields = string.Join(", ", KeyUpsertSqlProperties.Select(p => p.PropertyName));
}
//exclude update at when has modified is enabled
var update = string.Empty;
if (HasModifiedAt)
{
update = "UPDATE SET " +
string.Join(", ", properties.Where(e => e.PropertyName != UpdatedAtProperty?.Name)
.Select(p => p.ColumnName + " = @" + p.PropertyName));
}
else
{
//exclude both modified and updated at
update = "UPDATE SET " +
string.Join(", ", properties.Where(e => e.PropertyName != UpdatedAtProperty?.Name && e.PropertyName != ModifiedAtProperty?.Name)
.Select(p => p.ColumnName + " = @" + p.PropertyName));
}
var insert = "INSERT "
+ "(" + string.Join(", ", properties.Select(p => p.ColumnName)) + ")" // columNames
+ " VALUES (" + string.Join(", ", properties.Select(p => "@" + p.PropertyName)) + ")"; // values
query.SqlBuilder.Append(
$"MERGE {TableName} AS target USING (SELECT {valueFields}) AS source ({selectFields}) ON ({where}) WHEN MATCHED THEN {update} WHEN NOT MATCHED THEN {insert} OUTPUT Inserted.{KeySqlProperties.First().PropertyName}; ");
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetBulkUpdate(IEnumerable <TEntity> entities)
{
var entitiesArray = entities as TEntity[] ?? entities.ToArray();
if (!entitiesArray.Any())
{
throw new ArgumentException("collection is empty");
}
var entityType = entitiesArray[0].GetType();
var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).ToArray();
var query = new SqlQuery();
var parameters = new Dictionary <string, object>();
for (var i = 0; i < entitiesArray.Length; i++)
{
if (HasModifiedAt)
{
ModifiedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
}
if (i > 0)
{
query.SqlBuilder.Append("; ");
}
query.SqlBuilder.Append("UPDATE " + TableName + " SET " + string.Join(", ", properties.Select(p => p.ColumnName + " = @" + p.PropertyName + i))
+ " WHERE " + string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate).Select(p => p.ColumnName + " = @" + p.PropertyName + i)));
// ReSharper disable PossibleNullReferenceException
foreach (var property in properties)
{
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
}
foreach (var property in KeySqlProperties.Where(p => !p.IgnoreUpdate))
{
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
}
// ReSharper restore PossibleNullReferenceException
}
query.SetParam(parameters);
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity)
{
var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate);
if (HasModifiedAt)
{
ModifiedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder.Append("UPDATE " + TableName + " SET " + string.Join(", ", properties.Select(p => p.ColumnName + " = @" + p.PropertyName)) + " ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(TEntity entity)
{
var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).ToArray();
if (!properties.Any())
{
throw new ArgumentException("Can't update without [Key]");
}
if (HasModifiedAt)
{
ModifiedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder.Append("UPDATE " + TableName + " SET " + string.Join(", ", properties.Select(p => p.ColumnName + " = @" + p.PropertyName))
+ " WHERE " + string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate).Select(p => p.ColumnName + " = @" + p.PropertyName)));
return(query);
}