public string SelectSet(IClassMapper classMap, IPredicate predicate,
IList <ISort> sort,
int firstResult,
int maxResults,
IDictionary <string, object> parameters,
IList <IProjection> projections)
{
Guard.EnumerableArgumentNull(sort, nameof(sort));
Guard.ArgumentNull(parameters, nameof(parameters));
var context = new SqlGenerationContext(Configuration.Dialect, classMap);
var selectQuery = GetSelectQuery(classMap, projections);
var innerSql = new StringBuilder(selectQuery);
if (predicate != null)
{
innerSql.Append(" WHERE ")
.Append(predicate.GetSql(context, parameters));
}
var orderBy = sort.Select(s => GetColumnName(classMap, s.PropertyName, false) + (s.Ascending ? " ASC" : " DESC")).AppendStrings();
innerSql.Append(" ORDER BY " + orderBy);
var sql = Configuration.Dialect.GetSetSql(innerSql.ToString(), firstResult, maxResults, parameters);
return(sql);
}
public string Update(IClassMapper classMap,
IPredicate predicate,
IDictionary <string, object> parameters)
{
Guard.ArgumentNull(predicate, nameof(predicate));
Guard.ArgumentNull(parameters, nameof(parameters));
var context = new SqlGenerationContext(Configuration.Dialect, classMap);
var setSql = _updateCache.GetOrAdd(classMap.EntityType.TypeHandle, x =>
{
var columns = classMap.GetMutableColumns();
if (!columns.Any())
{
throw new ArgumentException("No columns were mapped.");
}
return(columns.Select(
p => $"{GetColumnName(classMap, p, false)} = {Configuration.Dialect.ParameterPrefix}{p.Name}")
.AppendStrings());
});
return($"UPDATE {GetTableName(classMap)} SET {setSql} WHERE {predicate.GetSql(context, parameters)}");
}
public string Select(
IClassMapper map,
IPredicate predicate,
IList <ISort> sort,
IDictionary <string, object> parameters,
IList <IProjection> projections)
{
Guard.ArgumentNull(parameters, nameof(parameters));
var context = new SqlGenerationContext(Configuration.Dialect, map);
var selectQuery = GetSelectQuery(map, projections);
var sql = new StringBuilder(selectQuery);
if (predicate != null)
{
sql.Append(" WHERE ")
.Append(predicate.GetSql(context, parameters));
}
if (sort != null && sort.Any())
{
sql.Append(" ORDER BY ")
.Append(sort.Select(s => GetColumnName(map, s.PropertyName, false) + (s.Ascending ? " ASC" : " DESC")).AppendStrings());
}
return(sql.ToString());
}
public string AtomicIncrement(IClassMapper map, IPredicate predicate, IDictionary <string, object> parameters, IProjection projection, int amount)
{
var context = new SqlGenerationContext(Configuration.Dialect, map);
var column = GetColumnName(map, projection.PropertyName, false);
return($"UPDATE {GetTableName(map)} SET {column} = {column} + {amount} WHERE {predicate.GetSql(context, parameters)}");
}
public string Delete(IClassMapper classMap, IPredicate predicate, IDictionary <string, object> parameters)
{
Guard.ArgumentNull(predicate, nameof(predicate));
Guard.ArgumentNull(parameters, nameof(parameters));
var context = new SqlGenerationContext(Configuration.Dialect, classMap);
var sql = new StringBuilder($"DELETE FROM {GetTableName(classMap)}");
sql.Append(" WHERE ").Append(predicate.GetSql(context, parameters));
return(sql.ToString());
}
public string Update(IClassMapper classMap,
IPredicate predicate,
IList <IProjectionSet> projectionSets,
Dictionary <string, object> parameters)
{
Guard.ArgumentNull(predicate, nameof(predicate));
Guard.ArgumentNull(projectionSets, nameof(projectionSets));
Guard.ArgumentNull(parameters, nameof(parameters));
var context = new SqlGenerationContext(Configuration.Dialect, classMap);
var setSql = projectionSets.Select(x => GetUpdateParameterSql(classMap, x, parameters));
return($"UPDATE {GetTableName(classMap)} SET {string.Join(", ", setSql)} WHERE {predicate.GetSql(context, parameters)}");
}
public string Count(IClassMapper classMap, IPredicate predicate, IDictionary <string, object> parameters)
{
Guard.ArgumentNull(parameters, nameof(parameters));
var context = new SqlGenerationContext(Configuration.Dialect, classMap);
var countSql = $"SELECT COUNT(*) AS {Configuration.Dialect.WrapWithQuotes("Total")} FROM {GetTableName(classMap)}";
var sql = new StringBuilder(countSql);
if (predicate != null)
{
sql.Append(" WHERE ")
.Append(predicate.GetSql(context, parameters));
}
return(sql.ToString());
}
private static SqlGenerationContext GenerateSQL(
IHaveSelectionSet context,
EntityMetadataContext metadata,
SqlGenerationContext sqlContext = null,
SQLOperation operation = SQLOperation.SELECT,
int level = 0,
string parentAlias = null,
EntityMetadataRelation relationMetadata = null)
{
if (sqlContext == null)
{
sqlContext = new SqlGenerationContext();
}
var key = metadata.Keys.Values.ToArray();
var schema =
metadata.CustomMetadata.TryGetValue(Globals.CUSTOM_METADATA_SCHEMA, out var customSchema) ?
customSchema : "dbo";
var table =
metadata.CustomMetadata.TryGetValue(Globals.CUSTOM_METADATA_TABLE, out var customTable) ?
customTable : metadata.Type.Name;
var queriedFields = context.GetSelectedFields();
var alias = GetAlias(level);
var entityFields =
metadata.Included.Keys
.Select(f => f.ToLower())
.Intersect(queriedFields.Keys)
.Select(f => new { field = $"{f}", exp = $"{alias}.[{f}]" })
.ToArray();
sqlContext.SelectFields.AddRange(entityFields);
sqlContext.SplitOnTypes.Add(metadata.Type);
if (level > 0)
{
sqlContext.SplitOnFields.Add(entityFields.First().field);
}
if (operation == SQLOperation.SELECT)
{
sqlContext.Sql = $"SELECT %fields% FROM [{schema}].[{table}] {alias}";
foreach (var field in queriedFields)
{
if (metadata.Relations.TryGetValue(field.Key.ToLower(), out var relation))
{
sqlContext.Relations.Add(relation);
GenerateSQL(
field.Value,
relation.EntityRight,
sqlContext,
operation: SQLOperation.JOIN,
level: level + 1,
parentAlias: alias,
relationMetadata: relation
);
}
}
}
else if (operation == SQLOperation.JOIN)
{
if (relationMetadata.IsCollection)
{
sqlContext.Sql += $" LEFT JOIN [{schema}].[{table}] {alias} ON {ManyForeignKeyCriteria(parentAlias, alias, relationMetadata)}";
}
else
{
sqlContext.Sql += $" LEFT JOIN [{schema}].[{table}] {alias} ON {SingleForeignKeyCriteria(parentAlias, alias, relationMetadata)}";
}
}
if (level == 0)
{
sqlContext.Sql = sqlContext.Sql.Replace("%fields%", string.Join(",", sqlContext.SelectFields.Select(x => x.exp)));
}
return(sqlContext);
}
