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