/// <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(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 GetAllSelectedFields(Expression <Func <TEntity, bool> > predicate, params Expression <Func <TEntity, object> >[] fields)
{
List <string> fieldNames = new List <string>();
foreach (var field in fields)
{
fieldNames.Add(ExpressionHelper.GetPropertyName(field));
}
if (fieldNames.Count() == 0)
{
return(GetSelectAll(predicate));
}
var query = new SqlQuery(TableName, QueryType.Select);
query.SqlBuilder.Append("SELECT ")
.Append(GetFieldsSelect(TableName, SqlProperties.Where(x => fieldNames.Any(s => s == x.PropertyName)).ToArray()))
.Append(" FROM ")
.Append(TableName)
.Append(" ");
AppendWherePredicateQuery(query, predicate, QueryType.Select);
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetCount(Expression <Func <TEntity, bool> >?predicate, Expression <Func <TEntity, object> > distinctField,
params Expression <Func <TEntity, object> >[] includes)
{
var propertyName = ExpressionHelper.GetPropertyName(distinctField);
var property = SqlProperties.First(x => x.PropertyName == propertyName);
var sqlQuery = InitBuilderCountWithDistinct(property);
if (includes.Length > 0)
{
var joinsBuilder = AppendJoinToSelect(sqlQuery, true, includes);
sqlQuery.SqlBuilder
.Append(" FROM ")
.Append(TableName)
.Append(" ");
sqlQuery.SqlBuilder.Append(joinsBuilder);
}
else
{
sqlQuery.SqlBuilder
.Append(" FROM ")
.Append(TableName)
.Append(" ");
}
AppendWherePredicateQuery(sqlQuery, predicate, QueryType.Select);
return(sqlQuery);
}
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 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);
}
/// <summary>
/// Fill query properties
/// </summary>
/// <param name="expr">The expression.</param>
/// <param name="linkingType">Type of the linking.</param>
/// <param name="queryProperties">The query properties.</param>
private void FillQueryProperties(Expression expr, ExpressionType linkingType, ref List <QueryParameter> queryProperties)
{
if (expr is MethodCallExpression body)
{
var innerBody = body;
var methodName = innerBody.Method.Name;
switch (methodName)
{
case "Contains":
{
var propertyName = ExpressionHelper.GetPropertyNamePath(innerBody, out var isNested);
if (!SqlProperties.Select(x => x.PropertyName).Contains(propertyName) &&
!SqlJoinProperties.Select(x => x.PropertyName).Contains(propertyName))
{
throw new NotSupportedException("predicate can't parse");
}
var propertyValue = ExpressionHelper.GetValuesFromCollection(innerBody);
var opr = ExpressionHelper.GetMethodCallSqlOperator(methodName);
var link = ExpressionHelper.GetSqlOperator(linkingType);
queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr, isNested));
break;
}
default:
throw new NotSupportedException($"'{methodName}' method is not supported");
}
}
else if (expr is BinaryExpression)
{
var innerbody = (BinaryExpression)expr;
if (innerbody.NodeType != ExpressionType.AndAlso && innerbody.NodeType != ExpressionType.OrElse)
{
var propertyName = ExpressionHelper.GetPropertyNamePath(innerbody, out var isNested);
if (!SqlProperties.Select(x => x.PropertyName).Contains(propertyName) &&
!SqlJoinProperties.Select(x => x.PropertyName).Contains(propertyName))
{
throw new NotSupportedException("predicate can't parse");
}
var propertyValue = ExpressionHelper.GetValue(innerbody.Right);
var opr = ExpressionHelper.GetSqlOperator(innerbody.NodeType);
var link = ExpressionHelper.GetSqlOperator(linkingType);
queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr, isNested));
}
else
{
FillQueryProperties(innerbody.Left, innerbody.NodeType, ref queryProperties);
FillQueryProperties(innerbody.Right, innerbody.NodeType, ref queryProperties);
}
}
else
{
FillQueryProperties(ExpressionHelper.GetBinaryExpression(expr), linkingType, ref queryProperties);
}
}
/// <inheritdoc />
public virtual SqlQuery GetInsert(TEntity entity)
{
var properties =
(IsIdentity
? SqlProperties.Where(p => !p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase))
: SqlProperties).ToList();
var keyProperty = KeySqlProperties.Where(s => s.PropertyInfo.PropertyType == typeof(Guid) || s.PropertyInfo.PropertyType == typeof(Guid?)).FirstOrDefault();
#region If There Is No Identity Property
if (!IsIdentity && keyProperty != null)
{
var oldKey = keyProperty.PropertyInfo.GetValue(entity, null);
if (oldKey == null || (Guid?)oldKey == default(Guid?))
{
keyProperty.PropertyInfo.SetValue(entity, Guid.NewGuid());
}
}
#endregion
if (HasCreatedAt)
{
CreatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(TableName, entity, QueryType.Insert);
query.SqlBuilder.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2})", TableName, string.Join(", ", properties.Select(p => p.ColumnName)),
string.Join(", ", properties.Select(p => "@" + p.PropertyName))); // values
if (IsIdentity)
{
switch (Config.SqlProvider)
{
case SqlProvider.MSSQL:
query.SqlBuilder.Append(" SELECT SCOPE_IDENTITY() AS " + IdentitySqlProperty.ColumnName);
break;
case SqlProvider.MySQL:
query.SqlBuilder.Append("; SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) AS " + IdentitySqlProperty.ColumnName);
break;
case SqlProvider.PostgreSQL:
query.SqlBuilder.Append(" RETURNING " + IdentitySqlProperty.ColumnName);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetSelectBetween(object from, object to, Expression <Func <TEntity, object> > btwField, int pageNo, int pageSize, bool includeLogicalDeleted, Expression <Func <TEntity, bool> > predicate)
{
var fieldName = ExpressionHelper.GetPropertyName(btwField);
var columnName = SqlProperties.First(x => x.PropertyName == fieldName).ColumnName;
var query = GetSelectAll(predicate, pageNo, pageSize, includeLogicalDeleted, from, to, columnName, true);
LogSqlQuery(query);
return(query);
}
/// <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 GetSelectBetween(object from, object to, Expression <Func <TEntity, object> > btwField, Expression <Func <TEntity, bool> > expression = null)
{
var fieldName = ExpressionHelper.GetPropertyName(btwField);
var columnName = SqlProperties.First(x => x.PropertyName == fieldName).ColumnName;
var query = GetSelectAll(expression);
query.SqlBuilder.Append((expression == null && !LogicalDelete ? "WHERE" : "AND") + " " + TableName + "." + columnName + " BETWEEN '" + from + "' AND '" + to + "'");
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);
}
private void InitLogicalDeleted()
{
var statusProperty =
SqlProperties.FirstOrDefault(x => x.PropertyInfo.GetCustomAttribute <StatusAttribute>() != null);
foreach (var property in AllProperties)
{
var joinAttr = property.GetCustomAttribute <JoinAttributeBase>();
if (joinAttr?.TableName == null)
{
continue;
}
//var deleted = joinProperty.JoinPropertyInfo.PropertyType.GetCustomAttribute<DeletedAttribute>();
var deleteAttr = property.PropertyType.GetProperties().FirstOrDefault(x => x.GetCustomAttribute <DeletedAttribute>() != null);
if (deleteAttr == null)
{
continue;
}
if (JoinsLogicalDelete == null)
{
JoinsLogicalDelete = new Dictionary <string, PropertyInfo>();
}
JoinsLogicalDelete.Add(joinAttr.TableName, deleteAttr);
}
if (statusProperty == null)
{
return;
}
StatusPropertyName = statusProperty.ColumnName;
if (statusProperty.PropertyInfo.PropertyType == typeof(bool))
{
LogicalDelete = true;
LogicalDeleteValue = 1; // true
}
else if (statusProperty.PropertyInfo.PropertyType.IsEnum)
{
var deleteOption = statusProperty.PropertyInfo.PropertyType.GetFields().FirstOrDefault(f => f.GetCustomAttribute <DeletedAttribute>() != null);
if (deleteOption == null)
{
return;
}
var enumValue = Enum.Parse(statusProperty.PropertyInfo.PropertyType, deleteOption.Name);
LogicalDeleteValue = Convert.ChangeType(enumValue, Enum.GetUnderlyingType(statusProperty.PropertyInfo.PropertyType));
LogicalDelete = true;
}
}
/// <inheritdoc />
public virtual SqlQuery GetBulkInsert(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 =
(IsIdentity
? SqlProperties.Where(p => !p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase))
: SqlProperties).ToList();
var query = new SqlQuery();
var values = new List <string>();
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);
}
foreach (var property in properties)
{
// ReSharper disable once PossibleNullReferenceException
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entity, null));
}
values.Add(string.Format("({0})", string.Join(", ", properties.Select(p => "@" + p.PropertyName + i))));
}
query.SqlBuilder.AppendFormat("INSERT INTO {0} ({1}) VALUES {2}", TableName, string.Join(", ", properties.Select(p => p.ColumnName)),
string.Join(",", values)); // values
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);
}
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);
}
private void InitLogicalDeleted()
{
var deleteProperty = SqlProperties.FirstOrDefault(p => p.PropertyInfo.GetCustomAttributes <DeletedAttribute>().Any());
if (deleteProperty == null)
{
return;
}
LogicalDeleteProperty = deleteProperty.PropertyInfo;
LogicalDeletePropertyMetadata = deleteProperty;
}
/// <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 GetInsert(TEntity entity)
{
var properties =
(IsIdentity
? SqlProperties.Where(p =>
(!p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase) ||
(MandatoryInsertProperty != null && MandatoryInsertProperty.Any(x => x.Name == p.PropertyName))) &&
!p.IgnoreCreate && !p.RowVersionProp)
: SqlProperties).ToList();
if (HasCreatedAt)
{
CreatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2})", TableName, string.Join(", ", properties.Select(p => p.ColumnName)),
string.Join(", ", properties.Select(p => "@" + p.PropertyName))); // values
if (IsIdentity)
{
switch (Config.SqlProvider)
{
case SqlProvider.MSSQL:
//query.SqlBuilder.Append(" SELECT SCOPE_IDENTITY() AS " + IdentitySqlProperty.ColumnName);
query.SqlBuilder.Append(" SELECT * FROM " + TableName + " WHERE " + IdentitySqlProperty.ColumnName + " = SCOPE_IDENTITY()");
break;
case SqlProvider.MySQL:
//query.SqlBuilder.Append("; SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) AS " + IdentitySqlProperty.ColumnName);
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " WHERE " + IdentitySqlProperty.ColumnName + " = CONVERT(LAST_INSERT_ID(), SIGNED INTEGER)");
break;
case SqlProvider.PostgreSQL:
query.SqlBuilder.Append("; SELECT * FROM " + TableName + " WHERE " + IdentitySqlProperty.ColumnName +
" = CURRVAL('" + TableName + "_" + IdentitySqlProperty.ColumnName + "_seq')");
//query.SqlBuilder.Append(" RETURNING " + IdentitySqlProperty.ColumnName);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
LogSqlQuery(query);
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetCount(Expression <Func <TEntity, bool> > predicate, Expression <Func <TEntity, object> > distinctField)
{
var propertyName = ExpressionHelper.GetPropertyName(distinctField);
var property = SqlProperties.First(x => x.PropertyName == propertyName);
var sqlQuery = InitBuilderCountWithDistinct(property);
sqlQuery.SqlBuilder.Append(" FROM " + TableName + " ");
AppendWherePredicateQuery(sqlQuery, predicate, QueryType.Select);
return(sqlQuery);
}
/// <inheritdoc />
public virtual SqlQuery GetInsert(TEntity entity)
{
var properties =
(IsIdentity
? SqlProperties.Where(p => !p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase))
: SqlProperties).ToList();
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.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2})", TableName, string.Join(", ", properties.Select(p => p.ColumnName)),
string.Join(", ", properties.Select(p => "@" + p.PropertyName))); // values
if (IsIdentity)
{
switch (Config.SqlProvider)
{
case SqlProvider.MSSQL:
query.SqlBuilder.Append(" SELECT SCOPE_IDENTITY() AS " + IdentitySqlProperty.ColumnName);
break;
case SqlProvider.MySQL:
query.SqlBuilder.Append("; SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) AS " + IdentitySqlProperty.ColumnName);
break;
case SqlProvider.SQLite:
query.SqlBuilder.Append("; SELECT LAST_INSERT_ROWID() AS " + IdentitySqlProperty.ColumnName);
break;
case SqlProvider.PostgreSQL:
query.SqlBuilder.Append(" RETURNING " + IdentitySqlProperty.ColumnName);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetBulkInsert(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 = (IsIdentity ? SqlProperties.Where(p => !p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase)) : SqlProperties).ToList();
var query = new SqlQuery();
var values = new List <string>();
var parameters = new Dictionary <string, object>();
for (var i = 0; i < entitiesArray.Length; i++)
{
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
}
foreach (var property in properties)
{
// ReSharper disable once PossibleNullReferenceException
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
}
values.Add((Config.SqlProvider == SqlProvider.Oracle ? " SELECT " : "(") + string.Join(", ", properties.Select(p => SqlParamPrefix + p.PropertyName + i)) + (Config.SqlProvider == SqlProvider.Oracle ? " FROM dual" : ")"));
}
if (Config.SqlProvider == SqlProvider.Oracle)
{
query.SqlBuilder.Append(
"INSERT INTO " + TableName
+ " (" + string.Join(", ", properties.Select(p => p.ColumnName)) + ")" // columNames
+ " " + string.Join(" union all ", values)); // values
}
else
{
query.SqlBuilder.Append(
"INSERT INTO " + TableName
+ " (" + string.Join(", ", properties.Select(p => p.ColumnName)) + ")" // columNames
+ " VALUES " + string.Join(",", values)); // values
}
query.SetParam(parameters);
return(query);
}
/// <inheritdoc />
public virtual SqlQuery GetBulkInsert(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 =
(IsIdentity
? SqlProperties.Where(p =>
(!p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase) ||
(MandatoryInsertProperty != null && MandatoryInsertProperty.Any(x => x.Name == p.PropertyName))) &&
!p.IgnoreCreate && !p.RowVersionProp)
: SqlProperties).ToList();
var query = new SqlQuery();
var values = new List <string>();
var parameters = new Dictionary <string, object>();
for (var i = 0; i < entitiesArray.Length; i++)
{
if (HasCreatedAt)
{
CreatedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
}
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entitiesArray[i], DateTime.UtcNow);
}
foreach (var property in properties)
{
// ReSharper disable once PossibleNullReferenceException
parameters.Add(property.PropertyName + i, entityType.GetProperty(property.PropertyName).GetValue(entitiesArray[i], null));
}
values.Add(string.Format("({0})", string.Join(", ", properties.Select(p => "@" + p.PropertyName + i))));
}
query.SqlBuilder.AppendFormat("INSERT INTO {0} ({1}) VALUES {2}", TableName, string.Join(", ", properties.Select(p => p.ColumnName)), string.Join(",", values)); // values
query.SetParam(parameters);
LogSqlQuery(query);
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 GetBulkInsert(IEnumerable <TEntity> entities)
{
var entitiesArray = entities as TEntity[] ?? entities.ToArray();
if (!entitiesArray.Any())
{
throw new ArgumentException("collection is empty");
}
var properties = (IsIdentity ? SqlProperties.Where(p => !p.Name.Equals(IdentitySqlProperty.Name, StringComparison.OrdinalIgnoreCase)) : SqlProperties).ToList();
var query = new SqlQuery();
var cacheKey = _sqlCacheKey + "GetBulkInsert";
if (StringCache.ContainsKey(cacheKey))
{
query.SqlBuilder.Append(StringCache[cacheKey]);
}
else
{
query.SqlBuilder.Append($"INSERT INTO {TableName} ({string.Join(", ", properties.Select(p => GetPropertyName(p.Name)))}) VALUES ");
StringCache.TryAdd(cacheKey, query.GetSql());
}
var entityType = entitiesArray[0].GetType();
var values = new List <string>();
var parameters = new Dictionary <string, object>();
for (var i = 0; i < entitiesArray.Length; i++)
{
foreach (var property in properties)
{
// ReSharper disable once PossibleNullReferenceException
parameters.Add(property.Name + i, entityType.GetProperty(property.Name).GetValue(entitiesArray[i], null));
}
values.Add($"({string.Join(", ", properties.Select(p => "@" + p.Name + i))})");
}
query.SqlBuilder.Append(" " + string.Join(",", values));
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).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 GetInsert(TEntity entity)
{
var properties =
(IsIdentity
? SqlProperties.Where(p => !p.PropertyName.Equals(IdentitySqlProperty.PropertyName, StringComparison.OrdinalIgnoreCase))
: SqlProperties).ToList();
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
}
var query = new SqlQuery(entity);
query.SqlBuilder.Append(
"INSERT INTO " + TableName
+ " (" + string.Join(", ", properties.Select(p => p.ColumnName)) + ")" // columNames
+ " VALUES (" + string.Join(", ", properties.Select(p => "@" + p.PropertyName)) + ")"); // values
if (IsIdentity)
{
switch (Config.SqlProvider)
{
case SqlProvider.MSSQL:
query.SqlBuilder.Append(" SELECT SCOPE_IDENTITY() AS " + IdentitySqlProperty.ColumnName);
break;
case SqlProvider.MySQL:
query.SqlBuilder.Append("; SELECT CONVERT(LAST_INSERT_ID(), SIGNED INTEGER) AS " + IdentitySqlProperty.ColumnName);
break;
case SqlProvider.PostgreSQL:
query.SqlBuilder.Append(" RETURNING " + IdentitySqlProperty.ColumnName);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
return(query);
}
