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