Mercurial > pub > bltoolkit
diff Source/Data/Sql/SqlProvider/BasicSqlProvider.cs @ 0:f990fcb411a9
Копия текущей версии из github
author | cin |
---|---|
date | Thu, 27 Mar 2014 21:46:09 +0400 |
parents | |
children |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Source/Data/Sql/SqlProvider/BasicSqlProvider.cs Thu Mar 27 21:46:09 2014 +0400 @@ -0,0 +1,3606 @@ +using System; +using System.Collections; +using System.Collections.Generic; +using System.Data; +using System.Globalization; +using System.Linq; +using System.Linq.Expressions; +using System.Reflection; +using System.Text; + +namespace BLToolkit.Data.Sql.SqlProvider +{ + using DataProvider; + using Mapping; + using Linq; + using Reflection; + + public abstract class BasicSqlProvider : ISqlProvider + { + #region Init + + public SqlQuery SqlQuery { get; set; } + public int Indent { get; set; } + + private int _nextNesting = 1; + private int _nesting; + public int Nesting + { + get { return _nesting; } + } + + bool _skipAlias; + + public Step BuildStep { get; set; } + + #endregion + + #region Support Flags + + public virtual bool SkipAcceptsParameter { get { return true; } } + public virtual bool TakeAcceptsParameter { get { return true; } } + public virtual bool IsTakeSupported { get { return true; } } + public virtual bool IsSkipSupported { get { return true; } } + public virtual bool IsSubQueryTakeSupported { get { return true; } } + public virtual bool IsSubQueryColumnSupported { get { return true; } } + public virtual bool IsCountSubQuerySupported { get { return true; } } + public virtual bool IsNestedJoinSupported { get { return true; } } + public virtual bool IsNestedJoinParenthesisRequired { get { return false; } } + public virtual bool IsIdentityParameterRequired { get { return false; } } + public virtual bool IsApplyJoinSupported { get { return false; } } + public virtual bool IsInsertOrUpdateSupported { get { return true; } } + public virtual bool CanCombineParameters { get { return true; } } + public virtual bool IsGroupByExpressionSupported { get { return true; } } + public virtual int MaxInListValuesCount { get { return int.MaxValue; } } + + public virtual bool ConvertCountSubQuery(SqlQuery subQuery) + { + return true; + } + + #endregion + + #region CommandCount + + public virtual int CommandCount(SqlQuery sqlQuery) + { + return 1; + } + + #endregion + + #region BuildSql + + public virtual int BuildSql(int commandNumber, SqlQuery sqlQuery, StringBuilder sb, int indent, int nesting, bool skipAlias) + { + SqlQuery = sqlQuery; + Indent = indent; + _nesting = nesting; + _nextNesting = _nesting + 1; + _skipAlias = skipAlias; + + if (commandNumber == 0) + { + BuildSql(sb); + + if (sqlQuery.HasUnion) + { + foreach (var union in sqlQuery.Unions) + { + AppendIndent(sb); + sb.Append("UNION"); + if (union.IsAll) sb.Append(" ALL"); + sb.AppendLine(); + + CreateSqlProvider().BuildSql(commandNumber, union.SqlQuery, sb, indent, nesting, skipAlias); + } + } + } + else + { + BuildCommand(commandNumber, sb); + } + + return _nextNesting; + } + + protected virtual void BuildCommand(int commandNumber, StringBuilder sb) + { + } + + #endregion + + #region Overrides + + protected virtual int BuildSqlBuilder(SqlQuery sqlQuery, StringBuilder sb, int indent, int nesting, bool skipAlias) + { + if (!IsSkipSupported && sqlQuery.Select.SkipValue != null) + throw new SqlException("Skip for subqueries is not supported by the '{0}' provider.", Name); + + if (!IsTakeSupported && sqlQuery.Select.TakeValue != null) + throw new SqlException("Take for subqueries is not supported by the '{0}' provider.", Name); + + return CreateSqlProvider().BuildSql(0, sqlQuery, sb, indent, nesting, skipAlias); + } + + protected abstract ISqlProvider CreateSqlProvider(); + + protected virtual bool ParenthesizeJoin() + { + return false; + } + + protected virtual void BuildSql(StringBuilder sb) + { + switch (SqlQuery.QueryType) + { + case QueryType.Select : BuildSelectQuery (sb); break; + case QueryType.Delete : BuildDeleteQuery (sb); break; + case QueryType.Update : BuildUpdateQuery (sb); break; + case QueryType.Insert : BuildInsertQuery (sb); break; + case QueryType.InsertOrUpdate : BuildInsertOrUpdateQuery(sb); break; + default : BuildUnknownQuery (sb); break; + } + } + + protected virtual void BuildDeleteQuery(StringBuilder sb) + { + BuildStep = Step.DeleteClause; BuildDeleteClause (sb); + BuildStep = Step.FromClause; BuildFromClause (sb); + BuildStep = Step.WhereClause; BuildWhereClause (sb); + BuildStep = Step.GroupByClause; BuildGroupByClause(sb); + BuildStep = Step.HavingClause; BuildHavingClause (sb); + BuildStep = Step.OrderByClause; BuildOrderByClause(sb); + BuildStep = Step.OffsetLimit; BuildOffsetLimit (sb); + } + + protected virtual void BuildUpdateQuery(StringBuilder sb) + { + BuildStep = Step.UpdateClause; BuildUpdateClause (sb); + BuildStep = Step.FromClause; BuildFromClause (sb); + BuildStep = Step.WhereClause; BuildWhereClause (sb); + BuildStep = Step.GroupByClause; BuildGroupByClause(sb); + BuildStep = Step.HavingClause; BuildHavingClause (sb); + BuildStep = Step.OrderByClause; BuildOrderByClause(sb); + BuildStep = Step.OffsetLimit; BuildOffsetLimit (sb); + } + + protected virtual void BuildSelectQuery(StringBuilder sb) + { + BuildStep = Step.SelectClause; BuildSelectClause (sb); + BuildStep = Step.FromClause; BuildFromClause (sb); + BuildStep = Step.WhereClause; BuildWhereClause (sb); + BuildStep = Step.GroupByClause; BuildGroupByClause(sb); + BuildStep = Step.HavingClause; BuildHavingClause (sb); + BuildStep = Step.OrderByClause; BuildOrderByClause(sb); + BuildStep = Step.OffsetLimit; BuildOffsetLimit (sb); + } + + protected virtual void BuildInsertQuery(StringBuilder sb) + { + BuildStep = Step.InsertClause; BuildInsertClause(sb); + + if (SqlQuery.QueryType == QueryType.Insert && SqlQuery.From.Tables.Count != 0) + { + BuildStep = Step.SelectClause; BuildSelectClause (sb); + BuildStep = Step.FromClause; BuildFromClause (sb); + BuildStep = Step.WhereClause; BuildWhereClause (sb); + BuildStep = Step.GroupByClause; BuildGroupByClause(sb); + BuildStep = Step.HavingClause; BuildHavingClause (sb); + BuildStep = Step.OrderByClause; BuildOrderByClause(sb); + BuildStep = Step.OffsetLimit; BuildOffsetLimit (sb); + } + + if (SqlQuery.Insert.WithIdentity) + BuildGetIdentity(sb); + } + + protected virtual void BuildUnknownQuery(StringBuilder sb) + { + throw new SqlException("Unknown query type '{0}'.", SqlQuery.QueryType); + } + + public virtual StringBuilder BuildTableName(StringBuilder sb, string database, string owner, string table) + { + if (database != null) + { + if (owner == null) sb.Append(database).Append(".."); + else sb.Append(database).Append(".").Append(owner).Append("."); + } + else if (owner != null) sb.Append(owner).Append("."); + + return sb.Append(table); + } + + public virtual object Convert(object value, ConvertType convertType) + { + return value; + } + + #endregion + + #region Build Select + + protected virtual void BuildSelectClause(StringBuilder sb) + { + AppendIndent(sb); + sb.Append("SELECT"); + + if (SqlQuery.Select.IsDistinct) + sb.Append(" DISTINCT"); + + BuildSkipFirst(sb); + + sb.AppendLine(); + BuildColumns(sb); + } + + protected virtual IEnumerable<SqlQuery.Column> GetSelectedColumns() + { + return SqlQuery.Select.Columns; + } + + protected virtual void BuildColumns(StringBuilder sb) + { + Indent++; + + var first = true; + + foreach (var col in GetSelectedColumns()) + { + if (!first) + sb.Append(',').AppendLine(); + first = false; + + var addAlias = true; + + AppendIndent(sb); + BuildColumnExpression(sb, col.Expression, col.Alias, ref addAlias); + + if (!_skipAlias && addAlias && col.Alias != null) + sb.Append(" as ").Append(Convert(col.Alias, ConvertType.NameToQueryFieldAlias)); + } + + if (first) + AppendIndent(sb).Append("*"); + + Indent--; + + sb.AppendLine(); + } + +// protected virtual void BuildColumn(StringBuilder sb, SqlQuery.Column col, ref bool addAlias) +// { +// BuildExpression(sb, col.Expression, true, true, col.Alias, ref addAlias); +// } + + protected virtual void BuildColumnExpression(StringBuilder sb, ISqlExpression expr, string alias, ref bool addAlias) + { + BuildExpression(sb, expr, true, true, alias, ref addAlias); + } + + #endregion + + #region Build Delete + + protected virtual void BuildDeleteClause(StringBuilder sb) + { + AppendIndent(sb); + sb.Append("DELETE "); + } + + #endregion + + #region Build Update + + protected virtual void BuildUpdateClause(StringBuilder sb) + { + BuildUpdateTable(sb); + BuildUpdateSet (sb); + } + + protected virtual void BuildUpdateTable(StringBuilder sb) + { + AppendIndent(sb) + .AppendLine("UPDATE") + .Append('\t'); + BuildUpdateTableName(sb); + sb.AppendLine(); + } + + protected virtual void BuildUpdateTableName(StringBuilder sb) + { + if (SqlQuery.Update.Table != null && SqlQuery.Update.Table != SqlQuery.From.Tables[0].Source) + BuildPhysicalTable(sb, SqlQuery.Update.Table, null); + else + BuildTableName(sb, SqlQuery.From.Tables[0], true, true); + } + + protected virtual void BuildUpdateSet(StringBuilder sb) + { + AppendIndent(sb) + .AppendLine("SET"); + + Indent++; + + var first = true; + + foreach (var expr in SqlQuery.Update.Items) + { + if (!first) + sb.Append(',').AppendLine(); + first = false; + + AppendIndent(sb); + BuildExpression(sb, expr.Column, false, true); + sb.Append(" = "); + + var addAlias = false; + + BuildColumnExpression(sb, expr.Expression, null, ref addAlias); + } + + Indent--; + + sb.AppendLine(); + } + + #endregion + + #region Build Insert + + protected void BuildInsertClause(StringBuilder sb) + { + BuildInsertClause(sb, "INSERT INTO ", true); + } + + protected virtual void BuildEmptyInsert(StringBuilder sb) + { + sb.AppendLine("DEFAULT VALUES"); + } + + protected virtual void BuildInsertClause(StringBuilder sb, string insertText, bool appendTableName) + { + AppendIndent(sb).Append(insertText); + + if (appendTableName) + BuildPhysicalTable(sb, SqlQuery.Insert.Into, null); + + if (SqlQuery.Insert.Items.Count == 0) + { + sb.Append(' '); + BuildEmptyInsert(sb); + } + else + { + sb.AppendLine(); + + AppendIndent(sb).AppendLine("("); + + Indent++; + + var first = true; + + foreach (var expr in SqlQuery.Insert.Items) + { + if (!first) + sb.Append(',').AppendLine(); + first = false; + + AppendIndent(sb); + BuildExpression(sb, expr.Column, false, true); + } + + Indent--; + + sb.AppendLine(); + AppendIndent(sb).AppendLine(")"); + + if (SqlQuery.QueryType == QueryType.InsertOrUpdate || SqlQuery.From.Tables.Count == 0) + { + AppendIndent(sb).AppendLine("VALUES"); + AppendIndent(sb).AppendLine("("); + + Indent++; + + first = true; + + foreach (var expr in SqlQuery.Insert.Items) + { + if (!first) + sb.Append(',').AppendLine(); + first = false; + + AppendIndent(sb); + BuildExpression(sb, expr.Expression); + } + + Indent--; + + sb.AppendLine(); + AppendIndent(sb).AppendLine(")"); + } + } + } + + protected virtual void BuildGetIdentity(StringBuilder sb) + { + //throw new SqlException("Insert with identity is not supported by the '{0}' sql provider.", Name); + } + + #endregion + + #region Build InsertOrUpdate + + protected virtual void BuildInsertOrUpdateQuery(StringBuilder sb) + { + throw new SqlException("InsertOrUpdate query type is not supported by {0} provider.", Name); + } + + protected void BuildInsertOrUpdateQueryAsMerge(StringBuilder sb, string fromDummyTable) + { + var table = SqlQuery.Insert.Into; + var targetAlias = Convert(SqlQuery.From.Tables[0].Alias, ConvertType.NameToQueryTableAlias).ToString(); + var sourceAlias = Convert(GetTempAliases(1, "s")[0], ConvertType.NameToQueryTableAlias).ToString(); + var keys = SqlQuery.Update.Keys; + + AppendIndent(sb).Append("MERGE INTO "); + BuildPhysicalTable(sb, table, null); + sb.Append(' ').AppendLine(targetAlias); + + AppendIndent(sb).Append("USING (SELECT "); + + for (var i = 0; i < keys.Count; i++) + { + BuildExpression(sb, keys[i].Expression, false, false); + sb.Append(" AS "); + BuildExpression(sb, keys[i].Column, false, false); + + if (i + 1 < keys.Count) + sb.Append(", "); + } + + if (!string.IsNullOrEmpty(fromDummyTable)) + sb.Append(' ').Append(fromDummyTable); + + sb.Append(") ").Append(sourceAlias).AppendLine(" ON"); + + AppendIndent(sb).AppendLine("("); + + Indent++; + + for (var i = 0; i < keys.Count; i++) + { + var key = keys[i]; + + AppendIndent(sb); + + sb.Append(targetAlias).Append('.'); + BuildExpression(sb, key.Column, false, false); + + sb.Append(" = ").Append(sourceAlias).Append('.'); + BuildExpression(sb, key.Column, false, false); + + if (i + 1 < keys.Count) + sb.Append(" AND"); + + sb.AppendLine(); + } + + Indent--; + + AppendIndent(sb).AppendLine(")"); + AppendIndent(sb).AppendLine("WHEN MATCHED THEN"); + + Indent++; + AppendIndent(sb).AppendLine("UPDATE "); + BuildUpdateSet(sb); + Indent--; + + AppendIndent(sb).AppendLine("WHEN NOT MATCHED THEN"); + + Indent++; + BuildInsertClause(sb, "INSERT", false); + Indent--; + + while (_endLine.Contains(sb[sb.Length - 1])) + sb.Length--; + } + + static readonly char[] _endLine = new[] { ' ', '\r', '\n' }; + + protected void BuildInsertOrUpdateQueryAsUpdateInsert(StringBuilder sb) + { + AppendIndent(sb).AppendLine("BEGIN TRAN").AppendLine(); + + BuildUpdateQuery(sb); + + AppendIndent(sb).AppendLine("WHERE"); + + var alias = Convert(SqlQuery.From.Tables[0].Alias, ConvertType.NameToQueryTableAlias).ToString(); + var exprs = SqlQuery.Update.Keys; + + Indent++; + + for (var i = 0; i < exprs.Count; i++) + { + var expr = exprs[i]; + + AppendIndent(sb); + + sb.Append(alias).Append('.'); + BuildExpression(sb, expr.Column, false, false); + + sb.Append(" = "); + BuildExpression(sb, Precedence.Comparison, expr.Expression); + + if (i + 1 < exprs.Count) + sb.Append(" AND"); + + sb.AppendLine(); + } + + Indent--; + + sb.AppendLine(); + AppendIndent(sb).AppendLine("IF @@ROWCOUNT = 0"); + AppendIndent(sb).AppendLine("BEGIN"); + + Indent++; + + BuildInsertQuery(sb); + + Indent--; + + AppendIndent(sb).AppendLine("END"); + + sb.AppendLine(); + AppendIndent(sb).AppendLine("COMMIT"); + } + + #endregion + + #region Build From + + protected virtual void BuildFromClause(StringBuilder sb) + { + if (SqlQuery.From.Tables.Count == 0) + return; + + AppendIndent(sb); + + sb.Append("FROM").AppendLine(); + + Indent++; + AppendIndent(sb); + + var first = true; + + foreach (var ts in SqlQuery.From.Tables) + { + if (!first) + { + sb.AppendLine(","); + AppendIndent(sb); + } + + first = false; + + var jn = ParenthesizeJoin() ? ts.GetJoinNumber() : 0; + + if (jn > 0) + { + jn--; + for (var i = 0; i < jn; i++) + sb.Append("("); + } + + BuildTableName(sb, ts, true, true); + + foreach (var jt in ts.Joins) + BuildJoinTable(sb, jt, ref jn); + } + + Indent--; + + sb.AppendLine(); + } + + protected void BuildPhysicalTable(StringBuilder sb, ISqlTableSource table, string alias) + { + switch (table.ElementType) + { + case QueryElementType.SqlTable : + case QueryElementType.TableSource : + sb.Append(GetTablePhysicalName(table, alias)); + break; + + case QueryElementType.SqlQuery : + sb.Append("(").AppendLine(); + _nextNesting = BuildSqlBuilder((SqlQuery)table, sb, Indent + 1, _nextNesting, false); + AppendIndent(sb).Append(")"); + + break; + + default: + throw new InvalidOperationException(); + } + } + + protected void BuildTableName(StringBuilder sb, SqlQuery.TableSource ts, bool buildName, bool buildAlias) + { + if (buildName) + { + var alias = GetTableAlias(ts); + BuildPhysicalTable(sb, ts.Source, alias); + } + + if (buildAlias) + { + if (ts.SqlTableType != SqlTableType.Expression) + { + var alias = GetTableAlias(ts); + + if (!string.IsNullOrEmpty(alias)) + { + if (buildName) + sb.Append(" "); + sb.Append(Convert(alias, ConvertType.NameToQueryTableAlias)); + } + + } + } + } + + void BuildJoinTable(StringBuilder sb, SqlQuery.JoinedTable join, ref int joinCounter) + { + sb.AppendLine(); + Indent++; + AppendIndent(sb); + + var buildOn = BuildJoinType(sb, join); + + if (IsNestedJoinParenthesisRequired && join.Table.Joins.Count != 0) + sb.Append('('); + + BuildTableName(sb, join.Table, true, true); + + if (IsNestedJoinSupported && join.Table.Joins.Count != 0) + { + foreach (var jt in join.Table.Joins) + BuildJoinTable(sb, jt, ref joinCounter); + + if (IsNestedJoinParenthesisRequired && join.Table.Joins.Count != 0) + sb.Append(')'); + + if (buildOn) + { + sb.AppendLine(); + AppendIndent(sb); + sb.Append("ON "); + } + } + else if (buildOn) + sb.Append(" ON "); + + if (buildOn) + { + if (join.Condition.Conditions.Count != 0) + BuildSearchCondition(sb, Precedence.Unknown, join.Condition); + else + sb.Append("1=1"); + } + + if (joinCounter > 0) + { + joinCounter--; + sb.Append(")"); + } + + if (!IsNestedJoinSupported) + foreach (var jt in join.Table.Joins) + BuildJoinTable(sb, jt, ref joinCounter); + + Indent--; + } + + protected virtual bool BuildJoinType(StringBuilder sb, SqlQuery.JoinedTable join) + { + switch (join.JoinType) + { + case SqlQuery.JoinType.Inner : sb.Append("INNER JOIN "); return true; + case SqlQuery.JoinType.Left : sb.Append("LEFT JOIN "); return true; + case SqlQuery.JoinType.CrossApply : sb.Append("CROSS APPLY "); return false; + case SqlQuery.JoinType.OuterApply : sb.Append("OUTER APPLY "); return false; + default: throw new InvalidOperationException(); + } + } + + #endregion + + #region Where Clause + + protected virtual bool BuildWhere() + { + return SqlQuery.Where.SearchCondition.Conditions.Count != 0; + } + + protected virtual void BuildWhereClause(StringBuilder sb) + { + if (!BuildWhere()) + return; + + AppendIndent(sb); + + sb.Append("WHERE").AppendLine(); + + Indent++; + AppendIndent(sb); + BuildWhereSearchCondition(sb, SqlQuery.Where.SearchCondition); + Indent--; + + sb.AppendLine(); + } + + #endregion + + #region GroupBy Clause + + protected virtual void BuildGroupByClause(StringBuilder sb) + { + if (SqlQuery.GroupBy.Items.Count == 0) + return; + + AppendIndent(sb); + + sb.Append("GROUP BY").AppendLine(); + + Indent++; + + for (var i = 0; i < SqlQuery.GroupBy.Items.Count; i++) + { + AppendIndent(sb); + + BuildExpression(sb, SqlQuery.GroupBy.Items[i]); + + if (i + 1 < SqlQuery.GroupBy.Items.Count) + sb.Append(','); + + sb.AppendLine(); + } + + Indent--; + } + + #endregion + + #region Having Clause + + protected virtual void BuildHavingClause(StringBuilder sb) + { + if (SqlQuery.Having.SearchCondition.Conditions.Count == 0) + return; + + AppendIndent(sb); + + sb.Append("HAVING").AppendLine(); + + Indent++; + AppendIndent(sb); + BuildWhereSearchCondition(sb, SqlQuery.Having.SearchCondition); + Indent--; + + sb.AppendLine(); + } + + #endregion + + #region OrderBy Clause + + protected virtual void BuildOrderByClause(StringBuilder sb) + { + if (SqlQuery.OrderBy.Items.Count == 0) + return; + + AppendIndent(sb); + + sb.Append("ORDER BY").AppendLine(); + + Indent++; + + for (var i = 0; i < SqlQuery.OrderBy.Items.Count; i++) + { + AppendIndent(sb); + + var item = SqlQuery.OrderBy.Items[i]; + + BuildExpression(sb, item.Expression); + + if (item.IsDescending) + sb.Append(" DESC"); + + if (i + 1 < SqlQuery.OrderBy.Items.Count) + sb.Append(','); + + sb.AppendLine(); + } + + Indent--; + } + + #endregion + + #region Skip/Take + + protected virtual bool SkipFirst { get { return true; } } + protected virtual string SkipFormat { get { return null; } } + protected virtual string FirstFormat { get { return null; } } + protected virtual string LimitFormat { get { return null; } } + protected virtual string OffsetFormat { get { return null; } } + protected virtual bool OffsetFirst { get { return false; } } + + protected bool NeedSkip { get { return SqlQuery.Select.SkipValue != null && IsSkipSupported; } } + protected bool NeedTake { get { return SqlQuery.Select.TakeValue != null && IsTakeSupported; } } + + protected virtual void BuildSkipFirst(StringBuilder sb) + { + if (SkipFirst && NeedSkip && SkipFormat != null) + sb.Append(' ').AppendFormat(SkipFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.SkipValue)); + + if (NeedTake && FirstFormat != null) + sb.Append(' ').AppendFormat(FirstFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.TakeValue)); + + if (!SkipFirst && NeedSkip && SkipFormat != null) + sb.Append(' ').AppendFormat(SkipFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.SkipValue)); + } + + protected virtual void BuildOffsetLimit(StringBuilder sb) + { + var doSkip = NeedSkip && OffsetFormat != null; + var doTake = NeedTake && LimitFormat != null; + + if (doSkip || doTake) + { + AppendIndent(sb); + + if (doSkip && OffsetFirst) + { + sb.AppendFormat(OffsetFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.SkipValue)); + + if (doTake) + sb.Append(' '); + } + + if (doTake) + { + sb.AppendFormat(LimitFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.TakeValue)); + + if (doSkip) + sb.Append(' '); + } + + if (doSkip && !OffsetFirst) + sb.AppendFormat(OffsetFormat, BuildExpression(new StringBuilder(), SqlQuery.Select.SkipValue)); + + sb.AppendLine(); + } + } + + #endregion + + #region Builders + + #region BuildSearchCondition + + protected virtual void BuildWhereSearchCondition(StringBuilder sb, SqlQuery.SearchCondition condition) + { + BuildSearchCondition(sb, Precedence.Unknown, condition); + } + + protected virtual void BuildSearchCondition(StringBuilder sb, SqlQuery.SearchCondition condition) + { + var isOr = (bool?)null; + var len = sb.Length; + var parentPrecedence = condition.Precedence + 1; + + foreach (var cond in condition.Conditions) + { + if (isOr != null) + { + sb.Append(isOr.Value ? " OR" : " AND"); + + if (condition.Conditions.Count < 4 && sb.Length - len < 50 || condition != SqlQuery.Where.SearchCondition) + { + sb.Append(' '); + } + else + { + sb.AppendLine(); + AppendIndent(sb); + len = sb.Length; + } + } + + if (cond.IsNot) + sb.Append("NOT "); + + var precedence = GetPrecedence(cond.Predicate); + + BuildPredicate(sb, cond.IsNot ? Precedence.LogicalNegation : parentPrecedence, precedence, cond.Predicate); + + isOr = cond.IsOr; + } + } + + protected virtual void BuildSearchCondition(StringBuilder sb, int parentPrecedence, SqlQuery.SearchCondition condition) + { + var wrap = Wrap(GetPrecedence(condition as ISqlExpression), parentPrecedence); + + if (wrap) sb.Append('('); + BuildSearchCondition(sb, condition); + if (wrap) sb.Append(')'); + } + + #endregion + + #region BuildPredicate + + protected virtual void BuildPredicate(StringBuilder sb, ISqlPredicate predicate) + { + switch (predicate.ElementType) + { + case QueryElementType.ExprExprPredicate : + { + var expr = (SqlQuery.Predicate.ExprExpr)predicate; + + switch (expr.Operator) + { + case SqlQuery.Predicate.Operator.Equal : + case SqlQuery.Predicate.Operator.NotEqual : + { + ISqlExpression e = null; + + if (expr.Expr1 is SqlValueBase && ((SqlValueBase)expr.Expr1).Value == null) + e = expr.Expr2; + else if (expr.Expr2 is SqlValueBase && ((SqlValueBase)expr.Expr2).Value == null) + e = expr.Expr1; + + if (e != null) + { + BuildExpression(sb, GetPrecedence(expr), e); + sb.Append(expr.Operator == SqlQuery.Predicate.Operator.Equal ? " IS NULL" : " IS NOT NULL"); + return; + } + + break; + } + } + + BuildExpression(sb, GetPrecedence(expr), expr.Expr1); + + switch (expr.Operator) + { + case SqlQuery.Predicate.Operator.Equal : sb.Append(" = "); break; + case SqlQuery.Predicate.Operator.NotEqual : sb.Append(" <> "); break; + case SqlQuery.Predicate.Operator.Greater : sb.Append(" > "); break; + case SqlQuery.Predicate.Operator.GreaterOrEqual : sb.Append(" >= "); break; + case SqlQuery.Predicate.Operator.NotGreater : sb.Append(" !> "); break; + case SqlQuery.Predicate.Operator.Less : sb.Append(" < "); break; + case SqlQuery.Predicate.Operator.LessOrEqual : sb.Append(" <= "); break; + case SqlQuery.Predicate.Operator.NotLess : sb.Append(" !< "); break; + } + + BuildExpression(sb, GetPrecedence(expr), expr.Expr2); + } + + break; + + case QueryElementType.LikePredicate : + BuildLikePredicate(sb, (SqlQuery.Predicate.Like)predicate); + break; + + case QueryElementType.BetweenPredicate : + { + var p = (SqlQuery.Predicate.Between)predicate; + BuildExpression(sb, GetPrecedence(p), p.Expr1); + if (p.IsNot) sb.Append(" NOT"); + sb.Append(" BETWEEN "); + BuildExpression(sb, GetPrecedence(p), p.Expr2); + sb.Append(" AND "); + BuildExpression(sb, GetPrecedence(p), p.Expr3); + } + + break; + + case QueryElementType.IsNullPredicate : + { + var p = (SqlQuery.Predicate.IsNull)predicate; + BuildExpression(sb, GetPrecedence(p), p.Expr1); + sb.Append(p.IsNot ? " IS NOT NULL" : " IS NULL"); + } + + break; + + case QueryElementType.InSubQueryPredicate : + { + var p = (SqlQuery.Predicate.InSubQuery)predicate; + BuildExpression(sb, GetPrecedence(p), p.Expr1); + sb.Append(p.IsNot ? " NOT IN " : " IN "); + BuildExpression(sb, GetPrecedence(p), p.SubQuery); + } + + break; + + case QueryElementType.InListPredicate : + BuildInListPredicate(predicate, sb); + break; + + case QueryElementType.FuncLikePredicate : + { + var f = (SqlQuery.Predicate.FuncLike)predicate; + BuildExpression(sb, f.Function.Precedence, f.Function); + } + + break; + + case QueryElementType.SearchCondition : + BuildSearchCondition(sb, predicate.Precedence, (SqlQuery.SearchCondition)predicate); + break; + + case QueryElementType.NotExprPredicate : + { + var p = (SqlQuery.Predicate.NotExpr)predicate; + + if (p.IsNot) + sb.Append("NOT "); + + BuildExpression(sb, p.IsNot ? Precedence.LogicalNegation : GetPrecedence(p), p.Expr1); + } + + break; + + case QueryElementType.ExprPredicate : + { + var p = (SqlQuery.Predicate.Expr)predicate; + + if (p.Expr1 is SqlValue) + { + var value = ((SqlValue)p.Expr1).Value; + + if (value is bool) + { + sb.Append((bool)value ? "1 = 1" : "1 = 0"); + return; + } + } + + BuildExpression(sb, GetPrecedence(p), p.Expr1); + } + + break; + + default : + throw new InvalidOperationException(); + } + } + + static SqlField GetUnderlayingField(ISqlExpression expr) + { + switch (expr.ElementType) + { + case QueryElementType.SqlField: return (SqlField)expr; + case QueryElementType.Column : return GetUnderlayingField(((SqlQuery.Column)expr).Expression); + } + + throw new InvalidOperationException(); + } + + void BuildInListPredicate(ISqlPredicate predicate, StringBuilder sb) + { + var p = (SqlQuery.Predicate.InList)predicate; + + if (p.Values == null || p.Values.Count == 0) + { + BuildPredicate(sb, new SqlQuery.Predicate.Expr(new SqlValue(false))); + } + else + { + ICollection values = p.Values; + + if (p.Values.Count == 1 && p.Values[0] is SqlParameter && + !(p.Expr1.SystemType == typeof(string) && ((SqlParameter)p.Values[0]).Value is string)) + { + var pr = (SqlParameter)p.Values[0]; + + if (pr.Value == null) + { + BuildPredicate(sb, new SqlQuery.Predicate.Expr(new SqlValue(false))); + return; + } + + if (pr.Value is IEnumerable) + { + var items = (IEnumerable)pr.Value; + + if (p.Expr1 is ISqlTableSource) + { + var firstValue = true; + var table = (ISqlTableSource)p.Expr1; + var keys = table.GetKeys(true); + + if (keys == null || keys.Count == 0) + throw new SqlException("Cannot create IN expression."); + + if (keys.Count == 1) + { + foreach (var item in items) + { + if (firstValue) + { + firstValue = false; + BuildExpression(sb, GetPrecedence(p), keys[0]); + sb.Append(p.IsNot ? " NOT IN (" : " IN ("); + } + + var field = GetUnderlayingField(keys[0]); + var value = field.MemberMapper.GetValue(item); + + if (value is ISqlExpression) + BuildExpression(sb, (ISqlExpression)value); + else + BuildValue(sb, value); + + sb.Append(", "); + } + } + else + { + var len = sb.Length; + var rem = 1; + + foreach (var item in items) + { + if (firstValue) + { + firstValue = false; + sb.Append('('); + } + + foreach (var key in keys) + { + var field = GetUnderlayingField(key); + var value = field.MemberMapper.GetValue(item); + + BuildExpression(sb, GetPrecedence(p), key); + + if (value == null) + { + sb.Append(" IS NULL"); + } + else + { + sb.Append(" = "); + BuildValue(sb, value); + } + + sb.Append(" AND "); + } + + sb.Remove(sb.Length - 4, 4).Append("OR "); + + if (sb.Length - len >= 50) + { + sb.AppendLine(); + AppendIndent(sb); + sb.Append(' '); + len = sb.Length; + rem = 5 + Indent; + } + } + + if (!firstValue) + sb.Remove(sb.Length - rem, rem); + } + + if (firstValue) + BuildPredicate(sb, new SqlQuery.Predicate.Expr(new SqlValue(p.IsNot))); + else + sb.Remove(sb.Length - 2, 2).Append(')'); + } + else + { + BuildInListValues(sb, p, items); + } + + return; + } + } + + BuildInListValues(sb, p, values); + } + } + + void BuildInListValues(StringBuilder sb, SqlQuery.Predicate.InList predicate, IEnumerable values) + { + var firstValue = true; + var len = sb.Length; + var hasNull = false; + var count = 0; + var longList = false; + + foreach (var value in values) + { + if (count++ >= MaxInListValuesCount) + { + count = 1; + longList = true; + + // start building next bucked + firstValue = true; + sb.Remove(sb.Length - 2, 2).Append(')'); + sb.Append(" OR "); + } + + var val = value; + + if (val is IValueContainer) + val = ((IValueContainer)value).Value; + + if (val == null) + { + hasNull = true; + continue; + } + + if (firstValue) + { + firstValue = false; + BuildExpression(sb, GetPrecedence(predicate), predicate.Expr1); + sb.Append(predicate.IsNot ? " NOT IN (" : " IN ("); + } + + if (value is ISqlExpression) + BuildExpression(sb, (ISqlExpression)value); + else + BuildValue(sb, value); + + sb.Append(", "); + } + + if (firstValue) + { + BuildPredicate(sb, + hasNull ? + new SqlQuery.Predicate.IsNull(predicate.Expr1, predicate.IsNot) : + new SqlQuery.Predicate.Expr(new SqlValue(predicate.IsNot))); + } + else + { + sb.Remove(sb.Length - 2, 2).Append(')'); + + if (hasNull) + { + sb.Insert(len, "("); + sb.Append(" OR "); + BuildPredicate(sb, new SqlQuery.Predicate.IsNull(predicate.Expr1, predicate.IsNot)); + sb.Append(")"); + } + } + + if (longList && !hasNull) + { + sb.Insert(len, "("); + sb.Append(")"); + } + } + + protected void BuildPredicate(StringBuilder sb, int parentPrecedence, ISqlPredicate predicate) + { + BuildPredicate(sb, parentPrecedence, GetPrecedence(predicate), predicate); + } + + protected void BuildPredicate(StringBuilder sb, int parentPrecedence, int precedence, ISqlPredicate predicate) + { + var wrap = Wrap(precedence, parentPrecedence); + + if (wrap) sb.Append('('); + BuildPredicate(sb, predicate); + if (wrap) sb.Append(')'); + } + + protected virtual void BuildLikePredicate(StringBuilder sb, SqlQuery.Predicate.Like predicate) + { + var precedence = GetPrecedence(predicate); + + BuildExpression(sb, precedence, predicate.Expr1); + sb.Append(predicate.IsNot? " NOT LIKE ": " LIKE "); + BuildExpression(sb, precedence, predicate.Expr2); + + if (predicate.Escape != null) + { + sb.Append(" ESCAPE "); + BuildExpression(sb, predicate.Escape); + } + } + + #endregion + + #region BuildExpression + + protected virtual StringBuilder BuildExpression( + StringBuilder sb, + ISqlExpression expr, + bool buildTableName, + bool checkParentheses, + string alias, + ref bool addAlias) + { + expr = ConvertExpression(expr); + + switch (expr.ElementType) + { + case QueryElementType.SqlField: + { + var field = (SqlField)expr; + + if (field == field.Table.All) + { + sb.Append("*"); + } + else + { + if (buildTableName) + { + var ts = SqlQuery.GetTableSource(field.Table); + + if (ts == null) + { +#if DEBUG + SqlQuery.GetTableSource(field.Table); +#endif + + throw new SqlException(string.Format("Table '{0}' not found.", field.Table)); + } + + var table = GetTableAlias(ts); + + table = table == null ? + GetTablePhysicalName(field.Table, null) : + Convert(table, ConvertType.NameToQueryTableAlias).ToString(); + + if (string.IsNullOrEmpty(table)) + throw new SqlException(string.Format("Table {0} should have an alias.", field.Table)); + + addAlias = alias != field.PhysicalName; + + sb + .Append(table) + .Append('.'); + } + + sb.Append(Convert(field.PhysicalName, ConvertType.NameToQueryField)); + } + } + + break; + + case QueryElementType.Column: + { + var column = (SqlQuery.Column)expr; + +#if DEBUG + //if (column.ToString() == "t8.ParentID") + //{ + // column.ToString(); + //} + + var sql = SqlQuery.SqlText; +#endif + + var table = SqlQuery.GetTableSource(column.Parent); + + if (table == null) + { +#if DEBUG + table = SqlQuery.GetTableSource(column.Parent); +#endif + + throw new SqlException(string.Format("Table not found for '{0}'.", column)); + } + + var tableAlias = GetTableAlias(table) ?? GetTablePhysicalName(column.Parent, null); + + if (string.IsNullOrEmpty(tableAlias)) + throw new SqlException(string.Format("Table {0} should have an alias.", column.Parent)); + + addAlias = alias != column.Alias; + + sb + .Append(Convert(tableAlias, ConvertType.NameToQueryTableAlias)) + .Append('.') + .Append(Convert(column.Alias, ConvertType.NameToQueryField)); + } + + break; + + case QueryElementType.SqlQuery: + { + var hasParentheses = checkParentheses && sb[sb.Length - 1] == '('; + + if (!hasParentheses) + sb.Append("("); + sb.AppendLine(); + + _nextNesting = BuildSqlBuilder((SqlQuery)expr, sb, Indent + 1, _nextNesting, BuildStep != Step.FromClause); + + AppendIndent(sb); + + if (!hasParentheses) + sb.Append(")"); + } + + break; + + case QueryElementType.SqlValue: + BuildValue(sb, ((SqlValue)expr).Value); + break; + + case QueryElementType.SqlExpression: + { + var e = (SqlExpression)expr; + var s = new StringBuilder(); + + if (e.Parameters == null || e.Parameters.Length == 0) + sb.Append(e.Expr); + else + { + var values = new object[e.Parameters.Length]; + + for (var i = 0; i < values.Length; i++) + { + var value = e.Parameters[i]; + + s.Length = 0; + BuildExpression(s, GetPrecedence(e), value); + values[i] = s.ToString(); + } + + sb.AppendFormat(e.Expr, values); + } + } + + break; + + case QueryElementType.SqlBinaryExpression: + BuildBinaryExpression(sb, (SqlBinaryExpression)expr); + break; + + case QueryElementType.SqlFunction: + BuildFunction(sb, (SqlFunction)expr); + break; + + case QueryElementType.SqlParameter: + { + var parm = (SqlParameter)expr; + + if (parm.IsQueryParameter) + { + var name = Convert(parm.Name, ConvertType.NameToQueryParameter); + sb.Append(name); + } + else + BuildValue(sb, parm.Value); + } + + break; + + case QueryElementType.SqlDataType: + BuildDataType(sb, (SqlDataType)expr); + break; + + case QueryElementType.SearchCondition: + BuildSearchCondition(sb, expr.Precedence, (SqlQuery.SearchCondition)expr); + break; + + default: + throw new InvalidOperationException(); + } + + return sb; + } + + protected void BuildExpression(StringBuilder sb, int parentPrecedence, ISqlExpression expr, string alias, ref bool addAlias) + { + var wrap = Wrap(GetPrecedence(expr), parentPrecedence); + + if (wrap) sb.Append('('); + BuildExpression(sb, expr, true, true, alias, ref addAlias); + if (wrap) sb.Append(')'); + } + + protected StringBuilder BuildExpression(StringBuilder sb, ISqlExpression expr) + { + var dummy = false; + return BuildExpression(sb, expr, true, true, null, ref dummy); + } + + protected StringBuilder BuildExpression(StringBuilder sb, ISqlExpression expr, bool buildTableName, bool checkParentheses) + { + var dummy = false; + return BuildExpression(sb, expr, buildTableName, checkParentheses, null, ref dummy); + } + + protected void BuildExpression(StringBuilder sb, int precedence, ISqlExpression expr) + { + var dummy = false; + BuildExpression(sb, precedence, expr, null, ref dummy); + } + + #endregion + + #region BuildValue + + interface INullableValueReader + { + object GetValue(object value); + } + + class NullableValueReader<T> : INullableValueReader where T : struct + { + public object GetValue(object value) + { + return ((T?)value).Value; + } + } + + static readonly Dictionary<Type,INullableValueReader> _nullableValueReader = new Dictionary<Type,INullableValueReader>(); + + public NumberFormatInfo NumberFormatInfo = new NumberFormatInfo + { + CurrencyDecimalDigits = NumberFormatInfo.InvariantInfo.CurrencyDecimalDigits, + CurrencyDecimalSeparator = NumberFormatInfo.InvariantInfo.CurrencyDecimalSeparator, + CurrencyGroupSeparator = NumberFormatInfo.InvariantInfo.CurrencyGroupSeparator, + CurrencyGroupSizes = NumberFormatInfo.InvariantInfo.CurrencyGroupSizes, + CurrencyNegativePattern = NumberFormatInfo.InvariantInfo.CurrencyNegativePattern, + CurrencyPositivePattern = NumberFormatInfo.InvariantInfo.CurrencyPositivePattern, + CurrencySymbol = NumberFormatInfo.InvariantInfo.CurrencySymbol, + NaNSymbol = NumberFormatInfo.InvariantInfo.NaNSymbol, + NegativeInfinitySymbol = NumberFormatInfo.InvariantInfo.NegativeInfinitySymbol, + NegativeSign = NumberFormatInfo.InvariantInfo.NegativeSign, + NumberDecimalDigits = NumberFormatInfo.InvariantInfo.NumberDecimalDigits, + NumberDecimalSeparator = ".", + NumberGroupSeparator = NumberFormatInfo.InvariantInfo.NumberGroupSeparator, + NumberGroupSizes = NumberFormatInfo.InvariantInfo.NumberGroupSizes, + NumberNegativePattern = NumberFormatInfo.InvariantInfo.NumberNegativePattern, + PercentDecimalDigits = NumberFormatInfo.InvariantInfo.PercentDecimalDigits, + PercentDecimalSeparator = ".", + PercentGroupSeparator = NumberFormatInfo.InvariantInfo.PercentGroupSeparator, + PercentGroupSizes = NumberFormatInfo.InvariantInfo.PercentGroupSizes, + PercentNegativePattern = NumberFormatInfo.InvariantInfo.PercentNegativePattern, + PercentPositivePattern = NumberFormatInfo.InvariantInfo.PercentPositivePattern, + PercentSymbol = NumberFormatInfo.InvariantInfo.PercentSymbol, + PerMilleSymbol = NumberFormatInfo.InvariantInfo.PerMilleSymbol, + PositiveInfinitySymbol = NumberFormatInfo.InvariantInfo.PositiveInfinitySymbol, + PositiveSign = NumberFormatInfo.InvariantInfo.PositiveSign, + }; + + public virtual void BuildValue(StringBuilder sb, object value) + { + if (value == null) sb.Append("NULL"); + else if (value is string) BuildString(sb, value.ToString()); + else if (value is char) BuildChar (sb, (char)value); + else if (value is bool) sb.Append((bool)value ? "1" : "0"); + else if (value is DateTime) BuildDateTime(sb, value); + else if (value is Guid) sb.Append('\'').Append(value).Append('\''); + else if (value is decimal) sb.Append(((decimal)value).ToString(NumberFormatInfo)); + else if (value is double) sb.Append(((double) value).ToString(NumberFormatInfo)); + else if (value is float) sb.Append(((float) value).ToString(NumberFormatInfo)); + else + { + var type = value.GetType(); + + if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>)) + { + type = type.GetGenericArguments()[0]; + + if (type.IsEnum) + { + lock (_nullableValueReader) + { + INullableValueReader reader; + + if (_nullableValueReader.TryGetValue(type, out reader) == false) + { + reader = (INullableValueReader)Activator.CreateInstance(typeof(NullableValueReader<>).MakeGenericType(type)); + _nullableValueReader.Add(type, reader); + } + + value = reader.GetValue(value); + } + } + } + + if (type.IsEnum) + { + value = Map.EnumToValue(value); + + if (value != null && !value.GetType().IsEnum) + BuildValue(sb, value); + else + sb.Append(value); + } + else + sb.Append(value); + } + } + + protected virtual void BuildString(StringBuilder sb, string value) + { + sb + .Append('\'') + .Append(value.Replace("'", "''")) + .Append('\''); + } + + protected virtual void BuildChar(StringBuilder sb, char value) + { + sb.Append('\''); + + if (value == '\'') sb.Append("''"); + else sb.Append(value); + + sb.Append('\''); + } + + protected virtual void BuildDateTime(StringBuilder sb, object value) + { + sb.Append(string.Format("'{0:yyyy-MM-dd HH:mm:ss.fff}'", value)); + } + + #endregion + + #region BuildBinaryExpression + + protected virtual void BuildBinaryExpression(StringBuilder sb, SqlBinaryExpression expr) + { + BuildBinaryExpression(sb, expr.Operation, expr); + } + + protected void BuildFunction(StringBuilder sb, string name, SqlBinaryExpression expr) + { + sb.Append(name); + sb.Append("("); + BuildExpression(sb, expr.Expr1); + sb.Append(", "); + BuildExpression(sb, expr.Expr2); + sb.Append(')'); + } + + protected void BuildBinaryExpression(StringBuilder sb, string op, SqlBinaryExpression expr) + { + if (expr.Operation == "*" && expr.Expr1 is SqlValue) + { + var value = (SqlValue)expr.Expr1; + + if (value.Value is int && (int)value.Value == -1) + { + sb.Append('-'); + BuildExpression(sb, GetPrecedence(expr), expr.Expr2); + return; + } + } + + BuildExpression(sb, GetPrecedence(expr), expr.Expr1); + sb.Append(' ').Append(op).Append(' '); + BuildExpression(sb, GetPrecedence(expr), expr.Expr2); + } + + #endregion + + #region BuildFunction + + protected virtual void BuildFunction(StringBuilder sb, SqlFunction func) + { + if (func.Name == "CASE") + { + sb.Append(func.Name).AppendLine(); + + Indent++; + + var i = 0; + + for (; i < func.Parameters.Length - 1; i += 2) + { + AppendIndent(sb).Append("WHEN "); + + var len = sb.Length; + + BuildExpression(sb, func.Parameters[i]); + + if (SqlExpression.NeedsEqual(func.Parameters[i])) + { + sb.Append(" = "); + BuildValue(sb, true); + } + + if (sb.Length - len > 20) + { + sb.AppendLine(); + AppendIndent(sb).Append("\tTHEN "); + } + else + sb.Append(" THEN "); + + BuildExpression(sb, func.Parameters[i+1]); + sb.AppendLine(); + } + + if (i < func.Parameters.Length) + { + AppendIndent(sb).Append("ELSE "); + BuildExpression(sb, func.Parameters[i]); + sb.AppendLine(); + } + + Indent--; + + AppendIndent(sb).Append("END"); + } + else + BuildFunction(sb, func.Name, func.Parameters); + } + + protected void BuildFunction(StringBuilder sb, string name, ISqlExpression[] exprs) + { + sb.Append(name).Append('('); + + var first = true; + + foreach (var parameter in exprs) + { + if (!first) + sb.Append(", "); + + BuildExpression(sb, parameter, true, !first || name == "EXISTS"); + + first = false; + } + + sb.Append(')'); + } + + #endregion + + #region BuildDataType + + protected virtual void BuildDataType(StringBuilder sb, SqlDataType type) + { + sb.Append(type.SqlDbType.ToString()); + + if (type.Length > 0) + sb.Append('(').Append(type.Length).Append(')'); + + if (type.Precision > 0) + sb.Append('(').Append(type.Precision).Append(',').Append(type.Scale).Append(')'); + } + + #endregion + + #region GetPrecedence + + protected virtual int GetPrecedence(ISqlExpression expr) + { + return expr.Precedence; + } + + protected virtual int GetPrecedence(ISqlPredicate predicate) + { + return predicate.Precedence; + } + + #endregion + + #endregion + + #region Internal Types + + public enum Step + { + SelectClause, + DeleteClause, + UpdateClause, + InsertClause, + FromClause, + WhereClause, + GroupByClause, + HavingClause, + OrderByClause, + OffsetLimit + } + + #endregion + + #region Alternative Builders + + protected virtual void BuildAliases(StringBuilder sb, string table, List<SqlQuery.Column> columns, string postfix) + { + Indent++; + + var first = true; + + foreach (var col in columns) + { + if (!first) + sb.Append(',').AppendLine(); + first = false; + + AppendIndent(sb).AppendFormat("{0}.{1}", table, Convert(col.Alias, ConvertType.NameToQueryFieldAlias)); + + if (postfix != null) + sb.Append(postfix); + } + + Indent--; + + sb.AppendLine(); + } + + protected void AlternativeBuildSql(StringBuilder sb, bool implementOrderBy, Action<StringBuilder> buildSql) + { + if (NeedSkip) + { + var aliases = GetTempAliases(2, "t"); + var rnaliase = GetTempAliases(1, "rn")[0]; + + AppendIndent(sb).Append("SELECT *").AppendLine(); + AppendIndent(sb).Append("FROM"). AppendLine(); + AppendIndent(sb).Append("("). AppendLine(); + Indent++; + + AppendIndent(sb).Append("SELECT").AppendLine(); + + Indent++; + AppendIndent(sb).AppendFormat("{0}.*,", aliases[0]).AppendLine(); + AppendIndent(sb).Append("ROW_NUMBER() OVER"); + + if (!SqlQuery.OrderBy.IsEmpty && !implementOrderBy) + sb.Append("()"); + else + { + sb.AppendLine(); + AppendIndent(sb).Append("(").AppendLine(); + + Indent++; + + if (SqlQuery.OrderBy.IsEmpty) + { + AppendIndent(sb).Append("ORDER BY").AppendLine(); + BuildAliases(sb, aliases[0], SqlQuery.Select.Columns.Take(1).ToList(), null); + } + else + BuildAlternativeOrderBy(sb, true); + + Indent--; + AppendIndent(sb).Append(")"); + } + + sb.Append(" as ").Append(rnaliase).AppendLine(); + Indent--; + + AppendIndent(sb).Append("FROM").AppendLine(); + AppendIndent(sb).Append("(").AppendLine(); + + Indent++; + buildSql(sb); + Indent--; + + AppendIndent(sb).AppendFormat(") {0}", aliases[0]).AppendLine(); + + Indent--; + + AppendIndent(sb).AppendFormat(") {0}", aliases[1]).AppendLine(); + AppendIndent(sb).Append("WHERE").AppendLine(); + + Indent++; + + if (NeedTake) + { + var expr1 = Add(SqlQuery.Select.SkipValue, 1); + var expr2 = Add<int>(SqlQuery.Select.SkipValue, SqlQuery.Select.TakeValue); + + if (expr1 is SqlValue && expr2 is SqlValue && Equals(((SqlValue)expr1).Value, ((SqlValue)expr2).Value)) + { + AppendIndent(sb).AppendFormat("{0}.{1} = ", aliases[1], rnaliase); + BuildExpression(sb, expr1); + } + else + { + AppendIndent(sb).AppendFormat("{0}.{1} BETWEEN ", aliases[1], rnaliase); + BuildExpression(sb, expr1); + sb.Append(" AND "); + BuildExpression(sb, expr2); + } + } + else + { + AppendIndent(sb).AppendFormat("{0}.{1} > ", aliases[1], rnaliase); + BuildExpression(sb, SqlQuery.Select.SkipValue); + } + + sb.AppendLine(); + Indent--; + } + else + buildSql(sb); + } + + protected void AlternativeBuildSql2(StringBuilder sb, Action<StringBuilder> buildSql) + { + var aliases = GetTempAliases(3, "t"); + + AppendIndent(sb).Append("SELECT *").AppendLine(); + AppendIndent(sb).Append("FROM") .AppendLine(); + AppendIndent(sb).Append("(") .AppendLine(); + Indent++; + + AppendIndent(sb).Append("SELECT TOP "); + BuildExpression(sb, SqlQuery.Select.TakeValue); + sb.Append(" *").AppendLine(); + AppendIndent(sb).Append("FROM").AppendLine(); + AppendIndent(sb).Append("(") .AppendLine(); + Indent++; + + if (SqlQuery.OrderBy.IsEmpty) + { + AppendIndent(sb).Append("SELECT TOP "); + + var p = SqlQuery.Select.SkipValue as SqlParameter; + + if (p != null && !p.IsQueryParameter && SqlQuery.Select.TakeValue is SqlValue) + BuildValue(sb, (int)p.Value + (int)((SqlValue)(SqlQuery.Select.TakeValue)).Value); + else + BuildExpression(sb, Add<int>(SqlQuery.Select.SkipValue, SqlQuery.Select.TakeValue)); + + sb.Append(" *").AppendLine(); + AppendIndent(sb).Append("FROM").AppendLine(); + AppendIndent(sb).Append("(") .AppendLine(); + Indent++; + } + + buildSql(sb); + + if (SqlQuery.OrderBy.IsEmpty) + { + Indent--; + AppendIndent(sb).AppendFormat(") {0}", aliases[2]).AppendLine(); + AppendIndent(sb).Append("ORDER BY").AppendLine(); + BuildAliases(sb, aliases[2], SqlQuery.Select.Columns, null); + } + + Indent--; + AppendIndent(sb).AppendFormat(") {0}", aliases[1]).AppendLine(); + + if (SqlQuery.OrderBy.IsEmpty) + { + AppendIndent(sb).Append("ORDER BY").AppendLine(); + BuildAliases(sb, aliases[1], SqlQuery.Select.Columns, " DESC"); + } + else + { + BuildAlternativeOrderBy(sb, false); + } + + Indent--; + AppendIndent(sb).AppendFormat(") {0}", aliases[0]).AppendLine(); + + if (SqlQuery.OrderBy.IsEmpty) + { + AppendIndent(sb).Append("ORDER BY").AppendLine(); + BuildAliases(sb, aliases[0], SqlQuery.Select.Columns, null); + } + else + { + BuildAlternativeOrderBy(sb, true); + } + } + + protected void BuildAlternativeOrderBy(StringBuilder sb, bool ascending) + { + AppendIndent(sb).Append("ORDER BY").AppendLine(); + + var obys = GetTempAliases(SqlQuery.OrderBy.Items.Count, "oby"); + + Indent++; + + for (var i = 0; i < obys.Length; i++) + { + AppendIndent(sb).Append(obys[i]); + + if ( ascending && SqlQuery.OrderBy.Items[i].IsDescending || + !ascending && !SqlQuery.OrderBy.Items[i].IsDescending) + sb.Append(" DESC"); + + if (i + 1 < obys.Length) + sb.Append(','); + + sb.AppendLine(); + } + + Indent--; + } + + protected delegate IEnumerable<SqlQuery.Column> ColumnSelector(); + + protected IEnumerable<SqlQuery.Column> AlternativeGetSelectedColumns(ColumnSelector columnSelector) + { + foreach (var col in columnSelector()) + yield return col; + + var obys = GetTempAliases(SqlQuery.OrderBy.Items.Count, "oby"); + + for (var i = 0; i < obys.Length; i++) + yield return new SqlQuery.Column(SqlQuery, SqlQuery.OrderBy.Items[i].Expression, obys[i]); + } + + protected bool IsDateDataType(ISqlExpression expr, string dateName) + { + switch (expr.ElementType) + { + case QueryElementType.SqlDataType : return ((SqlDataType) expr).SqlDbType == SqlDbType.Date; + case QueryElementType.SqlExpression : return ((SqlExpression)expr).Expr == dateName; + } + + return false; + } + + protected bool IsTimeDataType(ISqlExpression expr) + { + switch (expr.ElementType) + { + case QueryElementType.SqlDataType : return ((SqlDataType)expr). SqlDbType == SqlDbType.Time; + case QueryElementType.SqlExpression : return ((SqlExpression)expr).Expr == "Time"; + } + + return false; + } + + protected ISqlExpression FloorBeforeConvert(SqlFunction func) + { + var par1 = func.Parameters[1]; + + return TypeHelper.IsFloatType(par1.SystemType) && TypeHelper.IsIntegerType(func.SystemType) ? + new SqlFunction(func.SystemType, "Floor", par1) : par1; + } + + protected ISqlExpression AlternativeConvertToBoolean(SqlFunction func, int paramNumber) + { + var par = func.Parameters[paramNumber]; + + if (TypeHelper.IsFloatType(par.SystemType) || TypeHelper.IsIntegerType(par.SystemType)) + { + var sc = new SqlQuery.SearchCondition(); + + sc.Conditions.Add( + new SqlQuery.Condition(false, new SqlQuery.Predicate.ExprExpr(par, SqlQuery.Predicate.Operator.Equal, new SqlValue(0)))); + + return ConvertExpression(new SqlFunction(func.SystemType, "CASE", sc, new SqlValue(false), new SqlValue(true))); + } + + return null; + } + + protected SqlQuery GetAlternativeDelete(SqlQuery sqlQuery) + { + if (sqlQuery.IsDelete && + (sqlQuery.From.Tables.Count > 1 || sqlQuery.From.Tables[0].Joins.Count > 0) && + sqlQuery.From.Tables[0].Source is SqlTable) + { + var sql = new SqlQuery { QueryType = QueryType.Delete, IsParameterDependent = sqlQuery.IsParameterDependent }; + + sqlQuery.ParentSql = sql; + sqlQuery.QueryType = QueryType.Select; + + var table = (SqlTable)sqlQuery.From.Tables[0].Source; + var copy = new SqlTable(table) { Alias = null }; + + var tableKeys = table.GetKeys(true); + var copyKeys = copy. GetKeys(true); + + if (sqlQuery.Where.SearchCondition.Conditions.Any(c => c.IsOr)) + { + var sc1 = new SqlQuery.SearchCondition(sqlQuery.Where.SearchCondition.Conditions); + var sc2 = new SqlQuery.SearchCondition(); + + for (var i = 0; i < tableKeys.Count; i++) + { + sc2.Conditions.Add(new SqlQuery.Condition( + false, + new SqlQuery.Predicate.ExprExpr(copyKeys[i], SqlQuery.Predicate.Operator.Equal, tableKeys[i]))); + } + + sqlQuery.Where.SearchCondition.Conditions.Clear(); + sqlQuery.Where.SearchCondition.Conditions.Add(new SqlQuery.Condition(false, sc1)); + sqlQuery.Where.SearchCondition.Conditions.Add(new SqlQuery.Condition(false, sc2)); + } + else + { + for (var i = 0; i < tableKeys.Count; i++) + sqlQuery.Where.Expr(copyKeys[i]).Equal.Expr(tableKeys[i]); + } + + sql.From.Table(copy).Where.Exists(sqlQuery); + sql.Parameters.AddRange(sqlQuery.Parameters); + + sqlQuery.Parameters.Clear(); + + sqlQuery = sql; + } + + return sqlQuery; + } + + protected SqlQuery GetAlternativeUpdate(SqlQuery sqlQuery) + { + if (sqlQuery.IsUpdate && (sqlQuery.From.Tables[0].Source is SqlTable || sqlQuery.Update.Table != null)) + { + if (sqlQuery.From.Tables.Count > 1 || sqlQuery.From.Tables[0].Joins.Count > 0) + { + var sql = new SqlQuery { QueryType = QueryType.Update, IsParameterDependent = sqlQuery.IsParameterDependent }; + + sqlQuery.ParentSql = sql; + sqlQuery.QueryType = QueryType.Select; + + var table = sqlQuery.Update.Table ?? (SqlTable)sqlQuery.From.Tables[0].Source; + + if (sqlQuery.Update.Table != null) + if (new QueryVisitor().Find(sqlQuery.From, t => t == table) == null) + table = (SqlTable)new QueryVisitor().Find(sqlQuery.From, + ex => ex is SqlTable && ((SqlTable)ex).ObjectType == table.ObjectType) ?? table; + + var copy = new SqlTable(table); + + var tableKeys = table.GetKeys(true); + var copyKeys = copy. GetKeys(true); + + for (var i = 0; i < tableKeys.Count; i++) + sqlQuery.Where + .Expr(copyKeys[i]).Equal.Expr(tableKeys[i]); + + sql.From.Table(copy).Where.Exists(sqlQuery); + + var map = new Dictionary<SqlField, SqlField>(table.Fields.Count); + + foreach (var field in table.Fields.Values) + map.Add(field, copy[field.Name]); + + foreach (var item in sqlQuery.Update.Items) + { + var ex = new QueryVisitor().Convert(item, expr => + { + var fld = expr as SqlField; + return fld != null && map.TryGetValue(fld, out fld) ? fld : expr; + }); + + sql.Update.Items.Add(ex); + } + + sql.Parameters.AddRange(sqlQuery.Parameters); + sql.Update.Table = sqlQuery.Update.Table; + + sqlQuery.Parameters.Clear(); + sqlQuery.Update.Items.Clear(); + + sqlQuery = sql; + } + + sqlQuery.From.Tables[0].Alias = "$"; + } + + return sqlQuery; + } + + static bool IsBooleanParameter(ISqlExpression expr, int count, int i) + { + if ((i % 2 == 1 || i == count - 1) && expr.SystemType == typeof(bool) || expr.SystemType == typeof(bool?)) + { + switch (expr.ElementType) + { + case QueryElementType.SearchCondition : return true; + } + } + + return false; + } + + protected SqlFunction ConvertFunctionParameters(SqlFunction func) + { + if (func.Name == "CASE" && + func.Parameters.Select((p,i) => new { p, i }).Any(p => IsBooleanParameter(p.p, func.Parameters.Length, p.i))) + { + return new SqlFunction( + func.SystemType, + func.Name, + func.Precedence, + func.Parameters.Select((p,i) => + IsBooleanParameter(p, func.Parameters.Length, i) ? + ConvertExpression(new SqlFunction(typeof(bool), "CASE", p, new SqlValue(true), new SqlValue(false))) : + p + ).ToArray()); + } + + return func; + } + + #endregion + + #region Helpers + + protected SequenceNameAttribute GetSequenceNameAttribute(SqlTable table, bool throwException) + { + var identityField = table.GetIdentityField(); + + if (identityField == null) + if (throwException) + throw new SqlException("Identity field must be defined for '{0}'.", table.Name); + else + return null; + + if (table.ObjectType == null) + if (throwException) + throw new SqlException("Sequence name can not be retrieved for the '{0}' table.", table.Name); + else + return null; + + var attrs = table.SequenceAttributes; + + if (attrs == null) + if (throwException) + throw new SqlException("Sequence name can not be retrieved for the '{0}' table.", table.Name); + else + return null; + + SequenceNameAttribute defaultAttr = null; + + foreach (var attr in attrs) + { + if (attr.ProviderName == Name) + return attr; + + if (defaultAttr == null && attr.ProviderName == null) + defaultAttr = attr; + } + + if (defaultAttr == null) + if (throwException) + throw new SqlException("Sequence name can not be retrieved for the '{0}' table.", table.Name); + else + return null; + + return defaultAttr; + } + + static string SetAlias(string alias, int maxLen) + { + if (alias == null) + return null; + + alias = alias.TrimStart('_'); + + var cs = alias.ToCharArray(); + var replace = false; + + for (var i = 0; i < cs.Length; i++) + { + var c = cs[i]; + + if (c >= 'a' && c <= 'z' || c >= 'A' && c <= 'Z' || c >= '0' && c <= '9' || c == '_') + continue; + + cs[i] = ' '; + replace = true; + } + + if (replace) + alias = new string(cs).Replace(" ", ""); + + return alias.Length == 0 || alias.Length > maxLen ? null : alias; + } + + protected void CheckAliases(SqlQuery sqlQuery, int maxLen) + { + new QueryVisitor().Visit(sqlQuery, e => + { + switch (e.ElementType) + { + case QueryElementType.SqlField : ((SqlField) e).Alias = SetAlias(((SqlField) e).Alias, maxLen); break; + case QueryElementType.SqlParameter : ((SqlParameter) e).Name = SetAlias(((SqlParameter) e).Name, maxLen); break; + case QueryElementType.SqlTable : ((SqlTable) e).Alias = SetAlias(((SqlTable) e).Alias, maxLen); break; + case QueryElementType.Join : ((Join) e).Alias = SetAlias(((Join) e).Alias, maxLen); break; + case QueryElementType.Column : ((SqlQuery.Column) e).Alias = SetAlias(((SqlQuery.Column) e).Alias, maxLen); break; + case QueryElementType.TableSource : ((SqlQuery.TableSource)e).Alias = SetAlias(((SqlQuery.TableSource)e).Alias, maxLen); break; + } + }); + } + + static bool Wrap(int precedence, int parentPrecedence) + { + return + precedence == 0 || + precedence < parentPrecedence || + (precedence == parentPrecedence && + (parentPrecedence == Precedence.Subtraction || + parentPrecedence == Precedence.LogicalNegation)); + } + + protected string[] GetTempAliases(int n, string defaultAlias) + { + return SqlQuery.GetTempAliases(n, defaultAlias + (Nesting == 0? "": "n" + Nesting)); + } + + protected static string GetTableAlias(ISqlTableSource table) + { + switch (table.ElementType) + { + case QueryElementType.TableSource : + var ts = (SqlQuery.TableSource)table; + var alias = string.IsNullOrEmpty(ts.Alias) ? GetTableAlias(ts.Source) : ts.Alias; + return alias != "$" ? alias : null; + + case QueryElementType.SqlTable : + return ((SqlTable)table).Alias; + + default : + throw new InvalidOperationException(); + } + } + + string GetTablePhysicalName(ISqlTableSource table, string alias) + { + switch (table.ElementType) + { + case QueryElementType.SqlTable : + { + var tbl = (SqlTable)table; + + var database = tbl.Database == null ? null : Convert(tbl.Database, ConvertType.NameToDatabase). ToString(); + var owner = tbl.Owner == null ? null : Convert(tbl.Owner, ConvertType.NameToOwner). ToString(); + var physicalName = tbl.PhysicalName == null ? null : Convert(tbl.PhysicalName, ConvertType.NameToQueryTable).ToString(); + + var sb = new StringBuilder(); + + if (tbl.SqlTableType == SqlTableType.Expression) + { + if (tbl.TableArguments == null) + physicalName = tbl.PhysicalName; + else + { + var values = new object[tbl.TableArguments.Length + 2]; + + values[0] = physicalName; + values[1] = Convert(alias, ConvertType.NameToQueryTableAlias); + + for (var i = 2; i < values.Length; i++) + { + var value = tbl.TableArguments[i - 2]; + + sb.Length = 0; + BuildExpression(sb, Precedence.Primary, value); + values[i] = sb.ToString(); + } + + physicalName = string.Format(tbl.Name, values); + + sb.Length = 0; + } + } + + BuildTableName(sb, database, owner, physicalName); + + if (tbl.SqlTableType == SqlTableType.Function) + { + sb.Append('('); + + if (tbl.TableArguments != null && tbl.TableArguments.Length > 0) + { + var first = true; + + foreach (var arg in tbl.TableArguments) + { + if (!first) + sb.Append(", "); + + BuildExpression(sb, arg, true, !first); + + first = false; + } + } + + sb.Append(')'); + } + + return sb.ToString(); + } + + case QueryElementType.TableSource : + return GetTablePhysicalName(((SqlQuery.TableSource)table).Source, alias); + + default : + throw new InvalidOperationException(); + } + } + + protected StringBuilder AppendIndent(StringBuilder sb) + { + if (Indent > 0) + sb.Append('\t', Indent); + + return sb; + } + + public ISqlExpression Add(ISqlExpression expr1, ISqlExpression expr2, Type type) + { + return ConvertExpression(new SqlBinaryExpression(type, expr1, "+", expr2, Precedence.Additive)); + } + + public ISqlExpression Add<T>(ISqlExpression expr1, ISqlExpression expr2) + { + return Add(expr1, expr2, typeof(T)); + } + + public ISqlExpression Add(ISqlExpression expr1, int value) + { + return Add<int>(expr1, new SqlValue(value)); + } + + public ISqlExpression Inc(ISqlExpression expr1) + { + return Add(expr1, 1); + } + + public ISqlExpression Sub(ISqlExpression expr1, ISqlExpression expr2, Type type) + { + return ConvertExpression(new SqlBinaryExpression(type, expr1, "-", expr2, Precedence.Subtraction)); + } + + public ISqlExpression Sub<T>(ISqlExpression expr1, ISqlExpression expr2) + { + return Sub(expr1, expr2, typeof(T)); + } + + public ISqlExpression Sub(ISqlExpression expr1, int value) + { + return Sub<int>(expr1, new SqlValue(value)); + } + + public ISqlExpression Dec(ISqlExpression expr1) + { + return Sub(expr1, 1); + } + + public ISqlExpression Mul(ISqlExpression expr1, ISqlExpression expr2, Type type) + { + return ConvertExpression(new SqlBinaryExpression(type, expr1, "*", expr2, Precedence.Multiplicative)); + } + + public ISqlExpression Mul<T>(ISqlExpression expr1, ISqlExpression expr2) + { + return Mul(expr1, expr2, typeof(T)); + } + + public ISqlExpression Mul(ISqlExpression expr1, int value) + { + return Mul<int>(expr1, new SqlValue(value)); + } + + public ISqlExpression Div(ISqlExpression expr1, ISqlExpression expr2, Type type) + { + return ConvertExpression(new SqlBinaryExpression(type, expr1, "/", expr2, Precedence.Multiplicative)); + } + + public ISqlExpression Div<T>(ISqlExpression expr1, ISqlExpression expr2) + { + return Div(expr1, expr2, typeof(T)); + } + + public ISqlExpression Div(ISqlExpression expr1, int value) + { + return Div<int>(expr1, new SqlValue(value)); + } + + #endregion + + #region DataTypes + + protected virtual int GetMaxLength (SqlDataType type) { return SqlDataType.GetMaxLength (type.SqlDbType); } + protected virtual int GetMaxPrecision (SqlDataType type) { return SqlDataType.GetMaxPrecision (type.SqlDbType); } + protected virtual int GetMaxScale (SqlDataType type) { return SqlDataType.GetMaxScale (type.SqlDbType); } + protected virtual int GetMaxDisplaySize(SqlDataType type) { return SqlDataType.GetMaxDisplaySize(type.SqlDbType); } + + protected virtual ISqlExpression ConvertConvertion(SqlFunction func) + { + var from = (SqlDataType)func.Parameters[1]; + var to = (SqlDataType)func.Parameters[0]; + + if (to.Type == typeof(object)) + return func.Parameters[2]; + + if (to.Precision > 0) + { + var maxPrecision = GetMaxPrecision(from); + var maxScale = GetMaxScale (from); + var newPrecision = maxPrecision >= 0 ? Math.Min(to.Precision, maxPrecision) : to.Precision; + var newScale = maxScale >= 0 ? Math.Min(to.Scale, maxScale) : to.Scale; + + if (to.Precision != newPrecision || to.Scale != newScale) + to = new SqlDataType(to.SqlDbType, to.Type, newPrecision, newScale); + } + else if (to.Length > 0) + { + var maxLength = to.Type == typeof(string) ? GetMaxDisplaySize(from) : GetMaxLength(from); + var newLength = maxLength >= 0 ? Math.Min(to.Length, maxLength) : to.Length; + + if (to.Length != newLength) + to = new SqlDataType(to.SqlDbType, to.Type, newLength); + } + else if (from.Type == typeof(short) && to.Type == typeof(int)) + return func.Parameters[2]; + + return ConvertExpression(new SqlFunction(func.SystemType, "Convert", to, func.Parameters[2])); + } + + #endregion + + #region ISqlProvider Members + + public virtual ISqlExpression ConvertExpression(ISqlExpression expression) + { + switch (expression.ElementType) + { + case QueryElementType.SqlBinaryExpression: + + #region SqlBinaryExpression + + { + var be = (SqlBinaryExpression)expression; + + switch (be.Operation) + { + case "+": + if (be.Expr1 is SqlValue) + { + var v1 = (SqlValue)be.Expr1; + if (v1.Value is int && (int) v1.Value == 0 || + v1.Value is string && (string)v1.Value == "") return be.Expr2; + } + + if (be.Expr2 is SqlValue) + { + var v2 = (SqlValue) be.Expr2; + + if (v2.Value is int) + { + if ((int)v2.Value == 0) return be.Expr1; + + if (be.Expr1 is SqlBinaryExpression) + { + var be1 = (SqlBinaryExpression) be.Expr1; + + if (be1.Expr2 is SqlValue) + { + var be1v2 = (SqlValue)be1.Expr2; + + if (be1v2.Value is int) + { + switch (be1.Operation) + { + case "+": + { + var value = (int)be1v2.Value + (int)v2.Value; + var oper = be1.Operation; + + if (value < 0) + { + value = - value; + oper = "-"; + } + + return new SqlBinaryExpression(be.SystemType, be1.Expr1, oper, new SqlValue(value), be.Precedence); + } + + case "-": + { + var value = (int)be1v2.Value - (int)v2.Value; + var oper = be1.Operation; + + if (value < 0) + { + value = - value; + oper = "+"; + } + + return new SqlBinaryExpression(be.SystemType, be1.Expr1, oper, new SqlValue(value), be.Precedence); + } + } + } + } + } + } + else if (v2.Value is string) + { + if ((string)v2.Value == "") return be.Expr1; + + if (be.Expr1 is SqlBinaryExpression) + { + var be1 = (SqlBinaryExpression)be.Expr1; + + if (be1.Expr2 is SqlValue) + { + var value = ((SqlValue)be1.Expr2).Value; + + if (value is string) + return new SqlBinaryExpression( + be1.SystemType, + be1.Expr1, + be1.Operation, + new SqlValue(string.Concat(value, v2.Value))); + } + } + } + } + + if (be.Expr1 is SqlValue && be.Expr2 is SqlValue) + { + var v1 = (SqlValue)be.Expr1; + var v2 = (SqlValue)be.Expr2; + if (v1.Value is int && v2.Value is int) return new SqlValue((int)v1.Value + (int)v2.Value); + if (v1.Value is string || v2.Value is string) return new SqlValue(v1.Value.ToString() + v2.Value); + } + + if (be.Expr1.SystemType == typeof(string) && be.Expr2.SystemType != typeof(string)) + { + var len = be.Expr2.SystemType == null ? 100 : SqlDataType.GetMaxDisplaySize(SqlDataType.GetDataType(be.Expr2.SystemType).SqlDbType); + + if (len <= 0) + len = 100; + + return new SqlBinaryExpression( + be.SystemType, + be.Expr1, + be.Operation, + ConvertExpression(new SqlFunction(typeof(string), "Convert", new SqlDataType(SqlDbType.VarChar, len), be.Expr2)), + be.Precedence); + } + + if (be.Expr1.SystemType != typeof(string) && be.Expr2.SystemType == typeof(string)) + { + var len = be.Expr1.SystemType == null ? 100 : SqlDataType.GetMaxDisplaySize(SqlDataType.GetDataType(be.Expr1.SystemType).SqlDbType); + + if (len <= 0) + len = 100; + + return new SqlBinaryExpression( + be.SystemType, + ConvertExpression(new SqlFunction(typeof(string), "Convert", new SqlDataType(SqlDbType.VarChar, len), be.Expr1)), + be.Operation, + be.Expr2, + be.Precedence); + } + + break; + + case "-": + if (be.Expr2 is SqlValue) + { + var v2 = (SqlValue) be.Expr2; + + if (v2.Value is int) + { + if ((int)v2.Value == 0) return be.Expr1; + + if (be.Expr1 is SqlBinaryExpression) + { + var be1 = (SqlBinaryExpression)be.Expr1; + + if (be1.Expr2 is SqlValue) + { + var be1v2 = (SqlValue)be1.Expr2; + + if (be1v2.Value is int) + { + switch (be1.Operation) + { + case "+": + { + var value = (int)be1v2.Value - (int)v2.Value; + var oper = be1.Operation; + + if (value < 0) + { + value = -value; + oper = "-"; + } + + return new SqlBinaryExpression(be.SystemType, be1.Expr1, oper, new SqlValue(value), be.Precedence); + } + + case "-": + { + var value = (int)be1v2.Value + (int)v2.Value; + var oper = be1.Operation; + + if (value < 0) + { + value = -value; + oper = "+"; + } + + return new SqlBinaryExpression(be.SystemType, be1.Expr1, oper, new SqlValue(value), be.Precedence); + } + } + } + } + } + } + } + + if (be.Expr1 is SqlValue && be.Expr2 is SqlValue) + { + var v1 = (SqlValue)be.Expr1; + var v2 = (SqlValue)be.Expr2; + if (v1.Value is int && v2.Value is int) return new SqlValue((int)v1.Value - (int)v2.Value); + } + + break; + + case "*": + if (be.Expr1 is SqlValue) + { + var v1 = (SqlValue)be.Expr1; + + if (v1.Value is int) + { + var v1v = (int)v1.Value; + + switch (v1v) + { + case 0 : return new SqlValue(0); + case 1 : return be.Expr2; + default : + { + var be2 = be.Expr2 as SqlBinaryExpression; + + if (be2 != null && be2.Operation == "*" && be2.Expr1 is SqlValue) + { + var be2v1 = be2.Expr1 as SqlValue; + + if (be2v1.Value is int) + return ConvertExpression( + new SqlBinaryExpression(be2.SystemType, new SqlValue(v1v * (int)be2v1.Value), "*", be2.Expr2)); + } + + break; + } + + } + } + } + + if (be.Expr2 is SqlValue) + { + var v2 = (SqlValue)be.Expr2; + if (v2.Value is int && (int)v2.Value == 1) return be.Expr1; + if (v2.Value is int && (int)v2.Value == 0) return new SqlValue(0); + } + + if (be.Expr1 is SqlValue && be.Expr2 is SqlValue) + { + var v1 = (SqlValue)be.Expr1; + var v2 = (SqlValue)be.Expr2; + + if (v1.Value is int) + { + if (v2.Value is int) return new SqlValue((int) v1.Value * (int) v2.Value); + if (v2.Value is double) return new SqlValue((int) v1.Value * (double)v2.Value); + } + else if (v1.Value is double) + { + if (v2.Value is int) return new SqlValue((double)v1.Value * (int) v2.Value); + if (v2.Value is double) return new SqlValue((double)v1.Value * (double)v2.Value); + } + } + + break; + } + } + + #endregion + + break; + + case QueryElementType.SqlFunction: + + #region SqlFunction + + { + var func = (SqlFunction)expression; + + switch (func.Name) + { + case "ConvertToCaseCompareTo": + return ConvertExpression(new SqlFunction(func.SystemType, "CASE", + new SqlQuery.SearchCondition().Expr(func.Parameters[0]). Greater .Expr(func.Parameters[1]).ToExpr(), new SqlValue(1), + new SqlQuery.SearchCondition().Expr(func.Parameters[0]). Equal .Expr(func.Parameters[1]).ToExpr(), new SqlValue(0), + new SqlValue(-1))); + + case "$Convert$": return ConvertConvertion(func); + case "Average" : return new SqlFunction(func.SystemType, "Avg", func.Parameters); + case "Max" : + case "Min" : + { + if (func.SystemType == typeof(bool) || func.SystemType == typeof(bool?)) + { + return new SqlFunction(typeof(int), func.Name, + new SqlFunction(func.SystemType, "CASE", func.Parameters[0], new SqlValue(1), new SqlValue(0))); + } + + break; + } + + case "CASE" : + { + var parms = func.Parameters; + var len = parms.Length; + + for (var i = 0; i < parms.Length - 1; i += 2) + { + var value = parms[i] as SqlValue; + + if (value != null) + { + if ((bool)value.Value == false) + { + var newParms = new ISqlExpression[parms.Length - 2]; + + if (i != 0) + Array.Copy(parms, 0, newParms, 0, i); + + Array.Copy(parms, i + 2, newParms, i, parms.Length - i - 2); + + parms = newParms; + i -= 2; + } + else + { + var newParms = new ISqlExpression[i + 1]; + + if (i != 0) + Array.Copy(parms, 0, newParms, 0, i); + + newParms[i] = parms[i + 1]; + + parms = newParms; + break; + } + } + } + + if (parms.Length == 1) + return parms[0]; + + if (parms.Length != len) + return new SqlFunction(func.SystemType, func.Name, func.Precedence, parms); + } + + break; + + case "Convert": + { + var from = func.Parameters[1] as SqlFunction; + var typef = TypeHelper.GetUnderlyingType(func.SystemType); + + if (from != null && from.Name == "Convert" && TypeHelper.GetUnderlyingType(from.Parameters[1].SystemType) == typef) + return from.Parameters[1]; + + var fe = func.Parameters[1] as SqlExpression; + + if (fe != null && fe.Expr == "Cast({0} as {1})" && TypeHelper.GetUnderlyingType(fe.Parameters[0].SystemType) == typef) + return fe.Parameters[0]; + } + + break; + } + } + + #endregion + + break; + + case QueryElementType.SearchCondition : + SqlQuery.OptimizeSearchCondition((SqlQuery.SearchCondition)expression); + break; + + case QueryElementType.SqlExpression : + { + var se = (SqlExpression)expression; + + if (se.Expr == "{0}" && se.Parameters.Length == 1 && se.Parameters[0] != null) + return se.Parameters[0]; + } + + break; + } + + return expression; + } + + public virtual ISqlPredicate ConvertPredicate(ISqlPredicate predicate) + { + switch (predicate.ElementType) + { + case QueryElementType.ExprExprPredicate: + { + var expr = (SqlQuery.Predicate.ExprExpr)predicate; + + if (expr.Operator == SqlQuery.Predicate.Operator.Equal && expr.Expr1 is SqlValue && expr.Expr2 is SqlValue) + { + var value = Equals(((SqlValue)expr.Expr1).Value, ((SqlValue)expr.Expr2).Value); + return new SqlQuery.Predicate.Expr(new SqlValue(value), Precedence.Comparison); + } + + switch (expr.Operator) + { + case SqlQuery.Predicate.Operator.Equal : + case SqlQuery.Predicate.Operator.NotEqual : + case SqlQuery.Predicate.Operator.Greater : + case SqlQuery.Predicate.Operator.GreaterOrEqual: + case SqlQuery.Predicate.Operator.Less : + case SqlQuery.Predicate.Operator.LessOrEqual : + predicate = OptimizeCase(expr); + break; + } + + if (predicate is SqlQuery.Predicate.ExprExpr) + { + expr = (SqlQuery.Predicate.ExprExpr)predicate; + + switch (expr.Operator) + { + case SqlQuery.Predicate.Operator.Equal : + case SqlQuery.Predicate.Operator.NotEqual : + var expr1 = expr.Expr1; + var expr2 = expr.Expr2; + + if (expr1.CanBeNull() && expr2.CanBeNull()) + { + if (expr1 is SqlParameter || expr2 is SqlParameter) + SqlQuery.IsParameterDependent = true; + else + if (expr1 is SqlQuery.Column || expr1 is SqlField) + if (expr2 is SqlQuery.Column || expr2 is SqlField) + predicate = ConvertEqualPredicate(expr); + } + + break; + } + } + } + + break; + + case QueryElementType.NotExprPredicate: + { + var expr = (SqlQuery.Predicate.NotExpr)predicate; + + if (expr.IsNot && expr.Expr1 is SqlQuery.SearchCondition) + { + var sc = (SqlQuery.SearchCondition)expr.Expr1; + + if (sc.Conditions.Count == 1) + { + var cond = sc.Conditions[0]; + + if (cond.IsNot) + return cond.Predicate; + + if (cond.Predicate is SqlQuery.Predicate.ExprExpr) + { + var ee = (SqlQuery.Predicate.ExprExpr)cond.Predicate; + + if (ee.Operator == SqlQuery.Predicate.Operator.Equal) + return new SqlQuery.Predicate.ExprExpr(ee.Expr1, SqlQuery.Predicate.Operator.NotEqual, ee.Expr2); + + if (ee.Operator == SqlQuery.Predicate.Operator.NotEqual) + return new SqlQuery.Predicate.ExprExpr(ee.Expr1, SqlQuery.Predicate.Operator.Equal, ee.Expr2); + } + } + } + } + + break; + } + + return predicate; + } + + protected ISqlPredicate ConvertEqualPredicate(SqlQuery.Predicate.ExprExpr expr) + { + var expr1 = expr.Expr1; + var expr2 = expr.Expr2; + var cond = new SqlQuery.SearchCondition(); + + if (expr.Operator == SqlQuery.Predicate.Operator.Equal) + cond + .Expr(expr1).IsNull. And .Expr(expr2).IsNull. Or + .Expr(expr1).IsNotNull. And .Expr(expr2).IsNotNull. And .Expr(expr1).Equal.Expr(expr2); + else + cond + .Expr(expr1).IsNull. And .Expr(expr2).IsNotNull. Or + .Expr(expr1).IsNotNull. And .Expr(expr2).IsNull. Or + .Expr(expr1).NotEqual.Expr(expr2); + + return cond; + } + + static SqlQuery.Predicate.Operator InvertOperator(SqlQuery.Predicate.Operator op, bool skipEqual) + { + switch (op) + { + case SqlQuery.Predicate.Operator.Equal : return skipEqual ? op : SqlQuery.Predicate.Operator.NotEqual; + case SqlQuery.Predicate.Operator.NotEqual : return skipEqual ? op : SqlQuery.Predicate.Operator.Equal; + case SqlQuery.Predicate.Operator.Greater : return SqlQuery.Predicate.Operator.LessOrEqual; + case SqlQuery.Predicate.Operator.NotLess : + case SqlQuery.Predicate.Operator.GreaterOrEqual : return SqlQuery.Predicate.Operator.Less; + case SqlQuery.Predicate.Operator.Less : return SqlQuery.Predicate.Operator.GreaterOrEqual; + case SqlQuery.Predicate.Operator.NotGreater : + case SqlQuery.Predicate.Operator.LessOrEqual : return SqlQuery.Predicate.Operator.Greater; + default: throw new InvalidOperationException(); + } + } + + ISqlPredicate OptimizeCase(SqlQuery.Predicate.ExprExpr expr) + { + var value = expr.Expr1 as SqlValue; + var func = expr.Expr2 as SqlFunction; + var valueFirst = false; + + if (value != null && func != null) + { + valueFirst = true; + } + else + { + value = expr.Expr2 as SqlValue; + func = expr.Expr1 as SqlFunction; + } + + if (value != null && func != null && func.Name == "CASE") + { + if (value.Value is int && func.Parameters.Length == 5) + { + var c1 = func.Parameters[0] as SqlQuery.SearchCondition; + var v1 = func.Parameters[1] as SqlValue; + var c2 = func.Parameters[2] as SqlQuery.SearchCondition; + var v2 = func.Parameters[3] as SqlValue; + var v3 = func.Parameters[4] as SqlValue; + + if (c1 != null && c1.Conditions.Count == 1 && v1 != null && v1.Value is int && + c2 != null && c2.Conditions.Count == 1 && v2 != null && v2.Value is int && v3 != null && v3.Value is int) + { + var ee1 = c1.Conditions[0].Predicate as SqlQuery.Predicate.ExprExpr; + var ee2 = c2.Conditions[0].Predicate as SqlQuery.Predicate.ExprExpr; + + if (ee1 != null && ee2 != null && ee1.Expr1.Equals(ee2.Expr1) && ee1.Expr2.Equals(ee2.Expr2)) + { + int e = 0, g = 0, l = 0; + + if (ee1.Operator == SqlQuery.Predicate.Operator.Equal || ee2.Operator == SqlQuery.Predicate.Operator.Equal) e = 1; + if (ee1.Operator == SqlQuery.Predicate.Operator.Greater || ee2.Operator == SqlQuery.Predicate.Operator.Greater) g = 1; + if (ee1.Operator == SqlQuery.Predicate.Operator.Less || ee2.Operator == SqlQuery.Predicate.Operator.Less) l = 1; + + if (e + g + l == 2) + { + var n = (int)value.Value; + var i1 = (int)v1.Value; + var i2 = (int)v2.Value; + var i3 = (int)v3.Value; + + var n1 = Compare(valueFirst ? n : i1, valueFirst ? i1 : n, expr.Operator) ? 1 : 0; + var n2 = Compare(valueFirst ? n : i2, valueFirst ? i2 : n, expr.Operator) ? 1 : 0; + var n3 = Compare(valueFirst ? n : i3, valueFirst ? i3 : n, expr.Operator) ? 1 : 0; + + if (n1 + n2 + n3 == 1) + { + if (n1 == 1) return ee1; + if (n2 == 1) return ee2; + + return ConvertPredicate(new SqlQuery.Predicate.ExprExpr( + ee1.Expr1, + e == 0 ? SqlQuery.Predicate.Operator.Equal : + g == 0 ? SqlQuery.Predicate.Operator.Greater : + SqlQuery.Predicate.Operator.Less, + ee1.Expr2)); + } + + // CASE + // WHEN [p].[FirstName] > 'John' + // THEN 1 + // WHEN [p].[FirstName] = 'John' + // THEN 0 + // ELSE -1 + // END <= 0 + if (ee1.Operator == SqlQuery.Predicate.Operator.Greater && i1 == 1 && + ee2.Operator == SqlQuery.Predicate.Operator.Equal && i2 == 0 && + i3 == -1 && n == 0) + { + return ConvertPredicate(new SqlQuery.Predicate.ExprExpr( + ee1.Expr1, + valueFirst ? InvertOperator(expr.Operator, true) : expr.Operator, + ee1.Expr2)); + } + } + } + } + } + else if (value.Value is bool && func.Parameters.Length == 3) + { + var c1 = func.Parameters[0] as SqlQuery.SearchCondition; + var v1 = func.Parameters[1] as SqlValue; + var v2 = func.Parameters[2] as SqlValue; + + if (c1 != null && c1.Conditions.Count == 1 && v1 != null && v1.Value is bool && v2 != null && v2.Value is bool) + { + var bv = (bool)value.Value; + var bv1 = (bool)v1.Value; + var bv2 = (bool)v2.Value; + + if (bv == bv1 && expr.Operator == SqlQuery.Predicate.Operator.Equal || + bv != bv1 && expr.Operator == SqlQuery.Predicate.Operator.NotEqual) + { + return c1; + } + + if (bv == bv2 && expr.Operator == SqlQuery.Predicate.Operator.NotEqual || + bv != bv1 && expr.Operator == SqlQuery.Predicate.Operator.Equal) + { + var ee = c1.Conditions[0].Predicate as SqlQuery.Predicate.ExprExpr; + + if (ee != null) + { + var op = InvertOperator(ee.Operator, false); + return new SqlQuery.Predicate.ExprExpr(ee.Expr1, op, ee.Expr2); + } + + var sc = new SqlQuery.SearchCondition(); + + sc.Conditions.Add(new SqlQuery.Condition(true, c1)); + + return sc; + } + } + } + else if (expr.Operator == SqlQuery.Predicate.Operator.Equal && func.Parameters.Length == 3) + { + var sc = func.Parameters[0] as SqlQuery.SearchCondition; + var v1 = func.Parameters[1] as SqlValue; + var v2 = func.Parameters[2] as SqlValue; + + if (sc != null && v1 != null && v2 != null) + { + if (Equals(value.Value, v1.Value)) + return sc; + + if (Equals(value.Value, v2.Value) && !sc.CanBeNull()) + return ConvertPredicate(new SqlQuery.Predicate.NotExpr(sc, true, Precedence.LogicalNegation)); + } + } + } + + return expr; + } + + static bool Compare(int v1, int v2, SqlQuery.Predicate.Operator op) + { + switch (op) + { + case SqlQuery.Predicate.Operator.Equal: return v1 == v2; + case SqlQuery.Predicate.Operator.NotEqual: return v1 != v2; + case SqlQuery.Predicate.Operator.Greater: return v1 > v2; + case SqlQuery.Predicate.Operator.NotLess: + case SqlQuery.Predicate.Operator.GreaterOrEqual: return v1 >= v2; + case SqlQuery.Predicate.Operator.Less: return v1 < v2; + case SqlQuery.Predicate.Operator.NotGreater: + case SqlQuery.Predicate.Operator.LessOrEqual: return v1 <= v2; + } + + throw new InvalidOperationException(); + } + + public virtual SqlQuery Finalize(SqlQuery sqlQuery) + { + sqlQuery.FinalizeAndValidate(IsApplyJoinSupported, IsGroupByExpressionSupported); + + if (!IsCountSubQuerySupported) sqlQuery = MoveCountSubQuery (sqlQuery); + if (!IsSubQueryColumnSupported) sqlQuery = MoveSubQueryColumn(sqlQuery); + + if (!IsCountSubQuerySupported || !IsSubQueryColumnSupported) + sqlQuery.FinalizeAndValidate(IsApplyJoinSupported, IsGroupByExpressionSupported); + + return sqlQuery; + } + + SqlQuery MoveCountSubQuery(SqlQuery sqlQuery) + { + new QueryVisitor().Visit(sqlQuery, MoveCountSubQuery); + return sqlQuery; + } + + void MoveCountSubQuery(IQueryElement element) + { + if (element.ElementType != QueryElementType.SqlQuery) + return; + + var query = (SqlQuery)element; + + for (var i = 0; i < query.Select.Columns.Count; i++) + { + var col = query.Select.Columns[i]; + + // The column is a subquery. + // + if (col.Expression.ElementType == QueryElementType.SqlQuery) + { + var subQuery = (SqlQuery)col.Expression; + var isCount = false; + + // Check if subquery is Count subquery. + // + if (subQuery.Select.Columns.Count == 1) + { + var subCol = subQuery.Select.Columns[0]; + + if (subCol.Expression.ElementType == QueryElementType.SqlFunction) + isCount = ((SqlFunction)subCol.Expression).Name == "Count"; + } + + if (!isCount) + continue; + + // Check if subquery where clause does not have ORs. + // + SqlQuery.OptimizeSearchCondition(subQuery.Where.SearchCondition); + + var allAnd = true; + + for (var j = 0; allAnd && j < subQuery.Where.SearchCondition.Conditions.Count - 1; j++) + { + var cond = subQuery.Where.SearchCondition.Conditions[j]; + + if (cond.IsOr) + allAnd = false; + } + + if (!allAnd || !ConvertCountSubQuery(subQuery)) + continue; + + // Collect tables. + // + var allTables = new HashSet<ISqlTableSource>(); + var levelTables = new HashSet<ISqlTableSource>(); + + new QueryVisitor().Visit(subQuery, e => + { + if (e is ISqlTableSource) + allTables.Add((ISqlTableSource)e); + }); + + new QueryVisitor().Visit(subQuery, e => + { + if (e is ISqlTableSource) + if (subQuery.From.IsChild((ISqlTableSource)e)) + levelTables.Add((ISqlTableSource)e); + }); + + Func<IQueryElement,bool> checkTable = e => + { + switch (e.ElementType) + { + case QueryElementType.SqlField : return !allTables.Contains(((SqlField) e).Table); + case QueryElementType.Column : return !allTables.Contains(((SqlQuery.Column)e).Parent); + } + return false; + }; + + var join = SqlQuery.LeftJoin(subQuery); + + query.From.Tables[0].Joins.Add(join.JoinedTable); + + for (var j = 0; j < subQuery.Where.SearchCondition.Conditions.Count; j++) + { + var cond = subQuery.Where.SearchCondition.Conditions[j]; + + if (new QueryVisitor().Find(cond, checkTable) == null) + continue; + + var replaced = new Dictionary<IQueryElement,IQueryElement>(); + + var nc = new QueryVisitor().Convert(cond, e => + { + var ne = e; + + switch (e.ElementType) + { + case QueryElementType.SqlField : + if (replaced.TryGetValue(e, out ne)) + return ne; + + if (levelTables.Contains(((SqlField)e).Table)) + { + subQuery.GroupBy.Expr((SqlField)e); + ne = subQuery.Select.Columns[subQuery.Select.Add((SqlField)e)]; + break; + } + + break; + + case QueryElementType.Column : + if (replaced.TryGetValue(e, out ne)) + return ne; + + if (levelTables.Contains(((SqlQuery.Column)e).Parent)) + { + subQuery.GroupBy.Expr((SqlQuery.Column)e); + ne = subQuery.Select.Columns[subQuery.Select.Add((SqlQuery.Column)e)]; + break; + } + + break; + } + + if (!ReferenceEquals(e, ne)) + replaced.Add(e, ne); + + return ne; + }); + + if (nc != null && !ReferenceEquals(nc, cond)) + { + join.JoinedTable.Condition.Conditions.Add(nc); + subQuery.Where.SearchCondition.Conditions.RemoveAt(j); + j--; + } + } + + if (!query.GroupBy.IsEmpty/* && subQuery.Select.Columns.Count > 1*/) + { + var oldFunc = (SqlFunction)subQuery.Select.Columns[0].Expression; + + subQuery.Select.Columns.RemoveAt(0); + + query.Select.Columns[i].Expression = + new SqlFunction(oldFunc.SystemType, oldFunc.Name, subQuery.Select.Columns[0]); + } + else + { + query.Select.Columns[i].Expression = subQuery.Select.Columns[0]; + } + } + } + } + + SqlQuery MoveSubQueryColumn(SqlQuery sqlQuery) + { + var dic = new Dictionary<IQueryElement,IQueryElement>(); + + new QueryVisitor().Visit(sqlQuery, element => + { + if (element.ElementType != QueryElementType.SqlQuery) + return; + + var query = (SqlQuery)element; + + for (var i = 0; i < query.Select.Columns.Count; i++) + { + var col = query.Select.Columns[i]; + + if (col.Expression.ElementType == QueryElementType.SqlQuery) + { + var subQuery = (SqlQuery)col.Expression; + var allTables = new HashSet<ISqlTableSource>(); + var levelTables = new HashSet<ISqlTableSource>(); + + Func<IQueryElement,bool> checkTable = e => + { + switch (e.ElementType) + { + case QueryElementType.SqlField : return !allTables.Contains(((SqlField)e).Table); + case QueryElementType.Column : return !allTables.Contains(((SqlQuery.Column)e).Parent); + } + return false; + }; + + new QueryVisitor().Visit(subQuery, e => + { + if (e is ISqlTableSource /*&& subQuery.From.IsChild((ISqlTableSource)e)*/) + allTables.Add((ISqlTableSource)e); + }); + + new QueryVisitor().Visit(subQuery, e => + { + if (e is ISqlTableSource && subQuery.From.IsChild((ISqlTableSource)e)) + levelTables.Add((ISqlTableSource)e); + }); + + if (IsSubQueryColumnSupported && new QueryVisitor().Find(subQuery, checkTable) == null) + continue; + + var join = SqlQuery.LeftJoin(subQuery); + + query.From.Tables[0].Joins.Add(join.JoinedTable); + + SqlQuery.OptimizeSearchCondition(subQuery.Where.SearchCondition); + + var isCount = false; + var isAggregated = false; + + if (subQuery.Select.Columns.Count == 1) + { + var subCol = subQuery.Select.Columns[0]; + + if (subCol.Expression.ElementType == QueryElementType.SqlFunction) + { + switch (((SqlFunction)subCol.Expression).Name) + { + case "Min" : + case "Max" : + case "Sum" : + case "Average" : isAggregated = true; break; + case "Count" : isAggregated = true; isCount = true; break; + } + } + } + + if (IsSubQueryColumnSupported && !isCount) + continue; + + var allAnd = true; + + for (var j = 0; allAnd && j < subQuery.Where.SearchCondition.Conditions.Count - 1; j++) + { + var cond = subQuery.Where.SearchCondition.Conditions[j]; + + if (cond.IsOr) + allAnd = false; + } + + if (!allAnd) + continue; + + var modified = false; + + for (var j = 0; j < subQuery.Where.SearchCondition.Conditions.Count; j++) + { + var cond = subQuery.Where.SearchCondition.Conditions[j]; + + if (new QueryVisitor().Find(cond, checkTable) == null) + continue; + + var replaced = new Dictionary<IQueryElement,IQueryElement>(); + + var nc = new QueryVisitor().Convert(cond, delegate(IQueryElement e) + { + var ne = e; + + switch (e.ElementType) + { + case QueryElementType.SqlField : + if (replaced.TryGetValue(e, out ne)) + return ne; + + if (levelTables.Contains(((SqlField)e).Table)) + { + if (isAggregated) + subQuery.GroupBy.Expr((SqlField)e); + ne = subQuery.Select.Columns[subQuery.Select.Add((SqlField)e)]; + break; + } + + break; + + case QueryElementType.Column : + if (replaced.TryGetValue(e, out ne)) + return ne; + + if (levelTables.Contains(((SqlQuery.Column)e).Parent)) + { + if (isAggregated) + subQuery.GroupBy.Expr((SqlQuery.Column)e); + ne = subQuery.Select.Columns[subQuery.Select.Add((SqlQuery.Column)e)]; + break; + } + + break; + } + + if (!ReferenceEquals(e, ne)) + replaced.Add(e, ne); + + return ne; + }); + + if (nc != null && !ReferenceEquals(nc, cond)) + { + modified = true; + + join.JoinedTable.Condition.Conditions.Add(nc); + subQuery.Where.SearchCondition.Conditions.RemoveAt(j); + j--; + } + } + + if (modified || isAggregated) + { + if (isCount && !query.GroupBy.IsEmpty) + { + var oldFunc = (SqlFunction)subQuery.Select.Columns[0].Expression; + + subQuery.Select.Columns.RemoveAt(0); + + query.Select.Columns[i] = new SqlQuery.Column( + query, + new SqlFunction(oldFunc.SystemType, oldFunc.Name, subQuery.Select.Columns[0])); + } + else if (isAggregated && !query.GroupBy.IsEmpty) + { + var oldFunc = (SqlFunction)subQuery.Select.Columns[0].Expression; + + subQuery.Select.Columns.RemoveAt(0); + + var idx = subQuery.Select.Add(oldFunc.Parameters[0]); + + query.Select.Columns[i] = new SqlQuery.Column( + query, + new SqlFunction(oldFunc.SystemType, oldFunc.Name, subQuery.Select.Columns[idx])); + } + else + { + query.Select.Columns[i] = new SqlQuery.Column(query, subQuery.Select.Columns[0]); + } + + dic.Add(col, query.Select.Columns[i]); + } + } + } + }); + + sqlQuery = new QueryVisitor().Convert(sqlQuery, e => + { + IQueryElement ne; + return dic.TryGetValue(e, out ne) ? ne : e; + }); + + return sqlQuery; + } + + public virtual ISqlExpression GetIdentityExpression(SqlTable table, SqlField identityField, bool forReturning) + { + return null; + } + + private string _name; + public virtual string Name + { + get { return _name ?? (_name = GetType().Name.Replace("SqlProvider", "")); } + } + + #endregion + + #region Linq Support + + public virtual LambdaExpression ConvertMember(MemberInfo mi) + { + return Expressions.ConvertMember(Name, mi); + } + + #endregion + } +}