/// <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); }