pub/bltoolkit: Source/Data/Sql/SqlProvider/BasicSqlProvider.cs comparison

comparison Source/Data/Sql/SqlProvider/BasicSqlProvider.cs @ 0:f990fcb411a9

Копия текущей версии из github

author	cin
date	Thu, 27 Mar 2014 21:46:09 +0400
parents
children

comparison

equal deleted inserted replaced

--1:000000000000
+:f990fcb411a9
+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
+	}
+}

Mercurial > pub > bltoolkit

comparison Source/Data/Sql/SqlProvider/BasicSqlProvider.cs @ 0:f990fcb411a9