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