public SelectWriterResult GenerateSql <T>(SelectQuery <T> selectQuery, AutoNamingDynamicParameters parameters = null, bool enforceAlias = false) where T : class, new() { // get fetch tree structure var rootNode = this.fetchTreeParser.GetFetchTree(selectQuery, out _, out var numberCollectionFetches); return(this.InnerGenerateSql(selectQuery, parameters ?? new AutoNamingDynamicParameters(), enforceAlias, rootNode, numberCollectionFetches, new StringBuilder(), false)); }
private SqlWriterResult InnerGenerateCountSql <T>(SelectQuery <T> selectQuery, FetchNode rootNode, int numberCollectionFetches) where T : class, new() // add where clause { var whereSql = new StringBuilder(); var parameters = new AutoNamingDynamicParameters(); this.AddWhereClause(selectQuery.WhereClauses, whereSql, parameters, ref rootNode); // add in the tables var columnSql = new StringBuilder(); var tableSql = new StringBuilder(); this.AddTables(selectQuery, tableSql, columnSql, rootNode, false); var sql = new StringBuilder(15 + tableSql.Length + whereSql.Length); sql.Append("select count("); if (numberCollectionFetches == 0) { sql.Append("1"); } else { sql.Append("distinct "); if (rootNode != null) { sql.Append(rootNode.Alias); sql.Append('.'); } this.Dialect.AppendQuotedName( sql, this.Configuration.GetMap <T>() .PrimaryKey.DbName); } sql.Append(")"); sql.Append(tableSql); sql.Append(whereSql); return(new SqlWriterResult(sql.ToString(), parameters)); }
public SelectWriterResult GenerateSql <T>(IEnumerable <Expression <Func <T, bool> > > whereClauses, FetchNode rootNode, AutoNamingDynamicParameters parameters) { if (whereClauses.IsEmpty()) { return(new SelectWriterResult(string.Empty, null, rootNode)); } this.InitVariables(); this.autoNamingDynamicParameters = parameters; this.modifiedRootNode = rootNode; foreach (var whereClause in whereClauses) { this.VisitWhereClause(whereClause); this.sqlElements.Enqueue(new StringElement(" and ")); } this.InferInnerJoins(); return(new SelectWriterResult(this.GetSql(), this.autoNamingDynamicParameters, this.modifiedRootNode)); }
public SqlWriterResult GenerateBulkSql <T>(Action <T> updateAction, IEnumerable <Expression <Func <T, bool> > > predicates) where T : class, new() { var predicateArray = predicates as Expression <Func <T, bool> >[] ?? predicates?.ToArray(); // add where clause var whereSql = new StringBuilder(); var parameters = new AutoNamingDynamicParameters(); FetchNode rootNode = null; if (predicateArray != null) { this.AddWhereClause(predicateArray, whereSql, parameters, ref rootNode); } // run the update var entity = new T(); ((ISetLogger)entity).EnableSetLogging(); updateAction(entity); // find the set properties var setLogger = (ISetLogger)entity; var setProps = setLogger.GetSetProperties(); if (!setProps.Any()) { return(new SqlWriterResult(string.Empty, parameters)); } if (rootNode == null) { // the where clauses on are on the root table return(new SqlWriterResult(this.GetSimpleUpdateQuery(setProps, entity, parameters, whereSql), parameters)); } // cross table where clause return(new SqlWriterResult(this.GetMultiTableUpdateQuery(setProps, entity, parameters, whereSql, rootNode), parameters)); }
public SqlWriterResult GenerateBulkSql <T>(IEnumerable <Expression <Func <T, bool> > > predicates) { var predicateArray = predicates as Expression <Func <T, bool> >[] ?? predicates?.ToArray(); // add where clause var whereSql = new StringBuilder(); var parameters = new AutoNamingDynamicParameters(); FetchNode rootNode = null; if (predicateArray != null) { this.AddWhereClause(predicateArray, whereSql, parameters, ref rootNode); } if (rootNode == null) { // the where clauses were all on the root table return(new SqlWriterResult(this.GetSimpleDeleteQuery <T>(whereSql), parameters)); } // cross table where clause return(new SqlWriterResult(this.GetMultiTableDeleteQuery <T>(whereSql, rootNode), parameters)); }
internal void AddWhereClause <T>(IList <Expression <Func <T, bool> > > whereClauses, StringBuilder sql, AutoNamingDynamicParameters parameters, ref FetchNode rootNode) { var whereClauseWriter = new WhereClauseWriter(this.Dialect, this.Configuration); var result = whereClauseWriter.GenerateSql(whereClauses, rootNode, parameters); if (result.Sql.Length > 0) { sql.Append(result.Sql); } rootNode = result.FetchTree; }
private FetchNode GenerateNoPagingSql <T>(SelectQuery <T> selectQuery, bool enforceAlias, FetchNode rootNode, StringBuilder sql, int numberCollectionFetches, AutoNamingDynamicParameters parameters, bool isProjectedQuery) where T : class, new() { var columnSql = new StringBuilder(); var tableSql = new StringBuilder(); var whereSql = new StringBuilder(); var orderSql = new StringBuilder(); if (rootNode == null && enforceAlias) { rootNode = new FetchNode(); } // add where clause this.AddWhereClause(selectQuery.WhereClauses, whereSql, parameters, ref rootNode); // add select columns this.AddRootColumns(selectQuery, columnSql, rootNode, isProjectedQuery); // do columns second as we may not be fetching but need joins for the where clause // add in the tables this.AddTables(selectQuery, tableSql, columnSql, rootNode, isProjectedQuery); // add order by if (selectQuery.OrderClauses.Any()) { var containsPrimaryKeyClause = this.AddOrderByClause(selectQuery.OrderClauses, orderSql, rootNode); if (numberCollectionFetches > 0 && !containsPrimaryKeyClause) { this.AppendDefaultOrderBy <T>(rootNode, orderSql, isFirstOrderClause: false); } } else if (numberCollectionFetches > 0 || selectQuery.SkipN > 0 || selectQuery.TakeN > 0) { // need to add a default order on the sort clause this.AppendDefaultOrderBy <T>(rootNode, orderSql); } // construct the query sql.Append("select "); sql.Append(columnSql); sql.Append(tableSql); sql.Append(whereSql); sql.Append(orderSql); //// if anything is added after orderSql then the paging will probably need changing // apply paging // only add paging to the query if it doesn't have any collection fetches if (selectQuery.TakeN > 0 || selectQuery.SkipN > 0) { this.Dialect.ApplySkipTake(sql, orderSql, selectQuery.TakeN, selectQuery.SkipN); if (selectQuery.TakeN > 0) { parameters.Add("@take", selectQuery.TakeN); } if (selectQuery.SkipN > 0) { parameters.Add("@skip", selectQuery.SkipN); } } if (selectQuery.IsForUpdate) { this.Dialect.AppendForUpdateOnQueryFinish(sql); } return(rootNode); }
private FetchNode GeneratePagingCollectionSql <T>(SelectQuery <T> selectQuery, bool enforceAlias, FetchNode rootNode, StringBuilder sql, int numberCollectionFetches, AutoNamingDynamicParameters parameters, bool isProjectedQuery) where T : class, new() { // we write a subquery for the root type and all Many-to-One coming off it, we apply paging to that // we then left join to all of the collection columns // we need to apply the order by outside of the join as well var whereSql = new StringBuilder(); this.AddWhereClause(selectQuery.WhereClauses, whereSql, parameters, ref rootNode); // add root columns var innerColumnSql = new StringBuilder(); this.AddRootColumns(selectQuery, innerColumnSql, rootNode, isProjectedQuery); var innerColLength = innerColumnSql.Length; var innerColLengthMinusOne = innerColLength - 1; var outerColumnSqlTemp = new char[innerColLength]; for (var i = 0; i < innerColLength; i++) { if (innerColumnSql[i] == 't' && i < innerColLengthMinusOne && (i == 0 || innerColumnSql[i - 1] == ' ') && (innerColumnSql[i + 1] == '.' || innerColumnSql[i + 1] == '_')) { outerColumnSqlTemp[i] = 'i'; } else { outerColumnSqlTemp[i] = innerColumnSql[i]; } } var outerColumnSql = new StringBuilder(new string(outerColumnSqlTemp)); // outer columns are the same but reference subquery aliased as i var innerTableSql = new StringBuilder(); var outerTableSql = new StringBuilder(); this.AddTablesForPagedCollection(selectQuery, innerTableSql, outerTableSql, innerColumnSql, outerColumnSql, rootNode, isProjectedQuery); // add order by var innerOrderSql = new StringBuilder(); var orderClauses = new Queue <OrderClause <T> >(selectQuery.OrderClauses); // clone the queue for use in the outer clause if (selectQuery.OrderClauses.Any()) { this.AddOrderByClause(selectQuery.OrderClauses, innerOrderSql, rootNode); } else { this.AppendDefaultOrderBy <T>(rootNode, innerOrderSql); } // construct the query var innerSql = new StringBuilder("select "); innerSql.Append(innerColumnSql) .Append(innerTableSql) .Append(whereSql) .Append(innerOrderSql); //// if anything is added after orderSql then the paging will probably need changing this.Dialect.ApplySkipTake(innerSql, innerOrderSql, selectQuery.TakeN, selectQuery.SkipN); if (selectQuery.TakeN > 0) { parameters.Add("@take", selectQuery.TakeN); } if (selectQuery.SkipN > 0) { parameters.Add("@skip", selectQuery.SkipN); } if (selectQuery.IsForUpdate) { this.Dialect.AppendForUpdateOnQueryFinish(innerSql); } // now construct the outer query sql.Append("select ") .Append(outerColumnSql) .Append(" from (") .Append(innerSql) .Append(") as i") .Append(outerTableSql); var outerOrderSql = new StringBuilder(); if (orderClauses.Any()) { var containsPrimaryKeyClause = this.AddOrderByClause(orderClauses, outerOrderSql, rootNode, (c, n) => "i", (c, n) => c.DbName); if (!containsPrimaryKeyClause) { this.AppendDefaultOrderBy <T>(rootNode, outerOrderSql, "i", isFirstOrderClause: false); } } else { this.AppendDefaultOrderBy <T>(rootNode, outerOrderSql, "i"); } sql.Append(outerOrderSql); return(rootNode); }
private FetchNode GenerateNoPagingUnionSql <T>(SelectQuery <T> selectQuery, bool enforceAlias, FetchNode rootNode, StringBuilder sql, int numberCollectionFetches, AutoNamingDynamicParameters parameters, bool isProjectedQuery) where T : class, new() { var numQueries = rootNode.Children.Count(c => c.Value.Column.Relationship == RelationshipType.OneToMany || c.Value.ContainedCollectionfetchesCount > 0); var whereSql = new StringBuilder(); this.AddWhereClause(selectQuery.WhereClauses, whereSql, parameters, ref rootNode); var subQueryColumnSqls = new StringBuilder[numQueries]; var subQueryTableSqls = new StringBuilder[numQueries]; for (var i = 0; i < numQueries; i++) { subQueryColumnSqls[i] = new StringBuilder(); subQueryTableSqls[i] = new StringBuilder(); } var outerQueryColumnSql = new StringBuilder(); // add root columns foreach (var column in GetColumnsWithIncludesAndExcludes(rootNode?.IncludedColumns, rootNode?.ExcludedColumns, this.Configuration.GetMap <T>(), selectQuery.FetchAllProperties, isProjectedQuery) .Where( c => !rootNode.Children.ContainsKey(c.Name) || !rootNode.Children[c.Name] .IsFetched)) { foreach (var subQuery in subQueryColumnSqls) { this.AddColumn(subQuery, column, rootNode.Alias, column.DbName + rootNode.Alias); subQuery.Append(", "); } outerQueryColumnSql.Append("i."); this.Dialect.AppendQuotedName(outerQueryColumnSql, column.DbName + rootNode.Alias); outerQueryColumnSql.Append(" as "); this.Dialect.AppendQuotedName(outerQueryColumnSql, column.DbName); outerQueryColumnSql.Append(", "); } // remove extraneous , outerQueryColumnSql.Remove(outerQueryColumnSql.Length - 2, 2); foreach (var subQuery in subQueryColumnSqls) { subQuery.Remove(subQuery.Length - 2, 2); } this.AddTablesForNoPagingUnion(selectQuery, outerQueryColumnSql, subQueryColumnSqls, subQueryTableSqls, rootNode, isProjectedQuery); // add order by var orderSql = new StringBuilder(); if (selectQuery.OrderClauses.Any()) { var containsPrimaryKeyClause = this.AddOrderByClause(selectQuery.OrderClauses, orderSql, rootNode, (c, n) => "i", (c, n) => c.DbName + n.Alias); if (!containsPrimaryKeyClause) { this.AppendDefaultOrderBy <T>( rootNode, orderSql, "i", this.Configuration.GetMap <T>() .PrimaryKey.DbName + rootNode.Alias, false); } } else { this.AppendDefaultOrderBy <T>( rootNode, orderSql, "i", this.Configuration.GetMap <T>() .PrimaryKey.DbName + rootNode.Alias); } // now create the query sql.Append("select ") .Append(outerQueryColumnSql) .Append(" from ("); for (var i = 0; i < numQueries; i++) { sql.Append("select ") .Append(subQueryColumnSqls[i]) .Append(subQueryTableSqls[i]); if (whereSql.Length > 0) { sql.Append(whereSql); } if (selectQuery.IsForUpdate) { this.Dialect.AppendForUpdateOnQueryFinish(sql); } if (i < numQueries - 1) { sql.Append(" union all "); } } sql.Append(") as i"); sql.Append(orderSql); return(rootNode); }
private SelectWriterResult InnerGenerateSql <T>(SelectQuery <T> selectQuery, AutoNamingDynamicParameters parameters, bool enforceAlias, FetchNode rootNode, int numberCollectionFetches, StringBuilder sql, bool isProjectedQuery) where T : class, new() // add in any includes or excludes { if ((selectQuery.Includes != null && selectQuery.Includes.Any()) || (selectQuery.Excludes != null && selectQuery.Excludes.Any())) { var parser = new IncludeExcludeParser(this.Configuration); rootNode = rootNode ?? new FetchNode(); GetIncludeExcludes <T>(selectQuery.Includes, parser, rootNode, true); GetIncludeExcludes <T>(selectQuery.Excludes, parser, rootNode, false); } if (numberCollectionFetches > 0) { if (numberCollectionFetches > 1 && (rootNode.Children.Count(c => c.Value.Column.Relationship == RelationshipType.OneToMany || c.Value.ContainedCollectionfetchesCount > 0) > 1)) { // multiple one to many branches so we'll perform a union query if (selectQuery.TakeN > 0 || selectQuery.SkipN > 0) { // TODO this is temporary, should generate union query similar to next rootNode = this.GeneratePagingCollectionSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery); } else { rootNode = this.GenerateNoPagingUnionSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery); } } else { if (selectQuery.TakeN > 0 || selectQuery.SkipN > 0) { // we're sub-selecting so need to use a subquery rootNode = this.GeneratePagingCollectionSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery); } else { // we're fetching all things rootNode = this.GenerateNoPagingSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery); } } } else { // no collection fetches // see if we can transform to union query for non-root disjunctions var nonRootDisjunctionTransformationSucceeded = false; if (selectQuery.TakeN == 0 && selectQuery.SkipN == 0) { var outerJoinDisjunctionTransformer = new OuterJoinDisjunctionTransformer(this.Configuration); int substitutedWhereClauseIndex = -1; Expression <Func <T, bool> > substitutedWhereClause = null; IEnumerable <Expression <Func <T, bool> > > substitutions = null; foreach (var whereClauseEntry in selectQuery.WhereClauses.AsSmartEnumerable()) { var whereClause = whereClauseEntry.Value; var result = outerJoinDisjunctionTransformer.AttemptGetOuterJoinDisjunctions(whereClause); if (result.ContainsOuterJoinDisjunction) { if (substitutedWhereClause != null) { // we'll bail out here as we're not supporting multiple disjunctions substitutedWhereClause = null; break; } substitutedWhereClauseIndex = whereClauseEntry.Index; substitutedWhereClause = whereClause; substitutions = result.UnionWhereClauses; } } if (substitutedWhereClause != null) { // we don't want to order the unioned queries, we'll order them subsequently var originalOrderClauses = selectQuery.OrderClauses; selectQuery.OrderClauses = new Queue <OrderClause <T> >(); // we need to copy the fetch node and re-use it inside every query var originalRootNode = rootNode != null ? rootNode.Clone() : new FetchNode(); // we force the unions to have the same alias foreach (var substitution in substitutions.AsSmartEnumerable()) { // swap out the original where clause for the substitute selectQuery.WhereClauses.RemoveAt(substitutedWhereClauseIndex); selectQuery.WhereClauses.Insert(substitutedWhereClauseIndex, substitution.Value); var substitutionRootNode = originalRootNode.Clone(); rootNode = this.GenerateNoPagingSql(selectQuery, enforceAlias, substitutionRootNode, sql, numberCollectionFetches, parameters, isProjectedQuery); if (!substitution.IsLast) { sql.Append(" union "); } } if (originalOrderClauses != null && originalOrderClauses.Any()) { this.AddOrderByClause(originalOrderClauses, sql, rootNode); } nonRootDisjunctionTransformationSucceeded = true; } } if (!nonRootDisjunctionTransformationSucceeded) { rootNode = this.GenerateNoPagingSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery); } } return(new SelectWriterResult(sql.ToString(), parameters, rootNode) { NumberCollectionsFetched = numberCollectionFetches }); }
private void AddSetClauses <T>(IEnumerable <string> setProps, IMap <T> map, StringBuilder sql, T entity, AutoNamingDynamicParameters parameters, bool includeRootAlias) { foreach (var updatedPropEntry in setProps.AsSmartEnumerable()) { if (includeRootAlias) { sql.Append("t."); } var column = map.Columns[updatedPropEntry.Value]; this.Dialect.AppendQuotedName(sql, column.DbName); var paramName = "@" + updatedPropEntry.Value; var propertyValue = map.GetColumnValue(entity, column); if (propertyValue == null) { parameters.Add(paramName, null); } else { parameters.Add(paramName, this.GetValueOrPrimaryKey(column, propertyValue)); } sql.Append(" = "); sql.Append(paramName); if (!updatedPropEntry.IsLast) { sql.Append(", "); } } }
private string GetSimpleUpdateQuery <T>(IEnumerable <string> setProps, T entity, AutoNamingDynamicParameters parameters, StringBuilder whereSql) { var map = this.Configuration.GetMap <T>(); var sql = new StringBuilder(); sql.Append("update "); this.Dialect.AppendQuotedTableName(sql, map); sql.Append(" set "); this.AddSetClauses(setProps, map, sql, entity, parameters, false); sql.Append(whereSql); return(sql.ToString()); }
private string GetMultiTableUpdateQuery <T>(IEnumerable <string> setProps, T entity, AutoNamingDynamicParameters parameters, StringBuilder whereSql, FetchNode rootNode) { var map = this.Configuration.GetMap <T>(); var sql = new StringBuilder(); sql.Append("update t set "); this.AddSetClauses(setProps, map, sql, entity, parameters, true); sql.Append(" from "); this.Dialect.AppendQuotedTableName(sql, map); sql.Append(" as t"); foreach (var node in rootNode.Children) { this.AddNode(node.Value, sql); } sql.Append(whereSql); return(sql.ToString()); }