/// <inheritdoc />
public virtual SqlQuery GetDelete(TEntity entity)
{
var sqlQuery = new SqlQuery();
var whereSql = " WHERE " + string.Join(" AND ", KeySqlProperties.Select(p => TableName + "." + p.ColumnName + " = @" + p.PropertyName));
if (!LogicalDelete)
{
sqlQuery.SqlBuilder.Append("DELETE FROM " + TableName + whereSql);
}
else
{
sqlQuery.SqlBuilder.Append("UPDATE " + TableName + " SET " + StatusPropertyName + " = " + LogicalDeleteValue);
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
sqlQuery.SqlBuilder.Append(", " + UpdatedAtPropertyMetadata.ColumnName + " = @" + UpdatedAtPropertyMetadata.PropertyName);
}
sqlQuery.SqlBuilder.Append(whereSql);
}
sqlQuery.SetParam(entity);
return(sqlQuery);
}
/// <inheritdoc />
public virtual SqlQuery GetDelete(TEntity entity)
{
var sqlQuery = new SqlQuery();
var whereSql = string.Format(" WHERE {0}", string.Join(" AND ", KeySqlProperties.Select(p => string.Format("{0}.{1} = @{2}", TableName, p.ColumnName, p.PropertyName))));
if (!LogicalDelete)
{
sqlQuery.SqlBuilder.AppendFormat("DELETE FROM {0}{1}", TableName, whereSql);
}
else
{
sqlQuery.SqlBuilder.AppendFormat("UPDATE {0} SET {1} = {2}", TableName, StatusPropertyName, LogicalDeleteValue);
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
sqlQuery.SqlBuilder.Append(string.Format(", {0} = @{1}", UpdatedAtPropertyMetadata.ColumnName, UpdatedAtPropertyMetadata.PropertyName));
}
sqlQuery.SqlBuilder.Append(whereSql);
}
sqlQuery.SetParam(entity);
return(sqlQuery);
}
public SqlQuery GetSelectByIds(TEntity entity)
{
var query = new SqlQuery(entity);
query.SqlBuilder.Append("SELECT * FROM " + TableName
+ " WHERE " + string.Join(" AND ", KeySqlProperties.Select(p => p.ColumnName + " = @" + p.PropertyName)));
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 GetDelete(TEntity entity)
{
var sqlQuery = new SqlQuery();
var whereAndSql =
string.Join(" AND ", KeySqlProperties.Select(p => string.Format("{0}.{1} = @{2}", TableName, p.ColumnName, p.PropertyName)));
if (!LogicalDelete)
{
sqlQuery.SqlBuilder
.Append("DELETE FROM ")
.Append(TableName)
.Append(" WHERE ")
.Append(whereAndSql);
}
else
{
sqlQuery.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" SET ")
.Append(StatusPropertyName)
.Append(" = ")
.Append(LogicalDeleteValue);
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);
sqlQuery.SqlBuilder
.Append(", ")
.Append(UpdatedAtPropertyMetadata.ColumnName)
.Append(" = @")
.Append(UpdatedAtPropertyMetadata.PropertyName);
}
sqlQuery.SqlBuilder
.Append(" WHERE ")
.Append(whereAndSql);
}
sqlQuery.SetParam(entity);
return(sqlQuery);
}
/// <inheritdoc />
public virtual SqlQuery GetDelete(TEntity entity)
{
var sqlQuery = new SqlQuery();
var whereAndSql =
string.Join(" AND ", KeySqlProperties.Select(p => string.Format("{0}.{1} = @{2}", TableName, p.ColumnName, p.PropertyName)));
if (HasRowVersion)
{
whereAndSql += " AND " + RowVersionPropertyMetadata.ColumnName + " = @" + RowVersionPropertyMetadata.PropertyName;
}
if (!LogicalDelete)
{
sqlQuery.SqlBuilder
.Append("DELETE FROM ")
.Append(TableName)
.Append(" WHERE ")
.Append(whereAndSql);
}
else
{
sqlQuery.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" SET ")
.Append(StatusPropertyName)
.Append(" = ")
.Append(LogicalDeleteValue);
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
sqlQuery.SqlBuilder
.Append(", ")
.Append(UpdatedAtPropertyMetadata.ColumnName)
.Append(" = @")
.Append(UpdatedAtPropertyMetadata.PropertyName);
}
sqlQuery.SqlBuilder
.Append(" WHERE ")
.Append(whereAndSql);
}
sqlQuery.SetParam(entity);
LogSqlQuery(sqlQuery);
return(sqlQuery);
}
/// <inheritdoc />
public virtual SqlQuery GetDelete(TEntity entity, bool isLogicDelete = true)
{
var sqlQuery = new SqlQuery();
var cacheKey = _sqlCacheKey + "GetDelete" + "_" + isLogicDelete;
if (StringCache.ContainsKey(cacheKey))
{
sqlQuery.SqlBuilder.Append(StringCache[cacheKey]);
}
else
{
var whereSql = $" WHERE {string.Join(" AND ", KeySqlProperties.Select(p => $" {GetPropertyName(p.Name)} = @{p.Name} "))}";
if (!isLogicDelete)
{
sqlQuery.SqlBuilder.Append($"DELETE FROM {TableName} {whereSql}");
}
else
{
sqlQuery.SqlBuilder.Append($"UPDATE {TableName} SET {Config.LogicDeleteSql} ");
foreach (var prop in Config.UpdateCommonProperty(entity.autoid))
{
sqlQuery.SqlBuilder.Append($", {GetPropertyName(prop.Key)} = @{prop.Key} ");
}
sqlQuery.SqlBuilder.Append(whereSql);
}
StringCache.TryAdd(cacheKey, sqlQuery.GetSql());
}
var dic = new Dictionary <string, object>();
foreach (var keySqlProperty in KeySqlProperties)
{
dic.Add(keySqlProperty.Name, keySqlProperty.GetValue(entity));
}
if (isLogicDelete)
{
foreach (var prop in Config.UpdateCommonProperty(entity.autoid))
{
dic.Add(prop.Key, prop.Value);
}
}
sqlQuery.SetParam(dic);
return(sqlQuery);
}
/// <inheritdoc />
public virtual SqlQuery GetDelete(TEntity entity)
{
var sqlQuery = new SqlQuery(TableName, QueryType.Delete);
var whereAndSql =
string.Join(" AND ", KeySqlProperties.Select(p => string.Format("{0}.{1} = @{2}", TableName, p.ColumnName, p.PropertyName)));
if (!LogicalDelete)
{
sqlQuery.SqlBuilder
.Append("DELETE FROM ")
.Append(TableName)
.Append(" WHERE ")
.Append(whereAndSql);
}
else
{
sqlQuery.SqlBuilder
.Append("UPDATE ")
.Append(TableName)
.Append(" SET ")
.Append(LogicalDeletePropertyMetadata.ColumnName)
.Append(" = ")
.Append(LogicalDeleteProperty.PropertyType.IsDateTime() ? $"'{DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss")}'" : GetLogicalDeleteValue());
if (HasUpdatedAt)
{
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
sqlQuery.SqlBuilder
.Append(", ")
.Append(UpdatedAtPropertyMetadata.ColumnName)
.Append(" = @")
.Append(UpdatedAtPropertyMetadata.PropertyName);
}
sqlQuery.SqlBuilder
.Append(" WHERE ")
.Append(whereAndSql);
}
sqlQuery.SetParam(entity);
return(sqlQuery);
}
/// <summary>
///
/// </summary>
public virtual SqlQuery GetDelete(TEntity entity)
{
var sqlQuery = new SqlQuery(entity);
var whereSql = " WHERE " + string.Join(" AND ", KeySqlProperties.Select(p => p.ColumnName + " = @" + p.Name));
if (!LogicalDelete)
{
sqlQuery.SqlBuilder.Append("DELETE FROM " + TableName + whereSql);
}
else
{
if (HasDateChanged)
{
DateChangedProperty.SetValue(entity, DateTime.UtcNow);
}
sqlQuery.SqlBuilder.Append("UPDATE " + TableName + " SET " + StatusPropertyName + " = " + LogicalDeleteValue + whereSql);
}
return(sqlQuery);
}
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);
}
/// <summary>
/// Performs bulk upsert operation
/// </summary>
/// <param name="entities"></param>
/// <returns></returns>
public virtual SqlQuery GetBulkUpSert(IEnumerable <TEntity> entities)
{
var query = new SqlQuery();
var t = TableName.Replace("[", "").Replace("]", "");
var tmpTable = $"#{t}_{new Random(100).Next()}";
//get bulk insert
var bulkSql = this.GetTempTableInsert(entities, tmpTable);
var bulkUpdateSql = this.GetTempTableUpdate(entities);
var on = string.Join(" AND ", KeySqlProperties.Select(p => "target." + p.ColumnName + " = source." + p.ColumnName));
query.SqlBuilder.Append(this.CreateTempTable(entities, tmpTable));
query.SqlBuilder.Append(";");
query.SqlBuilder.Append($"MERGE {TableName} as target ");
query.SqlBuilder.Append($"USING {tmpTable} as source ");
query.SqlBuilder.Append($"ON {on} ");
query.SqlBuilder.Append($"WHEN MATCHED THEN {bulkUpdateSql} ");
query.SqlBuilder.Append($"WHEN NOT MATCHED THEN {bulkSql}");
query.SqlBuilder.Append(";");
query.SqlBuilder.Append(this.DropTempTable(tmpTable));
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.Name.Equals(p.Name, StringComparison.OrdinalIgnoreCase))).ToArray();
var query = new SqlQuery();
var parameters = new Dictionary <string, object>();
for (var i = 0; i < entitiesArray.Length; i++)
{
if (i > 0)
{
query.SqlBuilder.Append("; ");
}
query.SqlBuilder.Append($"UPDATE {TableName} SET {string.Join(", ", properties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name + i}"))} WHERE {string.Join(" AND ", KeySqlProperties.Select(p => $"{GetPropertyName(p.Name)} = @{p.Name + i}"))} ");
// ReSharper disable PossibleNullReferenceException
foreach (var property in properties)
{
parameters.Add(property.Name + i, entityType.GetProperty(property.Name).GetValue(entitiesArray[i], null));
}
foreach (var property in KeySqlProperties)
{
parameters.Add(property.Name + i, entityType.GetProperty(property.Name).GetValue(entitiesArray[i], null));
}
// ReSharper restore PossibleNullReferenceException
}
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(TEntity entity)
{
var properties = SqlProperties.Where(p => !KeySqlProperties.Any(k => k.PropertyName.Equals(p.PropertyName, StringComparison.OrdinalIgnoreCase)));
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.Select(p => p.ColumnName + " = @" + p.PropertyName)));
return(query);
}
private SqlQuery GetSelect(Expression <Func <TEntity, bool> > predicate, bool firstOnly, int pageNo, int pageSize, bool includeLogicalDeleted, object from = null, object to = null, string columnName = "", bool betweenQuery = false, params Expression <Func <TEntity, object> >[] includes)
{
var sqlQuery = InitBuilderSelect(firstOnly);
var joinsBuilder = AppendJoinToSelect(sqlQuery, includes);
sqlQuery.SqlBuilder
.Append(" FROM ")
.Append(TableName)
.Append(" ");
if (includes.Any())
{
sqlQuery.SqlBuilder.Append(joinsBuilder);
}
AppendWherePredicateQuery(sqlQuery, predicate, QueryType.Select, includeLogicalDeleted);
if (betweenQuery)
{
sqlQuery.SqlBuilder
.Append(predicate == null && !LogicalDelete ? "WHERE" : "AND")
.Append(" ")
.Append(TableName)
.Append(".")
.Append(columnName)
.Append(" BETWEEN '")
.Append(from)
.Append("' AND '")
.Append(to)
.Append("'");
}
if (firstOnly && (Config.SqlProvider == SqlProvider.MySQL || Config.SqlProvider == SqlProvider.PostgreSQL))
{
sqlQuery.SqlBuilder.Append(" LIMIT 1");
}
if (!firstOnly && pageNo > 0 && pageSize > 0)
{
int offset = (pageNo - 1) * pageSize;
int fetch = pageSize;
string keyProperties = string.Join(',', KeySqlProperties.Select(x => x.PropertyName).ToList());
sqlQuery.SqlBuilder.Append($" ORDER BY {keyProperties}");
if (Config.SqlProvider == SqlProvider.MSSQL)
{
sqlQuery.SqlBuilder.Append($" OFFSET {offset} ROWS FETCH NEXT {fetch} ROWS ONLY;");
}
else if (Config.SqlProvider == SqlProvider.MySQL || Config.SqlProvider == SqlProvider.PostgreSQL)
{
sqlQuery.SqlBuilder.Append($" LIMIT {offset}, {fetch};");
}
//Count Pages
var countPagesQuery = new SqlQuery();
countPagesQuery.SqlBuilder.Append($" SELECT CAST((CEILING(CAST(COUNT(*) AS DECIMAL) / CAST({fetch} AS DECIMAL))) AS INT) ");
countPagesQuery.SqlBuilder
.Append(" FROM ")
.Append(TableName)
.Append(" ");
if (includes.Any())
{
countPagesQuery.SqlBuilder.Append(joinsBuilder);
}
AppendWherePredicateQuery(countPagesQuery, predicate, QueryType.Select, includeLogicalDeleted);
if (betweenQuery)
{
countPagesQuery.SqlBuilder
.Append(predicate == null && !LogicalDelete ? "WHERE" : "AND")
.Append(" ")
.Append(TableName)
.Append(".")
.Append(columnName)
.Append(" BETWEEN '")
.Append(from)
.Append("' AND '")
.Append(to)
.Append("'");
}
sqlQuery.SqlBuilder.Append(countPagesQuery.GetSql());
}
LogSqlQuery(sqlQuery);
return(sqlQuery);
}
public virtual SqlQuery GetDeleteByIds(string ids)
{
var sqlQuery = new SqlQuery();
sqlQuery.SqlBuilder.Append("DELETE FROM " + TableName + " WHERE " + string.Join(" ", KeySqlProperties.Select(p => p.ColumnName + " IN (" + ids + ")")));
return(sqlQuery);
}
