/// <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 (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder.Append(string.Format("UPDATE {0} SET {1} ", TableName,
string.Join(", ", properties.Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName)))));
AppendWherePredicateQuery(query, predicate, QueryType.Update);
var parameters = new Dictionary <string, object>();
var entityType = entity.GetType();
foreach (var property in properties)
{
parameters.Add(property.PropertyName, entityType.GetProperty(property.PropertyName).GetValue(entity, null));
}
var whereParam = query.Param as Dictionary <string, object>;
if (whereParam != null)
{
parameters.AddRange(whereParam);
}
query.SetParam(parameters);
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(TEntity entity)
{
var query = new SqlQuery(entity);
var cacheKey = _sqlCacheKey + "GetUpdate";
if (StringCache.ContainsKey(cacheKey))
{
query.SqlBuilder.Append(StringCache[cacheKey]);
}
else
{
var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.Name.Equals(p.Name, StringComparison.OrdinalIgnoreCase))).ToArray();
if (!properties.Any())
{
throw new ArgumentException("Can't update without [Key]");
}
query.SqlBuilder.Append($"UPDATE {TableName} SET {string.Join(", ", properties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name}"))} WHERE {string.Join(" AND ", KeySqlProperties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name}"))}");
StringCache.TryAdd(cacheKey, query.GetSql());
}
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, object setPropertyObj)
{
var setProperties = setPropertyObj.GetType().GetProperties();
var properties = SqlProperties
.Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate &&
setProperties.Any(k => k.Name.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)))
.ToArray();
var query = new SqlQuery();
query.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" ");
query.SqlBuilder.Append("SET ");
query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
query.SqlBuilder.Append(" ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
var parameters = (Dictionary <string, object>)query.Param;
foreach (var metadata in properties)
{
var setProp = setProperties.FirstOrDefault(p => p.Name.Equals(metadata.PropertyName, StringComparison.OrdinalIgnoreCase));
if (setProp == null)
{
continue;
}
parameters.Add($"{typeof(TEntity).Name}{metadata.PropertyName}", setProp.GetValue(setPropertyObj));
}
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, Dictionary <string, object> setPropertyDict)
{
var propNameExceptItems = setPropertyDict.Keys.Except(SqlProperties.Select(p => p.PropertyName));
if (propNameExceptItems.Any())
{
string keys = string.Join(",", propNameExceptItems.Select(p => p));
throw new ArgumentException(string.Concat(nameof(setPropertyDict), "content error detail:", $" [{keys}] not equal entity column name"));
}
var properties = SqlProperties
.Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate &&
setPropertyDict.Any(k => k.Key.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)))
.ToArray();
var query = new SqlQuery();
query.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" ");
query.SqlBuilder.Append("SET ");
query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
query.SqlBuilder.Append(" ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
var parameters = (Dictionary <string, object>)query.Param;
foreach (var metadata in properties)
{
var value = setPropertyDict.FirstOrDefault(p => p.Key.Equals(metadata.PropertyName, StringComparison.OrdinalIgnoreCase)).Value;
parameters.Add($"{typeof(TEntity).Name}{metadata.PropertyName}", value);
}
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 (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder.Append(string.Format("UPDATE {0} SET {1} WHERE {2}", TableName,
string.Join(", ", properties.Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName))),
string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate)
.Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName)))
));
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 && !p.RowVersionProp).ToArray();
if (!properties.Any())
{
throw new ArgumentException("Can't update without [Key]");
}
if (HasUpdatedAt)
{
UpdatedAtProperty.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 && !p.RowVersionProp)
.Select(p => p.ColumnName + " = @" + p.PropertyName)));
if (HasRowVersion)
{
query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
}
switch (Config.SqlProvider)
{
case SqlProvider.MSSQL:
//query.SqlBuilder.Append(" SELECT SCOPE_IDENTITY() AS " + IdentitySqlProperty.ColumnName);
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " WHERE " + string.Join(" AND ",
KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp)
.Select(p => p.ColumnName + " = @" + p.PropertyName)));
break;
case SqlProvider.MySQL:
//query.SqlBuilder.Append("; SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) AS " + IdentitySqlProperty.ColumnName);
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " WHERE " + string.Join(" AND ",
KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp)
.Select(p => p.ColumnName + " = @" + p.PropertyName)));
break;
case SqlProvider.PostgreSQL:
throw new ArgumentOutOfRangeException();
default:
throw new ArgumentOutOfRangeException();
}
LogSqlQuery(query);
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 (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
}
if (i > 0)
{
query.SqlBuilder.Append("; ");
}
query.SqlBuilder.Append(string.Format("UPDATE {0} SET {1} WHERE {2}", TableName,
string.Join(", ", properties.Select(p => string.Format("{0} = @{1}{2}", p.ColumnName, p.PropertyName, i))),
string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate)
.Select(p => string.Format("{0} = @{1}{2}", 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, params Expression <Func <TEntity, object> >[] includes)
{
var properties = SqlProperties.Where(p =>
!KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate).ToArray();
if (HasUpdatedAt)
{
var attribute = UpdatedAtProperty.GetCustomAttribute <UpdatedAtAttribute>();
var offset = attribute.TimeKind == DateTimeKind.Local
? new DateTimeOffset(DateTime.Now)
: new DateTimeOffset(DateTime.UtcNow);
if (attribute.OffSet != 0)
{
offset = offset.ToOffset(TimeSpan.FromHours(attribute.OffSet));
}
UpdatedAtProperty.SetValue(entity, offset.DateTime);
}
var query = new SqlQuery();
query.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" ");
if (includes?.Length > 0)
{
var joinsBuilder = AppendJoinToUpdate(entity, query, includes);
query.SqlBuilder.Append("SET ");
query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
query.SqlBuilder.Append(joinsBuilder);
}
else
{
query.SqlBuilder.Append("SET ");
query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
}
query.SqlBuilder
.Append(" ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
var parameters = (Dictionary <string, object>)query.Param;
foreach (var metadata in properties)
{
parameters.Add($"{entity.GetType().Name}{metadata.PropertyName}", entity.GetType().GetProperty(metadata.PropertyName).GetValue(entity, null));
}
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);
if (HasUpdatedAt)
{
UpdatedAtProperty.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);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, object entity)
{
var objectProperties = entity.GetType().GetProperties().ToList();
var properties = SqlProperties.Where(p =>
!KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.Ignore)
.Where(s => objectProperties.Any(x => x.Name == s.PropertyName) || (HasUpdatedAt && UpdatedAtProperty.Name == s.PropertyName))
.ToArray();
var query = new SqlQuery(TableName, entity, QueryType.Update);
query.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" SET ");
query.SqlBuilder.Append(string.Join(", ", properties
.Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName))));
query.SqlBuilder
.Append(" ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
var parameters = new Dictionary <string, object>();
var entityType = entity.GetType();
foreach (var property in properties)
{
if (property.PropertyName == UpdatedAtProperty.Name)
{
parameters.Add(property.PropertyName, $"{DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss")}");
}
else
{
parameters.Add(property.PropertyName, entityType.GetProperty(property.PropertyName).GetValue(entity, null));
}
}
if (query.Param is Dictionary <string, object> whereParam)
{
parameters.AddRange(whereParam);
}
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 (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder.Append("UPDATE " + TableName + " SET " + string.Join(", ", properties.Select(p => p.ColumnName + $" = {SqlParamPrefix}" + p.PropertyName)) + " ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
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).ToArray();
if (HasUpdatedAt)
{
var attribute = UpdatedAtProperty.GetCustomAttribute <UpdatedAtAttribute>();
var offset = attribute.TimeKind == DateTimeKind.Local
? new DateTimeOffset(DateTime.Now)
: new DateTimeOffset(DateTime.UtcNow);
if (attribute.OffSet != 0)
{
offset = offset.ToOffset(TimeSpan.FromHours(attribute.OffSet));
}
UpdatedAtProperty.SetValue(entity, offset.DateTime);
}
var query = new SqlQuery(entity);
query.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" SET ");
query.SqlBuilder.Append(string.Join(", ", properties
.Select(p => $"{p.ColumnName} = @{p.PropertyName}")));
query.SqlBuilder
.Append(" ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
var entityType = entity.GetType();
var parameters = properties.ToDictionary(property => property.PropertyName, property => entityType.GetProperty(property.PropertyName)?.GetValue(entity, null));
if (query.Param is Dictionary <string, object> whereParam)
{
parameters.AddRange(whereParam);
}
query.SetParam(parameters);
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 (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
}
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)));
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));
}
}
query.SetParam(parameters);
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 (HasUpdatedAt)
{
var attribute = UpdatedAtProperty.GetCustomAttribute <UpdatedAtAttribute>();
var offset = attribute.TimeKind == DateTimeKind.Local
? new DateTimeOffset(DateTime.Now)
: new DateTimeOffset(DateTime.UtcNow);
if (attribute.OffSet != 0)
{
offset = offset.ToOffset(TimeSpan.FromHours(attribute.OffSet));
}
UpdatedAtProperty.SetValue(entity, offset.DateTime);
}
var query = new SqlQuery(entity);
query.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" SET ");
query.SqlBuilder.Append(string.Join(", ", properties
.Select(p => $"{p.ColumnName} = @{p.PropertyName}")));
query.SqlBuilder.Append(" WHERE ");
query.SqlBuilder.Append(string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate)
.Select(p => $"{p.ColumnName} = @{p.PropertyName}")));
return(query);
}
public override SqlQuery GetUpdate(TEntity entity)
{
var properties = SqlProperties.Where(
p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase))).ToArray();
if (!properties.Any())
{
throw new ArgumentException("Can't update without [Key]");
}
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder.Append("UPDATE " + TableName + " SET " + string.Join(", ", properties.Where(p => !p.IgnoreUpdate).Select(p => p.ColumnName + " = @" + p.PropertyName))
+ " WHERE " + string.Join(" AND ", KeySqlProperties.Select(p => p.ColumnName + " = @" + p.PropertyName)));
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity)
{
var query = new SqlQuery(entity);
var cacheKey = _sqlCacheKey + "GetUpdateByExpression";
if (StringCache.ContainsKey(cacheKey))
{
query.SqlBuilder.Append(StringCache[cacheKey]);
}
else
{
var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.Name.Equals(p.Name, StringComparison.OrdinalIgnoreCase)));
query.SqlBuilder.Append($"UPDATE {TableName} SET {string.Join(", ", properties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name}"))} ");
StringCache.TryAdd(cacheKey, query.GetSql());
}
AppendWherePredicateQuery(query, predicate);
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity, object userId)
{
var query = new SqlQuery(entity);
var cacheKey = _sqlCacheKey + "GetUpdateByExpression";
if (SqlStringCache.ContainsKey(cacheKey))
{
query.SqlBuilder.Append(SqlStringCache[cacheKey]);
}
else
{
var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.Name.Equals(p.Name, StringComparison.OrdinalIgnoreCase)));
query.SqlBuilder.Append($"UPDATE {TableName} SET {string.Join(", ", properties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name}"))} ");
SqlStringCache.TryAdd(cacheKey, query.GetSql());
}
AppendWherePredicateQuery(query, predicate);
//处理公共字段
var commonValues = Config.UpdateCommonProperty(userId);
foreach (var common in commonValues)
{
if (query.Param.Keys.Contains(common.Key))
{
query.Param[common.Key] = common.Value;
}
else
{
query.Param.Add(common.Key, common.Value);
}
}
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?>();
//In Oracle we use MERGE INTO to excute multipe update with argument.
List <string> singleSelectsForOracle = new List <string>();
for (var i = 0; i < entitiesArray.Length; i++)
{
var entity = entitiesArray[i];
if (HasUpdatedAt && UpdatedAtProperty.GetCustomAttribute <UpdatedAtAttribute>() is { } attribute)
{
var offset = attribute.TimeKind == DateTimeKind.Local
? new DateTimeOffset(DateTime.Now)
: new DateTimeOffset(DateTime.UtcNow);
if (attribute.OffSet != 0)
{
offset = offset.ToOffset(TimeSpan.FromHours(attribute.OffSet));
}
UpdatedAtProperty.SetValue(entity, offset.DateTime);
}
if (Provider != SqlProvider.Oracle)
{
if (i > 0)
{
query.SqlBuilder.Append("; ");
}
query.SqlBuilder.Append(
$"UPDATE {TableName} SET {string.Join(", ", properties.Select(p => $"{p.ColumnName} = {ParameterSymbol}{p.PropertyName}{i}"))} WHERE {string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate).Select(p => $"{p.ColumnName} = {ParameterSymbol}{p.PropertyName}{i}"))}");
}
else
{
var singleSelect =
$"SELECT {string.Join(", ", properties.Select(p => $"{ParameterSymbol}{p.PropertyName}{i} AS {p.ColumnName}"))}, {string.Join(" , ", KeySqlProperties.Where(p => !p.IgnoreUpdate).Select(p => $"{ParameterSymbol}{p.PropertyName}{i} AS {p.ColumnName}"))} FROM DUAL";
singleSelectsForOracle.Add(singleSelect);
}
// ReSharper disable PossibleNullReferenceException
foreach (var property in properties)
{
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName)?.GetValue(entity, null));
}
foreach (var property in KeySqlProperties.Where(p => !p.IgnoreUpdate))
{
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName)?.GetValue(entity, null));
}
// ReSharper restore PossibleNullReferenceException
}
query.SetParam(parameters);
if (Provider == SqlProvider.Oracle)
{
var unionTName = $"{TableName}_BULKUPDATE";
var unionSelect = string.Join(" UNION ALL ", singleSelectsForOracle);
var unionOn = $"({string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate).Select(p => $"{unionTName}.{p.ColumnName} = {TableName}.{p.ColumnName}"))})";
var unionSet = $"{string.Join(",", properties.Select(p => $"{p.ColumnName} = {unionTName}.{p.ColumnName} "))}";
query.SqlBuilder.Append($"MERGE INTO {TableName} {TableName} USING ({unionSelect}) {unionTName} ON {unionOn} WHEN MATCHED THEN UPDATE SET {unionSet}");
}
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(TEntity entity, params Expression <Func <TEntity, object> >[] includes)
{
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 (HasUpdatedAt)
{
var attribute = UpdatedAtProperty.GetCustomAttribute <UpdatedAtAttribute>();
var offset = attribute.TimeKind == DateTimeKind.Local
? new DateTimeOffset(DateTime.Now)
: new DateTimeOffset(DateTime.UtcNow);
if (attribute.OffSet != 0)
{
offset = offset.ToOffset(TimeSpan.FromHours(attribute.OffSet));
}
UpdatedAtProperty.SetValue(entity, offset.DateTime);
}
var query = new SqlQuery();
query.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" ");
if (includes?.Length > 0)
{
var joinsBuilder = AppendJoinToUpdate(entity, query, includes);
query.SqlBuilder.Append("SET ");
query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
query.SqlBuilder.Append(joinsBuilder);
}
else
{
query.SqlBuilder.Append("SET ");
query.SqlBuilder.Append(GetFieldsUpdate(TableName, properties, UseQuotationMarks == true));
}
query.SqlBuilder.Append(" WHERE ");
query.SqlBuilder.Append(string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate)
.Select(p => $"{TableName}.{p.ColumnName} = @{entity.GetType().Name}{p.PropertyName}")));
if (query.Param == null || !(query.Param is Dictionary <string, object> parameters))
{
parameters = new Dictionary <string, object>();
}
foreach (var metadata in properties.Concat(KeySqlProperties))
{
parameters.Add($"{entity.GetType().Name}{metadata.PropertyName}", entity.GetType().GetProperty(metadata.PropertyName).GetValue(entity, null));
}
query.SetParam(parameters);
return(query);
}
private void InitProperties()
{
var entityType = typeof(TEntity);
var entityTypeInfo = entityType.GetTypeInfo();
var tableAttribute = entityTypeInfo.GetCustomAttribute <TableAttribute>();
TableName = tableAttribute != null ? tableAttribute.Name : entityTypeInfo.Name;
TableSchema = tableAttribute != null ? tableAttribute.Schema : string.Empty;
AllProperties = entityType.FindClassProperties().Where(q => q.CanWrite).ToArray();
var props = AllProperties.Where(ExpressionHelper.GetPrimitivePropertiesPredicate()).ToArray();
var joinProperties = AllProperties.Where(p => p.GetCustomAttributes <JoinAttributeBase>().Any()).ToArray();
SqlJoinProperties = GetJoinPropertyMetadata(joinProperties);
// Filter the non stored properties
SqlProperties = props.Where(p => !p.GetCustomAttributes <NotMappedAttribute>().Any()).Select(p => new SqlPropertyMetadata(p)).ToArray();
KeyUpsertSqlProperties =
props.Where(e => e.GetCustomAttributes <UpsertKeyAttribute>().Any())
.Select(p => new SqlPropertyMetadata(p))
.ToArray();
// Filter key properties
KeySqlProperties = props.Where(p => p.GetCustomAttributes <KeyAttribute>().Any()).Select(p => new SqlPropertyMetadata(p)).ToArray();
if (!KeySqlProperties.Any())
{
var prop = props.SingleOrDefault(p => p.Name.Equals("Id", StringComparison.OrdinalIgnoreCase));
//skip adding key if prop is null, which means the model doesn't have Id property....
if (prop != null)
{
KeySqlProperties = new SqlPropertyMetadata[1];
KeySqlProperties[0] = new SqlPropertyMetadata(prop);
}
}
// Use identity as key pattern
var identityProperty = props.FirstOrDefault(p => p.GetCustomAttributes <IdentityAttribute>().Any());
IdentitySqlProperty = identityProperty != null ? new SqlPropertyMetadata(identityProperty) : null;
var dateChangedProperty = props.FirstOrDefault(p => p.GetCustomAttributes <UpdatedAtAttribute>().Count() == 1);
if (dateChangedProperty != null && (dateChangedProperty.PropertyType == typeof(DateTime) || dateChangedProperty.PropertyType == typeof(DateTime?)))
{
UpdatedAtProperty = props.FirstOrDefault(p => p.GetCustomAttributes <UpdatedAtAttribute>().Any());
UpdatedAtPropertyMetadata = new SqlPropertyMetadata(UpdatedAtProperty);
}
var modifiedDateProperty = props.FirstOrDefault(p => p.GetCustomAttributes <ModifiedAtAttribute>().Count() == 1);
if (modifiedDateProperty != null &&
(modifiedDateProperty.PropertyType == typeof(DateTime) ||
modifiedDateProperty.PropertyType == typeof(DateTime?)))
{
ModifiedAtProperty = props.FirstOrDefault(p => p.GetCustomAttributes <ModifiedAtAttribute>().Any());
ModifiedAtPropertyMetadata = new SqlPropertyMetadata(ModifiedAtProperty);
}
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(Expression <Func <TEntity, bool> > predicate, TEntity entity, Expression <Func <TEntity, object> > propertiesToUpdate)
{
var properties = SqlProperties.Where(p =>
!KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) && !p.IgnoreUpdate && !p.RowVersionProp).ToArray();
if (propertiesToUpdate != null)
{
string[] updateProperties = ExpressionHelper.GetMemberName(propertiesToUpdate);
if (HasMandatoryProp)
{
properties = properties.Where(x =>
updateProperties.Contains(x.PropertyName) ||
MandatoryUpdateProperty.Select(y => y.Name).Contains(x.PropertyName))
.ToArray();
}
else
{
properties = properties.Where(x =>
updateProperties.Contains(x.PropertyName))
.ToArray();
}
}
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" SET ");
query.SqlBuilder.Append(string.Join(", ", properties
.Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName))));
query.SqlBuilder
.Append(" ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
if (HasRowVersion)
{
query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
}
switch (Config.SqlProvider)
{
case SqlProvider.MSSQL:
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
if (HasRowVersion)
{
query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
}
break;
case SqlProvider.MySQL:
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
if (HasRowVersion)
{
query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
}
break;
case SqlProvider.PostgreSQL:
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " ");
AppendWherePredicateQuery(query, predicate, QueryType.Update);
if (HasRowVersion)
{
query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
}
break;
default:
throw new ArgumentOutOfRangeException();
}
var parameters = new Dictionary <string, object>();
var entityType = entity.GetType();
foreach (var property in properties)
{
parameters.Add(property.PropertyName, entityType.GetProperty(property.PropertyName).GetValue(entity, null));
}
if (query.Param is Dictionary <string, object> whereParam)
{
parameters.AddRange(whereParam);
}
query.SetParam(parameters);
LogSqlQuery(query);
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetUpdate(TEntity entity, Expression <Func <TEntity, object> > propertiesToUpdate)
{
var properties = SqlProperties.Where(p =>
!KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)) &&
!p.IgnoreUpdate && !p.RowVersionProp).ToArray();
if (!properties.Any())
{
throw new ArgumentException("Can't update without [Key]");
}
if (propertiesToUpdate != null)
{
string[] updateProperties = ExpressionHelper.GetMemberName(propertiesToUpdate);
if (HasMandatoryProp)
{
properties = properties.Where(x =>
updateProperties.Contains(x.PropertyName) ||
MandatoryUpdateProperty.Select(y => y.Name).Contains(x.PropertyName))
.ToArray();
}
else
{
properties = properties.Where(x =>
updateProperties.Contains(x.PropertyName))
.ToArray();
}
}
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" SET ");
query.SqlBuilder.Append(string.Join(", ", properties
.Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName))));
query.SqlBuilder.Append(" WHERE ");
query.SqlBuilder.Append(string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp)
.Select(p => string.Format("{0} = @{1}", p.ColumnName, p.PropertyName))));
if (HasRowVersion)
{
query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
}
switch (Config.SqlProvider)
{
case SqlProvider.MSSQL:
//query.SqlBuilder.Append(" SELECT SCOPE_IDENTITY() AS " + IdentitySqlProperty.ColumnName);
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " WHERE " + string.Join(" AND ",
KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp)
.Select(p => p.ColumnName + " = @" + p.PropertyName)));
break;
case SqlProvider.MySQL:
//query.SqlBuilder.Append("; SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) AS " + IdentitySqlProperty.ColumnName);
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " WHERE " + string.Join(" AND ",
KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp)
.Select(p => p.ColumnName + " = @" + p.PropertyName)));
break;
case SqlProvider.PostgreSQL:
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " WHERE " + string.Join(" AND ",
KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp)
.Select(p => p.ColumnName + " = @" + p.PropertyName)));
break;
default:
throw new ArgumentOutOfRangeException();
}
LogSqlQuery(query);
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetBulkUpdate(IEnumerable <TEntity> entities, Expression <Func <TEntity, object> > propertiesToUpdate)
{
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 && !p.RowVersionProp).ToArray();
if (propertiesToUpdate != null)
{
string[] updateProperties = ExpressionHelper.GetMemberName(propertiesToUpdate);
if (HasMandatoryProp)
{
properties = properties.Where(x =>
updateProperties.Contains(x.PropertyName) ||
MandatoryUpdateProperty.Select(y => y.Name).Contains(x.PropertyName))
.ToArray();
}
else
{
properties = properties.Where(x =>
updateProperties.Contains(x.PropertyName))
.ToArray();
}
}
var query = new SqlQuery();
var parameters = new Dictionary <string, object>();
for (var i = 0; i < entitiesArray.Length; i++)
{
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
}
if (i > 0)
{
query.SqlBuilder.Append("; ");
}
query.SqlBuilder.Append(string.Format("UPDATE {0} SET {1} WHERE {2}", TableName,
string.Join(", ", properties.Select(p => string.Format("{0} = @{1}{2}", p.ColumnName, p.PropertyName, i))),
string.Join(" AND ", KeySqlProperties.Where(p => !p.IgnoreUpdate)
.Select(p => string.Format("{0} = @{1}{2}", p.ColumnName, p.PropertyName, i)))
));
if (HasRowVersion)
{
query.SqlBuilder.Append(" AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName);
}
// 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));
foreach (var property in KeySqlProperties.Where(p => !p.IgnoreUpdate && !p.RowVersionProp))
{
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
}
foreach (var property in KeySqlProperties.Where(p => p.RowVersionProp))
{
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
}
// ReSharper restore PossibleNullReferenceException
}
query.SetParam(parameters);
LogSqlQuery(query);
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++)
{
var entity = entitiesArray[i];
if (HasUpdatedAt)
{
var attribute = UpdatedAtProperty.GetCustomAttribute <UpdatedAtAttribute>();
var offset = attribute.TimeKind == DateTimeKind.Local
? new DateTimeOffset(DateTime.Now)
: new DateTimeOffset(DateTime.UtcNow);
if (attribute.OffSet != 0)
{
offset = offset.ToOffset(TimeSpan.FromHours(attribute.OffSet));
}
UpdatedAtProperty.SetValue(entity, offset.DateTime);
}
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(entity, null));
}
foreach (var property in KeySqlProperties.Where(p => !p.IgnoreUpdate))
{
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entity, null));
}
// ReSharper restore PossibleNullReferenceException
}
query.SetParam(parameters);
return(query);
}