static RoutinesTable() {
Table = new RealSqlTable("INFORMATION_SCHEMA.ROUTINES");
SpecificName = new SqlColumn("SPECIFIC_NAME", Table);
RoutineSchema = new SqlColumn("ROUTINE_SCHEMA", Table);
RoutineType = new SqlColumn("ROUTINE_TYPE", Table);
}
static DbColumns() {
Table = new RealSqlTable("INFORMATION_SCHEMA.Columns");
TableName = new SqlColumn("TABLE_NAME", Table);
TableSchema = new SqlColumn("TABLE_SCHEMA", Table);
ColumnName = new SqlColumn("COLUMN_NAME", Table);
}
private static string GetColumnName(SqlColumn c) {
#if DEBUG
return c.Text;
#else
return c.Name;
#endif
}
public void ImplicitConversion_WithSortOrder_ReturnsSort()
{
var column = new SqlColumn("[dbo].[Users].[Id]");
var sort = column + SqlSortOrder.Descending;
Assert.NotNull(sort);
Assert.Same(column, sort.Column);
Assert.Equal(SqlSortOrder.Descending, sort.SortOrder);
}
public bool Equals(SqlColumn obj) {
if (ReferenceEquals(null, obj)) {
return false;
}
if (ReferenceEquals(this, obj)) {
return true;
}
return base.Equals(obj) && Equals(obj.Name, Name) && Equals(obj.Table, Table);
}
public void Ctor_WithColumnName_SetsColumnNameProperty()
{
var column = new SqlColumn("[dbo].[Users].[Id]");
Assert.NotNull(column.ColumnName);
Assert.Equal("dbo", column.ColumnName.Segments[0]);
Assert.Equal("Users", column.ColumnName.Segments[1]);
Assert.Equal("Id", column.ColumnName.Segments[2]);
}
internal override SqlRow VisitRow(SqlRow row)
{
for (int i = 0, n = row.Columns.Count; i < n; i++) {
row.Columns[i].Expression = this.VisitExpression(row.Columns[i].Expression);
if (this.hasRowNumber) {
this.CurrentColumn = row.Columns[i];
break;
}
}
return row;
}
public void Equal_WithLeftAndRightOperand_ReturnsBinaryExpression()
{
var left = new SqlColumn("dbo.Users.Id");
var right = new SqlConstant(5);
var expression = SqlExpression.Equal(left, right);
Assert.NotNull(expression.Left);
Assert.Same(left, expression.Left);
Assert.Equal(SqlBinaryOperator.Equal, expression.Operator);
Assert.NotNull(expression.Right);
Assert.Same(right, expression.Right);
}
private static string GetDataType(SqlColumn sqlColumn)
{
switch (sqlColumn.DataTypeName)
{
case "bit":
return("bool");
case "tinyint":
return("byte");
case "smallint":
return("short");
case "int":
return("int");
case "bigint":
return("long");
case "real":
return("float");
case "float":
return("double");
case "decimal":
case "money":
case "smallmoney":
return("decimal");
case "time":
return("TimeSpan");
case "date":
case "datetime":
case "datetime2":
case "smalldatetime":
return("DateTime");
case "datetimeoffset":
return("DateTimeOffset");
case "char":
case "varchar":
case "nchar":
case "nvarchar":
case "text":
case "ntext":
case "xml":
return("string");
case "binary":
case "image":
case "varbinary":
case "timestamp":
return("byte[]");
case "uniqueidentifier":
return("Guid");
case "variant":
case "Udt":
return("object");
case "Structured":
return("DataTable");
case "geography":
return("geography");
default:
// Fallback to be manually handled by user
return(sqlColumn.DataTypeName);
}
}
private void GetChildProperty(object entity, PropertyInfo[] propertyInfos, object property, SqlColumn column, SqlValue val)
{
for (int n = 0; n < propertyInfos.Length; n++)
{
//A.B.C 最后一个C是属性，前面的是类
var classInfo = column.Name.Split('.');
string childFirstClass = classInfo[0];
string childField = classInfo[classInfo.Length - 1];
object childValue = GetValue(column, val);
if (classInfo.Length > 2)
{
for (int m = 0; m < classInfo.Length - 1; m++)
{
string childClass1 = classInfo[m];
if (propertyInfos[n].Name.ToLower() == childClass1.ToLower())
{
var childType = propertyInfos[n].PropertyType;//myclass
var childEntityClass = childType.Assembly.CreateInstance(childType.FullName, true);
var popchilds = childType.GetProperties(BindingFlags.Instance | BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.NonPublic);
propertyInfos[n].SetValue(entity, childEntityClass);
GetChildProperty(childEntityClass, popchilds, property, column, val);
}
}
}
else
{
if (propertyInfos[n].Name.ToLower() == childFirstClass.ToLower())
{
var childType = propertyInfos[n].PropertyType;
var childEntityClass = childType.Assembly.CreateInstance(childType.FullName, true);
var popchilds = childType.GetProperties(BindingFlags.Instance | BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.NonPublic);
var popchild = popchilds.First(t => t.Name.ToLower() == childField.ToLower());
var emitSetter = EmitHelper.CreatePropertiesFunc(popchilds);
popchild.SetValue(childEntityClass, childValue);
propertyInfos[n].SetValue(entity, childEntityClass);
}
}
}
}
private static void AssertColumnSqlOptions(string expectedColumnName, SqlDbType expectedDataType, bool expectedAllowNull, bool expectedNonClusteredIndex, SqlColumn actualColumn)
{
Assert.Equal(expectedColumnName, actualColumn.ColumnName);
Assert.Equal(expectedDataType, actualColumn.DataType);
Assert.Equal(expectedAllowNull, actualColumn.AllowNull);
Assert.Equal(expectedNonClusteredIndex, actualColumn.NonClusteredIndex);
}
public abstract string GetColumnCreation(SqlColumn column, DocumenterHelper helper, string extraAnnotation, string comment);
/// <summary>
/// Visits the specified <see cref="SqlColumn"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlColumn expression)
{
}
private SqlColumn FindColumn(IEnumerable<SqlColumn> columns, SqlColumn col) {
foreach (SqlColumn c in columns) {
if (this.RefersToColumn(c, col)) {
return c;
}
}
return null;
}
protected override SqlColumn VisitSqlColumn(SqlColumn sqlColumn)
{
this.AppendColumn(sqlColumn);
return(sqlColumn);
}
public static ExcludedOperand Excluded(this SqlColumn self) => new ExcludedOperand(self);
public static EqualsOneOperator EqualsOne(this SqlColumn self, SqlColumn other) => Operand.From(self ?? throw new ArgumentException(nameof(self)))
public static TableColumnOperand Column(this SqlColumn self) => new TableColumnOperand(self);
public static SqlFilterOperand Property(this SqlColumn self) => new SqlFilterOperand(self);
/// <summary>
/// Visits the specified <see cref="SqlColumn"/>.
/// </summary>
/// <param name="expression">
/// The expression to visit.
/// </param>
public virtual void Visit(SqlColumn expression)
{
}
protected override void GenerateSelectSql_Where_Item(StringBuilder sb, IDataParameterCollection parameters, ref int parameterCounter, IConditionExpression conditionExpression, SqlColumn sqlColumn, bool includeTableNameAlias, IMappingProvider mappingProvider)
{
if (conditionExpression.ConditionOperator == ConditionOperator.FullTextLike || conditionExpression.ConditionOperator == ConditionOperator.NotFullTextLike)
{
if (string.IsNullOrWhiteSpace(sqlColumn.Column.FullTextSearch))
{
throw ExceptionFactory.FullTextSearchNotSupported(sqlColumn.Column.PropertyName);
}
string fulltextSearch = sqlColumn.Column.FullTextSearch;
string tag = conditionExpression.ConditionOperator == ConditionOperator.FullTextLike ? ">" : "<=";
switch (fulltextSearch)
{
case "contains":
sb.Append("contains(");
sb.Append(sqlColumn.TableAliasAndColumn);
sb.Append(",");
sb.Append(CreateParameterNameInStatement(parameterCounter));
sb.Append(",1)");
sb.Append(tag);
sb.Append("0");
break;
case "catsearch":
sb.Append("catsearch(");
sb.Append(sqlColumn.TableAliasAndColumn);
sb.Append(",");
sb.Append(CreateParameterNameInStatement(parameterCounter));
sb.Append(",null)");
sb.Append(tag);
sb.Append("0");
break;
default:
//function({column},{parameter},1)>0
string s = fulltextSearch.Replace("{column}", sqlColumn.TableAliasAndColumn);
s = s.Replace("{parameter}", CreateParameterNameInStatement(parameterCounter));
sb.Append(s);
break;
}
AddParameter(parameters, ref parameterCounter, conditionExpression.ConditionValue, sqlColumn.Column.PropertyType, sqlColumn.Column.Size);
}
else
{
base.GenerateSelectSql_Where_Item(sb, parameters, ref parameterCounter, conditionExpression, sqlColumn, includeTableNameAlias, mappingProvider);
}
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pkColumn">需要指定主键列</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
public virtual SqlSelect ModifyToPagingTree(SqlSelect raw, SqlColumn pkColumn, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
raw.Top = pagingInfo.PageSize;
return(raw);
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
var excludeSelect = new SqlSelect
{
Top = (pagingInfo.PageNumber - 1) * pagingInfo.PageSize,
Selection = pkColumn,
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Top = pagingInfo.PageSize,
Selection = raw.Selection,
From = raw.From,
OrderBy = raw.OrderBy,
};
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return(res);
}
internal bool RefersToColumn(SqlExpression exp, SqlColumn col) {
#if DEBUG
try {
refersDepth++;
System.Diagnostics.Debug.Assert(refersDepth < 20);
#endif
if (exp != null) {
switch (exp.NodeType) {
case SqlNodeType.Column:
return exp == col || this.RefersToColumn(((SqlColumn)exp).Expression, col);
case SqlNodeType.ColumnRef:
SqlColumnRef cref = (SqlColumnRef)exp;
return cref.Column == col || this.RefersToColumn(cref.Column.Expression, col);
case SqlNodeType.ExprSet:
SqlExprSet set = (SqlExprSet)exp;
for (int i = 0, n = set.Expressions.Count; i < n; i++) {
if (this.RefersToColumn(set.Expressions[i], col)) {
return true;
}
}
break;
case SqlNodeType.OuterJoinedValue:
return this.RefersToColumn(((SqlUnary)exp).Operand, col);
}
}
return false;
#if DEBUG
}
finally {
refersDepth--;
}
#endif
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
private ISqlSelect ModifyToPagingTree_With_NotIn(SqlSelect raw, PagingInfo pagingInfo)
{
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* 转换分页后：
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
//先要找到主表的 PK，分页时需要使用此主键列来生成分页 Sql。
//这里约定 Id 为主键列名。
var finder = new FirstTableFinder();
var pkTable = finder.Find(raw.From);
var pkColumn = new SqlColumn {
Table = pkTable, ColumnName = EntityConvention.IdColumnName
};
//先生成内部的 Select
var excludeSelect = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " "
},
pkColumn
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
OrderBy = raw.OrderBy,
};
//where
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return(res);
}
internal override SqlExpression VisitColumn(SqlColumn col) {
SqlColumn c = this.FindColumn(this.row.Columns, col);
if (c == null && col.Expression != null && !this.isInput && (!this.isNew || (this.isNew && !col.Expression.IsConstantColumn))) {
c = this.FindColumnWithExpression(this.row.Columns, col.Expression);
}
if (c == null) {
this.row.Columns.Add(col);
c = col;
}
else if (c != col) {
// preserve expr-sets when folding expressions together
if (col.Expression.NodeType == SqlNodeType.ExprSet && c.Expression.NodeType != SqlNodeType.ExprSet) {
c.Expression = col.Expression;
}
this.map[col] = c;
}
return new SqlColumnRef(c);
}
protected SqlColumnProperty(SqlColumn sqlColumn)
{
SqlColumn = sqlColumn;
}
private void PushDown(SqlColumn column) {
SqlSelect select = new SqlSelect(new SqlNop(column.ClrType, column.SqlType, column.SourceExpression), this.currentSelect.From, this.currentSelect.SourceExpression);
this.currentSelect.From = new SqlAlias(select);
select.Row.Columns.Add(column);
}
private SqlCondition(SqlColumn column, SqlComparasionOperator comparativeOperator, object value)
{
Column = column;
ComparativeOperator = comparativeOperator;
Value = value;
}
internal override SqlExpression VisitColumn(SqlColumn c) {
if (!this.isDebugMode) {
throw Error.InvalidFormatNode("Column");
}
sb.Append("COLUMN(");
if (c.Expression != null) {
this.Visit(c.Expression);
}
else {
string aliasName = null;
if (c.Alias != null) {
if (c.Alias.Name == null) {
if (!this.names.TryGetValue(c.Alias, out aliasName)) {
aliasName = "A" + this.names.Count;
this.names[c.Alias] = aliasName;
}
} else {
aliasName = c.Alias.Name;
}
}
sb.Append(aliasName);
sb.Append(".");
sb.Append(c.Name);
}
sb.Append(")");
return c;
}
public static void SetParamValue(DbParameter colParam, SqlColumn column, List <Func <DbParameter, string, bool> > stringParsers)
{
if (column.Value == null)
{
colParam.Value = DBNull.Value;
}
else
{
switch (column.Type & BlittableJsonReaderBase.TypesMask)
{
case BlittableJsonToken.Null:
colParam.Value = DBNull.Value;
break;
case BlittableJsonToken.Boolean:
case BlittableJsonToken.Integer:
colParam.Value = column.Value;
break;
case BlittableJsonToken.LazyNumber:
colParam.Value = (double)(LazyNumberValue)column.Value;
break;
case BlittableJsonToken.String:
SetParamStringValue(colParam, ((LazyStringValue)column.Value).ToString(), stringParsers);
break;
case BlittableJsonToken.CompressedString:
SetParamStringValue(colParam, ((LazyCompressedStringValue)column.Value).ToString(), stringParsers);
break;
case BlittableJsonToken.StartObject:
var objectValue = (BlittableJsonReaderObject)column.Value;
if (objectValue.Count >= 2)
{
if (objectValue.TryGetMember(nameof(SqlDocumentTransformer.VarcharFunctionCall.Type), out object dbType) &&
objectValue.TryGetMember(nameof(SqlDocumentTransformer.VarcharFunctionCall.Value), out object fieldValue))
{
colParam.DbType = (DbType)Enum.Parse(typeof(DbType), dbType.ToString(), false);
colParam.Value = fieldValue.ToString();
if (objectValue.TryGetMember(nameof(SqlDocumentTransformer.VarcharFunctionCall.Size), out object size))
{
colParam.Size = (int)(long)size;
}
break;
}
}
colParam.Value = objectValue.ToString();
break;
case BlittableJsonToken.StartArray:
var blittableJsonReaderArray = (BlittableJsonReaderArray)column.Value;
colParam.Value = blittableJsonReaderArray.ToString();
break;
default:
{
if (column.Value is Stream stream)
{
colParam.DbType = DbType.Binary;
if (stream == Stream.Null)
{
colParam.Value = DBNull.Value;
}
else
{
colParam.Value = stream.ReadData();
}
break;
}
throw new InvalidOperationException("Cannot understand how to save " + column.Type + " for " + colParam.ParameterName);
}
}
}
}
public ColumnAndOrder(SqlColumn sqlColumn, AscDesc order)
: this(order)
{
SqlColumn = sqlColumn;
}
protected new TBuilder OrderBy(SqlColumn sqlColumn, OrderDirection direction) => (TBuilder)base.OrderBy(sqlColumn, direction);
public static List <IdentityMigration> CompareIdentity(SqlColumn columnOriginal, SqlColumn columnNew)
{
var comparer = new ComparerIdentity();
var changes = comparer.CompareProperties(columnOriginal, columnNew);
return(changes);
}
internal override SqlTable VisitTable(SqlTable tab) {
foreach (SqlColumn c in tab.Columns) {
if (c == this.match) {
if (this.found != null)
throw Error.ColumnIsDefinedInMultiplePlaces(GetColumnName(this.match));
this.found = c;
break;
}
}
return tab;
}
/// <summary>
/// Sets an existing column as an FK, pointing to the <paramref name="referredColumnName"/> of <paramref name="referredTableName"/>, with the Nocheck property.
/// Note <paramref name="referredColumnName"/> has to be a unique key.
/// </summary>
/// <param name="singleFkColumn">The existing column to set as FK.</param>
/// <param name="referredTableName">The name of the referred table.</param>
/// <param name="referredColumnName">The name of the referred column.</param>
/// <param name="fkName"></param>
/// <returns>The original <paramref name="singleFkColumn"/>.</returns>
public static SqlColumn SetForeignKeyToColumnNoCheck(this SqlColumn singleFkColumn, string referredTableName, string referredColumnName, string fkName = null)
{
var property = new SqlEngineVersionSpecificProperty(MsSqlVersion.MsSql2016, "Nocheck", "true");
return(singleFkColumn.SetForeignKeyToColumn(referredTableName, referredColumnName, property, fkName));
}
private void ForceLocal(SqlRow row, string name) {
bool isLocal = false;
// check to see if it already exists locally
foreach (SqlColumn c in row.Columns) {
if (this.RefersToColumn(c, this.found)) {
this.found = c;
isLocal = true;
break;
}
}
if (!isLocal) {
// need to put this in the local projection list to bubble it up
SqlColumn c = new SqlColumn(found.ClrType, found.SqlType, name, this.found.MetaMember, new SqlColumnRef(this.found), row.SourceExpression);
row.Columns.Add(c);
this.found = c;
}
}
internal override SqlRow VisitRow(SqlRow row) {
foreach (SqlColumn c in row.Columns) {
if (this.RefersToColumn(c, this.match)) {
if (this.found != null) {
throw Error.ColumnIsDefinedInMultiplePlaces(GetColumnName(this.match));
}
this.found = c;
break;
}
}
return row;
}
private void AppendColumnUsage(SqlColumn sqlColumn)
{
this.QuoteAppend(sqlColumn.Table.GetName());
_sql.Append(".");
this.QuoteAppend(sqlColumn.ColumnName);
}
internal override SqlExpression VisitTableValuedFunctionCall(SqlTableValuedFunctionCall fc) {
foreach (SqlColumn c in fc.Columns) {
if (c == this.match) {
if (this.found != null)
throw Error.ColumnIsDefinedInMultiplePlaces(GetColumnName(this.match));
this.found = c;
break;
}
}
return fc;
}
internal override SqlExpression VisitColumn(SqlColumn col) {
return new SqlColumnRef(col);
}
internal virtual SqlExpression VisitColumn(SqlColumn col) {
col.Expression = this.VisitExpression(col.Expression);
return col;
}
private SqlNode AccessMember(SqlMember m, SqlExpression expo) {
SqlExpression exp = expo;
switch (exp.NodeType) {
case SqlNodeType.ClientCase: {
// Distribute into each case.
SqlClientCase sc = (SqlClientCase)exp;
Type newClrType = null;
List<SqlExpression> matches = new List<SqlExpression>();
List<SqlExpression> values = new List<SqlExpression>();
foreach (SqlClientWhen when in sc.Whens) {
SqlExpression newValue = (SqlExpression)AccessMember(m, when.Value);
if (newClrType == null) {
newClrType = newValue.ClrType;
}
else if (newClrType != newValue.ClrType) {
throw Error.ExpectedClrTypesToAgree(newClrType, newValue.ClrType);
}
matches.Add(when.Match);
values.Add(newValue);
}
SqlExpression result = sql.Case(newClrType, sc.Expression, matches, values, sc.SourceExpression);
return result;
}
case SqlNodeType.SimpleCase: {
// Distribute into each case.
SqlSimpleCase sc = (SqlSimpleCase)exp;
Type newClrType = null;
List<SqlExpression> newMatches = new List<SqlExpression>();
List<SqlExpression> newValues = new List<SqlExpression>();
foreach (SqlWhen when in sc.Whens) {
SqlExpression newValue = (SqlExpression)AccessMember(m, when.Value);
if (newClrType == null) {
newClrType = newValue.ClrType;
}
else if (newClrType != newValue.ClrType) {
throw Error.ExpectedClrTypesToAgree(newClrType, newValue.ClrType);
}
newMatches.Add(when.Match);
newValues.Add(newValue);
}
SqlExpression result = sql.Case(newClrType, sc.Expression, newMatches, newValues, sc.SourceExpression);
return result;
}
case SqlNodeType.SearchedCase: {
// Distribute into each case.
SqlSearchedCase sc = (SqlSearchedCase)exp;
List<SqlWhen> whens = new List<SqlWhen>(sc.Whens.Count);
foreach (SqlWhen when in sc.Whens) {
SqlExpression value = (SqlExpression)AccessMember(m, when.Value);
whens.Add(new SqlWhen(when.Match, value));
}
SqlExpression @else = (SqlExpression)AccessMember(m, sc.Else);
return sql.SearchedCase(whens.ToArray(), @else, sc.SourceExpression);
}
case SqlNodeType.TypeCase: {
// We don't allow derived types to map members to different database fields.
// Therefore, just pick the best SqlNew to call AccessMember on.
SqlTypeCase tc = (SqlTypeCase)exp;
// Find the best type binding for this member.
SqlNew tb = tc.Whens[0].TypeBinding as SqlNew;
foreach (SqlTypeCaseWhen when in tc.Whens) {
if (when.TypeBinding.NodeType == SqlNodeType.New) {
SqlNew sn = (SqlNew)when.TypeBinding;
if (m.Member.DeclaringType.IsAssignableFrom(sn.ClrType)) {
tb = sn;
break;
}
}
}
return AccessMember(m, tb);
}
case SqlNodeType.AliasRef: {
// convert alias.Member => column
SqlAliasRef aref = (SqlAliasRef)exp;
// if its a table, find the matching column
SqlTable tab = aref.Alias.Node as SqlTable;
if (tab != null) {
MetaDataMember mm = GetRequiredInheritanceDataMember(tab.RowType, m.Member);
System.Diagnostics.Debug.Assert(mm != null);
string name = mm.MappedName;
SqlColumn c = tab.Find(name);
if (c == null) {
ProviderType sqlType = sql.Default(mm);
c = new SqlColumn(m.ClrType, sqlType, name, mm, null, m.SourceExpression);
c.Alias = aref.Alias;
tab.Columns.Add(c);
}
return new SqlColumnRef(c);
}
// if it is a table valued function, find the matching result column
SqlTableValuedFunctionCall fc = aref.Alias.Node as SqlTableValuedFunctionCall;
if (fc != null) {
MetaDataMember mm = GetRequiredInheritanceDataMember(fc.RowType, m.Member);
System.Diagnostics.Debug.Assert(mm != null);
string name = mm.MappedName;
SqlColumn c = fc.Find(name);
if (c == null) {
ProviderType sqlType = sql.Default(mm);
c = new SqlColumn(m.ClrType, sqlType, name, mm, null, m.SourceExpression);
c.Alias = aref.Alias;
fc.Columns.Add(c);
}
return new SqlColumnRef(c);
}
break;
}
case SqlNodeType.OptionalValue:
// convert option(exp).Member => exp.Member
return this.AccessMember(m, ((SqlOptionalValue)exp).Value);
case SqlNodeType.OuterJoinedValue: {
SqlNode n = this.AccessMember(m, ((SqlUnary)exp).Operand);
SqlExpression e = n as SqlExpression;
if (e != null) return sql.Unary(SqlNodeType.OuterJoinedValue, e);
return n;
}
case SqlNodeType.Lift:
return this.AccessMember(m, ((SqlLift)exp).Expression);
case SqlNodeType.UserRow: {
// convert UserRow.Member => UserColumn
SqlUserRow row = (SqlUserRow)exp;
SqlUserQuery suq = row.Query;
MetaDataMember mm = GetRequiredInheritanceDataMember(row.RowType, m.Member);
System.Diagnostics.Debug.Assert(mm != null);
string name = mm.MappedName;
SqlUserColumn c = suq.Find(name);
if (c == null) {
ProviderType sqlType = sql.Default(mm);
c = new SqlUserColumn(m.ClrType, sqlType, suq, name, mm.IsPrimaryKey, m.SourceExpression);
suq.Columns.Add(c);
}
return c;
}
case SqlNodeType.New: {
// convert (new {Member = expr}).Member => expr
SqlNew sn = (SqlNew)exp;
SqlExpression e = sn.Find(m.Member);
if (e != null) {
return e;
}
MetaDataMember mm = sn.MetaType.PersistentDataMembers.FirstOrDefault(p => p.Member == m.Member);
if (!sn.SqlType.CanBeColumn && mm != null) {
throw Error.MemberNotPartOfProjection(m.Member.DeclaringType, m.Member.Name);
}
break;
}
case SqlNodeType.Element:
case SqlNodeType.ScalarSubSelect: {
// convert Scalar/Element(select exp).Member => Scalar/Element(select exp.Member) / select exp.Member
SqlSubSelect sub = (SqlSubSelect)exp;
SqlAlias alias = new SqlAlias(sub.Select);
SqlAliasRef aref = new SqlAliasRef(alias);
SqlSelect saveSelect = this.currentSelect;
try {
SqlSelect newSelect = new SqlSelect(aref, alias, sub.SourceExpression);
this.currentSelect = newSelect;
SqlNode result = this.Visit(sql.Member(aref, m.Member));
SqlExpression rexp = result as SqlExpression;
if (rexp != null) {
// If the expression is still a Member after being visited, but it cannot be a column, then it cannot be collapsed
// into the SubSelect because we need to keep track of the fact that this member has to be accessed on the client.
// This must be done after the expression has been Visited above, because otherwise we don't have
// enough context to know if the member can be a column or not.
if (rexp.NodeType == SqlNodeType.Member && !SqlColumnizer.CanBeColumn(rexp)) {
// If the original member expression is an Element, optimize it by converting to an OuterApply if possible.
// We have to do this here because we are creating a new member expression based on it, and there are no
// subsequent visitors that will do this optimization.
if (this.canUseOuterApply && exp.NodeType == SqlNodeType.Element && this.currentSelect != null) {
// Reset the currentSelect since we are not going to use the previous SqlSelect that was created
this.currentSelect = saveSelect;
this.currentSelect.From = sql.MakeJoin(SqlJoinType.OuterApply, this.currentSelect.From, alias, null, sub.SourceExpression);
exp = this.VisitExpression(aref);
}
return sql.Member(exp, m.Member);
}
// Since we are going to make a SubSelect out of this member expression, we need to make
// sure it gets columnized before it gets to the PostBindDotNetConverter, otherwise only the
// entire SubSelect will be columnized as a whole. Subsequent columnization does not know how to handle
// any function calls that may be produced by the PostBindDotNetConverter, but we know how to handle it here.
newSelect.Selection = rexp;
newSelect.Selection = this.columnizer.ColumnizeSelection(newSelect.Selection);
newSelect.Selection = this.ConvertLinks(newSelect.Selection);
SqlNodeType subType = (rexp is SqlTypeCase || !rexp.SqlType.CanBeColumn) ? SqlNodeType.Element : SqlNodeType.ScalarSubSelect;
SqlSubSelect subSel = sql.SubSelect(subType, newSelect);
return this.FoldSubquery(subSel);
}
SqlSelect rselect = result as SqlSelect;
if (rselect != null) {
SqlAlias ralias = new SqlAlias(rselect);
SqlAliasRef rref = new SqlAliasRef(ralias);
newSelect.Selection = this.ConvertLinks(this.VisitExpression(rref));
newSelect.From = new SqlJoin(SqlJoinType.CrossApply, alias, ralias, null, m.SourceExpression);
return newSelect;
}
throw Error.UnexpectedNode(result.NodeType);
}
finally {
this.currentSelect = saveSelect;
}
}
case SqlNodeType.Value: {
SqlValue val = (SqlValue)exp;
if (val.Value == null) {
return sql.Value(m.ClrType, m.SqlType, null, val.IsClientSpecified, m.SourceExpression);
}
else if (m.Member is PropertyInfo) {
PropertyInfo p = (PropertyInfo)m.Member;
return sql.Value(m.ClrType, m.SqlType, p.GetValue(val.Value, null), val.IsClientSpecified, m.SourceExpression);
}
else {
FieldInfo f = (FieldInfo)m.Member;
return sql.Value(m.ClrType, m.SqlType, f.GetValue(val.Value), val.IsClientSpecified, m.SourceExpression);
}
}
case SqlNodeType.Grouping: {
SqlGrouping g = ((SqlGrouping)exp);
if (m.Member.Name == "Key") {
return g.Key;
}
break;
}
case SqlNodeType.ClientParameter: {
SqlClientParameter cp = (SqlClientParameter)exp;
// create new accessor including this member access
LambdaExpression accessor =
Expression.Lambda(
typeof(Func<,>).MakeGenericType(typeof(object[]), m.ClrType),
Expression.MakeMemberAccess(cp.Accessor.Body, m.Member),
cp.Accessor.Parameters
);
return new SqlClientParameter(m.ClrType, m.SqlType, accessor, cp.SourceExpression);
}
default:
break;
}
if (m.Expression == exp) {
return m;
}
else {
return sql.Member(exp, m.Member);
}
}
private SqlNode VisitAggregate(Expression sequence, LambdaExpression lambda, SqlNodeType aggType, Type returnType) {
// Convert seq.Agg(exp) into
//
// 1) SELECT Agg(exp) FROM seq
// 2) SELECT Agg1 FROM (SELECT Agg(exp) as Agg1 FROM group-seq GROUP BY ...)
// 3) SCALAR(SELECT Agg(exp) FROM seq)
//
bool isCount = aggType == SqlNodeType.Count || aggType == SqlNodeType.LongCount;
SqlNode source = this.Visit(sequence);
SqlSelect select = this.CoerceToSequence(source);
SqlAlias alias = new SqlAlias(select);
SqlAliasRef aref = new SqlAliasRef(alias);
// If the sequence is of the form x.Select(expr).Agg() and the lambda for the aggregate is null,
// or is a no-op parameter expression (like u=>u), clone the group by selection lambda
// expression, and use for the aggregate.
// Final form should be x.Agg(expr)
MethodCallExpression mce = sequence as MethodCallExpression;
if (!outerNode && !isCount && (lambda == null || (lambda.Parameters.Count == 1 && lambda.Parameters[0] == lambda.Body)) &&
(mce != null) && IsSequenceOperatorCall(mce, "Select") && select.From is SqlAlias) {
LambdaExpression selectionLambda = GetLambda(mce.Arguments[1]);
lambda = Expression.Lambda(selectionLambda.Type, selectionLambda.Body, selectionLambda.Parameters);
alias = (SqlAlias)select.From;
aref = new SqlAliasRef(alias);
}
if (lambda != null && !TypeSystem.IsSimpleType(lambda.Body.Type)) {
throw Error.CannotAggregateType(lambda.Body.Type);
}
//Empty parameter aggregates are not allowed on anonymous types
//i.e. db.Customers.Select(c=>new{c.Age}).Max() instead it should be
// db.Customers.Select(c=>new{c.Age}).Max(c=>c.Age)
if (select.Selection.SqlType.IsRuntimeOnlyType && !IsGrouping(sequence.Type) && !isCount && lambda == null) {
throw Error.NonCountAggregateFunctionsAreNotValidOnProjections(aggType);
}
if (lambda != null)
this.map[lambda.Parameters[0]] = aref;
if (this.outerNode) {
// If this aggregate is basically the last/outer-most operator of the query
//
// produce SELECT Agg(exp) FROM seq
//
SqlExpression exp = (lambda != null) ? this.VisitExpression(lambda.Body) : null;
SqlExpression where = null;
if (isCount && exp != null) {
where = exp;
exp = null;
}
else if (exp == null && !isCount) {
exp = aref;
}
if (exp != null) {
// in case this contains another aggregate
exp = new SqlSimpleExpression(exp);
}
SqlSelect sel = new SqlSelect(
this.GetAggregate(aggType, returnType, exp),
alias,
this.dominatingExpression
);
sel.Where = where;
sel.OrderingType = SqlOrderingType.Never;
return sel;
}
else if (!isCount || lambda == null) {
// Look to optimize aggregate by pushing its evaluation down to the select node that has the
// actual group-by operator.
//
// Produce: SELECT Agg1 FROM (SELECT Agg(exp) as Agg1 FROM seq GROUP BY ...)
//
GroupInfo info = this.FindGroupInfo(source);
if (info != null) {
SqlExpression exp = null;
if (lambda != null) {
// evaluate expression relative to the group-by select node
this.map[lambda.Parameters[0]] = (SqlExpression)SqlDuplicator.Copy(info.ElementOnGroupSource);
exp = this.VisitExpression(lambda.Body);
} else if (!isCount) {
// support aggregates w/o an explicit selector specified
exp = info.ElementOnGroupSource;
}
if (exp != null) {
// in case this contains another aggregate
exp = new SqlSimpleExpression(exp);
}
SqlExpression agg = this.GetAggregate(aggType, returnType, exp);
SqlColumn c = new SqlColumn(agg.ClrType, agg.SqlType, null, null, agg, this.dominatingExpression);
info.SelectWithGroup.Row.Columns.Add(c);
return new SqlColumnRef(c);
}
}
// Otherwise, if we cannot optimize then fall back to generating a nested aggregate in a correlated sub query
//
// SCALAR(SELECT Agg(exp) FROM seq)
{
SqlExpression exp = (lambda != null) ? this.VisitExpression(lambda.Body) : null;
if (exp != null) {
// in case this contains another aggregate
exp = new SqlSimpleExpression(exp);
}
SqlSelect sel = new SqlSelect(
this.GetAggregate(aggType, returnType, isCount ? null : (lambda == null) ? aref : exp),
alias,
this.dominatingExpression
);
sel.Where = isCount ? exp : null;
return sql.SubSelect(SqlNodeType.ScalarSubSelect, sel);
}
}
internal override SqlExpression VisitColumn(SqlColumn col)
{
VisitAliasConsumed(col.Alias);
VisitExpression(col.Expression);
return(col);
}
// add a new sub query that projects the given expression
private SqlExpression PushDownExpression(SqlExpression expr) {
// make sure this expression was columnized like a selection
if (expr.NodeType == SqlNodeType.Value && expr.SqlType.CanBeColumn) {
expr = new SqlColumn(expr.ClrType, expr.SqlType, null, null, expr, expr.SourceExpression);
}
else {
expr = this.columnizer.ColumnizeSelection(expr);
}
SqlSelect simple = new SqlSelect(expr, this.currentSelect.From, expr.SourceExpression);
this.currentSelect.From = new SqlAlias(simple);
// make a copy of the expression for the current scope
return this.ExpandExpression(expr);
}
private static bool NeedsNumericRange(SqlColumn sqlColumn)
{
var numericSqlColumn = sqlColumn.SqlDataType as SqlTypeNumberBase;
return(numericSqlColumn != null);
}
private SqlSelect GenerateSkipTake(SqlSelect sequence, SqlExpression skipExp, SqlExpression takeExp) {
SqlSelect select = this.LockSelect(sequence);
// no skip?
if (skipExp == null) {
if (takeExp != null) {
select.Top = takeExp;
}
return select;
}
SqlAlias alias = new SqlAlias(select);
SqlAliasRef aref = new SqlAliasRef(alias);
if (this.UseConverterStrategy(ConverterStrategy.SkipWithRowNumber)) {
// use ROW_NUMBER() (preferred)
SqlColumn rowNumber = new SqlColumn("ROW_NUMBER", sql.RowNumber(new List<SqlOrderExpression>(), this.dominatingExpression));
SqlColumnRef rowNumberRef = new SqlColumnRef(rowNumber);
select.Row.Columns.Add(rowNumber);
SqlSelect final = new SqlSelect(aref, alias, this.dominatingExpression);
if (takeExp != null) {
// use BETWEEN for skip+take combo (much faster)
final.Where = sql.Between(
rowNumberRef,
sql.Add(skipExp, 1),
sql.Binary(SqlNodeType.Add, (SqlExpression)SqlDuplicator.Copy(skipExp), takeExp),
this.dominatingExpression
);
}
else {
final.Where = sql.Binary(SqlNodeType.GT, rowNumberRef, skipExp);
}
return final;
}
else {
// Ensure that the sequence contains elements that can be skipped
if (!CanSkipOnSelection(select.Selection)) {
throw Error.SkipNotSupportedForSequenceTypes();
}
// use NOT EXISTS
// Supported cases:
// - Entities
// - Projections that contain all PK columns
//
// .. where there sequence can be traced back to a:
// - Single-table query
// - Distinct
// - Except
// - Intersect
// - Union, where union.All == false
// Not supported: joins
// Sequence should also be ordered, but we can't test for it at this
// point in processing, and we won't know that we need to test it, later.
SingleTableQueryVisitor stqv = new SingleTableQueryVisitor();
stqv.Visit(select);
if (!stqv.IsValid) {
throw Error.SkipRequiresSingleTableQueryWithPKs();
}
SqlSelect dupsel = (SqlSelect)SqlDuplicator.Copy(select);
dupsel.Top = skipExp;
SqlAlias dupAlias = new SqlAlias(dupsel);
SqlAliasRef dupRef = new SqlAliasRef(dupAlias);
SqlSelect eqsel = new SqlSelect(dupRef, dupAlias, this.dominatingExpression);
eqsel.Where = sql.Binary(SqlNodeType.EQ2V, aref, dupRef);
SqlSubSelect ss = sql.SubSelect(SqlNodeType.Exists, eqsel);
SqlSelect final = new SqlSelect(aref, alias, this.dominatingExpression);
final.Where = sql.Unary(SqlNodeType.Not, ss, this.dominatingExpression);
final.Top = takeExp;
return final;
}
}
private static bool NeedsCharRange(SqlColumn sqlColumn)
{
return(sqlColumn.SqlDataType.MaximumCharLength.HasValue && sqlColumn.SqlDataType.MaximumCharLength != -1);
}
internal override SqlExpression VisitColumn(SqlColumn col) {
SqlColumn n = new SqlColumn(col.ClrType, col.SqlType, col.Name, col.MetaMember, null, col.SourceExpression);
this.nodeMap[col] = n;
n.Expression = this.VisitExpression(col.Expression);
n.Alias = (SqlAlias)this.Visit(col.Alias);
return n;
}
/// <summary>
/// Returns a valid C# default value for the given SQL datatype.
/// </summary>
public static string GetCSharpDefaultValue(SqlColumn sql_column)
{
// do we have a non default value?
if (sql_column.DefaultValue != string.Empty)
{
switch (sql_column.SqlDataType)
{
case SqlDbType.BigInt: return(sql_column.DefaultValue);
//case SqlDbType.Binary: return "null";
case SqlDbType.Bit:
if (sql_column.DefaultValue == "1")
{
return("true");
}
else
{
return("false");
}
case SqlDbType.Char: return("\"" + sql_column.DefaultValue + "\"");
case SqlDbType.Date: return(sql_column.DefaultValue.ToLower() == "getdate()" ? "DateTime.Now;" : "DateTime.Parse(\"" + sql_column.DefaultValue + "\")");
case SqlDbType.DateTime: return(sql_column.DefaultValue.ToLower() == "getdate()" ? "DateTime.Now;" : "DateTime.Parse(\"" + sql_column.DefaultValue + "\")");
case SqlDbType.DateTime2: return(sql_column.DefaultValue.ToLower() == "getdate()" ? "DateTime.Now;" : "DateTime.Parse(\"" + sql_column.DefaultValue + "\")");
case SqlDbType.DateTimeOffset: return(sql_column.DefaultValue.ToLower() == "getdate()" ? "DateTime.Now;" : "DateTime.Parse(\"" + sql_column.DefaultValue + "\")");
case SqlDbType.Decimal: return(sql_column.DefaultValue);
case SqlDbType.Float: return(sql_column.DefaultValue);
//case SqlDbType.Image: return "null";
case SqlDbType.Int: return(sql_column.DefaultValue);
case SqlDbType.Money: return(sql_column.DefaultValue + "m");
case SqlDbType.NChar: return("\"" + sql_column.DefaultValue + "\"");
case SqlDbType.NText: return("\"" + sql_column.DefaultValue + "\"");
case SqlDbType.NVarChar: return("\"" + sql_column.DefaultValue + "\"");
case SqlDbType.Real: return(sql_column.DefaultValue + "f");
case SqlDbType.SmallDateTime: return("\"" + DateTime.MinValue.ToString() + "\"");
case SqlDbType.SmallInt: return(sql_column.DefaultValue);
case SqlDbType.SmallMoney: return(sql_column.DefaultValue + "f");
//case SqlDbType.Structured: return "null";
case SqlDbType.Text: return("string.Empty");
case SqlDbType.Time: return("DateTime.Now");
//case SqlDbType.Timestamp: return "string.Empty";
case SqlDbType.TinyInt: return(sql_column.DefaultValue);
//case SqlDbType.Udt: return "null";
case SqlDbType.UniqueIdentifier: return("Guid.Empty");
//case SqlDbType.VarBinary: return "null";
case SqlDbType.VarChar: return("\"" + sql_column.DefaultValue + "\"");
//case SqlDbType.Variant: return "null";
case SqlDbType.Xml: return("\"" + sql_column.DefaultValue + "\"");
default:
return("// NO DEFAULT AVAILABLE FOR " + sql_column.SqlDataType.ToString());
}
}
if (sql_column.IsNullable)
{
switch (sql_column.SqlDataType)
{
case SqlDbType.Variant:
return("// NO DEFAULT AVAILABLE FOR " + sql_column.SqlDataType.ToString());
default:
return("null");
}
}
else
{
switch (sql_column.SqlDataType)
{
case SqlDbType.BigInt: return("0");
case SqlDbType.Binary: return("null");
case SqlDbType.Bit: return("false");
case SqlDbType.Char: return("string.Empty");
case SqlDbType.Date: return("DateTime.Now");
case SqlDbType.DateTime: return("DateTime.Now");
case SqlDbType.DateTime2: return("DateTime.Now");
case SqlDbType.DateTimeOffset: return("DateTime.Now");
case SqlDbType.Decimal: return("0.0m");
case SqlDbType.Float: return("0.0d");
case SqlDbType.Image: return("null");
case SqlDbType.Int: return("0");
case SqlDbType.Money: return("0.0m");
case SqlDbType.NChar: return("string.Empty");
case SqlDbType.NText: return("string.Empty");
case SqlDbType.NVarChar: return("string.Empty");
case SqlDbType.Real: return("0.0f");
case SqlDbType.SmallDateTime: return("DateTime.Now");
case SqlDbType.SmallInt: return("0");
case SqlDbType.SmallMoney: return("0.0f");
case SqlDbType.Structured: return("null");
case SqlDbType.Text: return("string.Empty");
case SqlDbType.Time: return("DateTime.Now");
case SqlDbType.Timestamp: return("string.Empty");
case SqlDbType.TinyInt: return("byte.MinValue");
case SqlDbType.Udt: return("null");
case SqlDbType.UniqueIdentifier: return("Guid.Empty");
case SqlDbType.VarBinary: return("null");
case SqlDbType.VarChar: return("string.Empty");
case SqlDbType.Variant: return("null");
case SqlDbType.Xml: return("string.Empty");
default:
return("// NO DEFAULT AVAILABLE FOR " + sql_column.SqlDataType.ToString());
}
}
}
private SqlColumnRef MakeFlattenedColumn(SqlExpression expr, string name) {
SqlColumn c = (!this.isInput) ? this.FindColumnWithExpression(this.row.Columns, expr) : null;
if (c == null) {
c = new SqlColumn(expr.ClrType, expr.SqlType, name, null, expr, expr.SourceExpression);
this.row.Columns.Add(c);
}
return new SqlColumnRef(c);
}
/// <summary>
/// Returns the proper cast to C# type for the given SQL datatype.
/// Example: int -> Convert.ToInt32
/// </summary>
public static string GetCSharpCastString(SqlColumn sql_column)
{
//if (sql_column.IsNullable)
//{
// // TODO
// return string.Empty;
//}
//else
//{
switch (sql_column.SqlDataType)
{
case SqlDbType.BigInt: return("Convert.ToInt64");
//case SqlDbType.Binary: return Convert"null";
case SqlDbType.Bit: return("Convert.ToBoolean");
case SqlDbType.Char: return("Convert.ToChar");
case SqlDbType.Date: return("Convert.ToDateTime");
case SqlDbType.DateTime: return("Convert.ToDateTime");
case SqlDbType.DateTime2: return("Convert.ToDateTime");
case SqlDbType.DateTimeOffset: return("DateTime.Now");
case SqlDbType.Decimal: return("Convert.ToDecimal");
case SqlDbType.Float: return("Convert.ToDouble");
//case SqlDbType.Image: return "null";
case SqlDbType.Int: return("Convert.ToInt32");
case SqlDbType.Money: return("Convert.ToDecimal");
case SqlDbType.NChar: return("Convert.ToString");
case SqlDbType.NText: return("Convert.ToString");
case SqlDbType.NVarChar: return("Convert.ToString");
case SqlDbType.Real: return("Convert.ToDouble");
case SqlDbType.SmallDateTime: return("DateTime.Now");
case SqlDbType.SmallInt: return("Convert.ToInt16");
case SqlDbType.SmallMoney: return("Convert.ToDecimal");
//case SqlDbType.Structured: return "null";
case SqlDbType.Text: return("Convert.ToString");
case SqlDbType.Time: return("DateTime.Now");
//case SqlDbType.Timestamp: return "string.Empty";
case SqlDbType.TinyInt: return("byte.MinValue");
//case SqlDbType.Udt: return "null";
case SqlDbType.UniqueIdentifier: return("Convert.ToString");
//case SqlDbType.VarBinary: return "null";
case SqlDbType.VarChar: return("Convert.ToString");
//case SqlDbType.Variant: return "null";
case SqlDbType.Xml: return("Convert.ToString");
default:
return("// NO CONVERSION AVAILABLE FOR " + sql_column.SqlDataType.ToString());
}
//}
}
internal override SqlRowNumber VisitRowNumber(SqlRowNumber rowNumber) {
if (rowNumber.OrderBy.Count > 0) return rowNumber;
SqlDuplicator dup = new SqlDuplicator(true);
List<SqlOrderExpression> orderBy = new List<SqlOrderExpression>();
List<SqlOrderExpression> existingOrders = new List<SqlOrderExpression>();
if (this.rowNumberOrders != null && this.rowNumberOrders.Count != 0) {
existingOrders = new List<SqlOrderExpression>(this.rowNumberOrders);
}
else if (this.orders != null) {
existingOrders = new List<SqlOrderExpression>(this.orders);
}
foreach (SqlOrderExpression expr in existingOrders) {
if (!expr.Expression.IsConstantColumn) {
orderBy.Add(expr);
if (this.rowNumberOrders != null) {
this.rowNumberOrders.Remove(expr);
}
if (this.orders != null) {
this.orders.Remove(expr);
}
}
}
rowNumber.OrderBy.Clear();
if (orderBy.Count == 0) {
List<SqlColumn> columns = SqlGatherColumnsProduced.GatherColumns(this.currentSelect.From);
foreach (SqlColumn col in columns) {
if (col.Expression.SqlType.IsOrderable) {
orderBy.Add(new SqlOrderExpression(SqlOrderType.Ascending, new SqlColumnRef(col)));
}
}
if (orderBy.Count == 0) {
// insert simple column
SqlColumn col =
new SqlColumn(
"rowNumberOrder",
sql.Value(typeof(int), this.typeProvider.From(typeof(int)), 1, false, rowNumber.SourceExpression)
);
this.PushDown(col);
orderBy.Add(new SqlOrderExpression(SqlOrderType.Ascending, new SqlColumnRef(col)));
}
}
foreach (SqlOrderExpression sox in orderBy) {
rowNumber.OrderBy.Add(new SqlOrderExpression(sox.OrderType, (SqlExpression)dup.Duplicate(sox.Expression)));
}
return rowNumber;
}
/// <summary>
/// Returns the minimum C# value for the given SQL datatype.
/// </summary>
public static string GetCSharpMinValue(SqlColumn sql_column)
{
switch (sql_column.SqlDataType)
{
case SqlDbType.BigInt: return("long.MinValue");
case SqlDbType.Binary: return("null");
case SqlDbType.Bit: return("false");
case SqlDbType.Char: return("string.Empty");
case SqlDbType.Date: return("DateTime.MinValue");
case SqlDbType.DateTime: return("DateTime.MinValue");
case SqlDbType.DateTime2: return("DateTime.MinValue");
case SqlDbType.DateTimeOffset: return("DateTime.MinValue");
case SqlDbType.Decimal: return("decimal.MinValue");
case SqlDbType.Float: return("double.MinValue");
case SqlDbType.Image: return("null");
case SqlDbType.Int: return("int.MinValue");
case SqlDbType.Money: return("decimal.MinValue");
case SqlDbType.NChar: return("string.Empty");
case SqlDbType.NText: return("string.Empty");
case SqlDbType.NVarChar: return("string.Empty");
case SqlDbType.Real: return("float.MinValue");
case SqlDbType.SmallDateTime: return("DateTime.Now");
case SqlDbType.SmallInt: return("int.MinValue");
case SqlDbType.SmallMoney: return("float.MinValue");
case SqlDbType.Structured: return("null");
case SqlDbType.Text: return("string.Empty");
case SqlDbType.Time: return("DateTime.MinValue");
case SqlDbType.Timestamp: return("string.Empty");
case SqlDbType.TinyInt: return("byte.MinValue");
case SqlDbType.Udt: return("null");
case SqlDbType.UniqueIdentifier: return("Guid.Empty");
case SqlDbType.VarBinary: return("null");
case SqlDbType.VarChar: return("string.Empty");
case SqlDbType.Variant: return("null");
case SqlDbType.Xml: return("string.Empty");
default:
return("// NO MIN VALUE AVAILABLE FOR " + sql_column.SqlDataType.ToString());
}
}
internal override SqlAlias VisitAlias(SqlAlias a) {
SqlTable tab = a.Node as SqlTable;
SqlTableValuedFunctionCall tvf = a.Node as SqlTableValuedFunctionCall;
if (this.addPrimaryKeys && (tab != null || tvf != null)) {
List<SqlOrderExpression> list = new List<SqlOrderExpression>();
bool isTable = tab != null;
MetaType rowType = isTable ? tab.RowType : tvf.RowType;
foreach (MetaDataMember mm in rowType.IdentityMembers) {
string name = mm.MappedName;
SqlColumn col;
Expression sourceExpression;
List<SqlColumn> columns;
if (isTable) {
col = tab.Find(name);
sourceExpression = tab.SourceExpression;
columns = tab.Columns;
}
else {
col = tvf.Find(name);
sourceExpression = tvf.SourceExpression;
columns = tvf.Columns;
}
if (col == null) {
col = new SqlColumn(mm.MemberAccessor.Type, typeProvider.From(mm.MemberAccessor.Type), name, mm, null, sourceExpression);
col.Alias = a;
columns.Add(col);
}
list.Add(new SqlOrderExpression(SqlOrderType.Ascending, new SqlColumnRef(col)));
}
this.PrependOrderExpressions(list);
return a;
}
else {
return base.VisitAlias(a);
}
}
/// <summary>
/// Returns the SQL column name formatted to be used as a T-SQL
/// variable. Sample: Foo -> @Foo;
/// </summary>
public static string ToTSQLVariableName(SqlColumn sql_column)
{
return("@" + sql_column.Name);
}
internal override SqlExpression VisitColumn(SqlColumn col) {
this.aliasMap[col] = this.currentAlias;
this.Visit(col.Expression);
return col;
}
/// <summary>
/// Returns the CSharp mapping of the SQL datatype.
/// Maps actual datatypes, not datatype names.
/// sample: varchar(50) -> string
/// </summary>
public static string SQLTypeToCSharpType(SqlColumn sql_column)
{
if (sql_column.IsNullable)
{
switch (sql_column.SqlDataType)
{
case SqlDbType.BigInt: return("long?");
case SqlDbType.Binary: return("byte[]");
case SqlDbType.Bit: return("bool?");
case SqlDbType.Char: return("string");
case SqlDbType.Date: return("DateTime?");
case SqlDbType.DateTime: return("DateTime?");
case SqlDbType.DateTime2: return("DateTime?");
case SqlDbType.DateTimeOffset: return("DateTime?");
case SqlDbType.Decimal: return("decimal?");
case SqlDbType.Float: return("double?");
case SqlDbType.Image: return("byte[]");
case SqlDbType.Int: return("int?");
case SqlDbType.Money: return("decimal?");
case SqlDbType.NChar: return("string");
case SqlDbType.NText: return("string");
case SqlDbType.NVarChar: return("string");
case SqlDbType.Real: return("float?");
case SqlDbType.SmallDateTime: return("DateTime?");
case SqlDbType.SmallInt: return("short?");
case SqlDbType.SmallMoney: return("float?");
case SqlDbType.Structured: return("// NO TYPE AVAILABLE FOR " + sql_column.SqlDataType.ToString());
case SqlDbType.Text: return("string");
case SqlDbType.Time: return("DateTime?");
case SqlDbType.Timestamp: return("string");
case SqlDbType.TinyInt: return("byte?");
case SqlDbType.Udt: return("byte[]");
case SqlDbType.UniqueIdentifier: return("Guid?");
case SqlDbType.VarBinary: return("byte[]");
case SqlDbType.VarChar: return("string");
case SqlDbType.Variant: return("byte[]");
case SqlDbType.Xml: return("string");
default:
return("// NO TYPE AVAILABLE FOR " + sql_column.SqlDataType.ToString());
}
}
else
{
switch (sql_column.SqlDataType)
{
case SqlDbType.BigInt: return("long");
case SqlDbType.Binary: return("byte[]");
case SqlDbType.Bit: return("bool");
case SqlDbType.Char: return("string");
case SqlDbType.Date: return("DateTime");
case SqlDbType.DateTime: return("DateTime");
case SqlDbType.DateTime2: return("DateTime");
case SqlDbType.DateTimeOffset: return("DateTime");
case SqlDbType.Decimal: return("decimal");
case SqlDbType.Float: return("double");
case SqlDbType.Image: return("byte[]");
case SqlDbType.Int: return("int");
case SqlDbType.Money: return("decimal");
case SqlDbType.NChar: return("string");
case SqlDbType.NText: return("string");
case SqlDbType.NVarChar: return("string");
case SqlDbType.Real: return("float");
case SqlDbType.SmallDateTime: return("DateTime");
case SqlDbType.SmallInt: return("short");
case SqlDbType.SmallMoney: return("float");
case SqlDbType.Structured: return("// NO TYPE AVAILABLE FOR " + sql_column.SqlDataType.ToString());
case SqlDbType.Text: return("string");
case SqlDbType.Time: return("DateTime");
case SqlDbType.Timestamp: return("string");
case SqlDbType.TinyInt: return("byte");
case SqlDbType.Udt: return("byte[]");
case SqlDbType.UniqueIdentifier: return("Guid");
case SqlDbType.VarBinary: return("byte[]");
case SqlDbType.VarChar: return("string");
case SqlDbType.Variant: return("byte[]");
case SqlDbType.Xml: return("string");
default:
return("// NO TYPE AVAILABLE FOR " + sql_column.SqlDataType.ToString());
}
}
}
internal override SqlExpression VisitColumn(SqlColumn col) {
return base.VisitColumn(col);
}
/// <summary>
/// Returns a ASP.NET match of the SQL datatype
/// </summary>
public static string SQLToASPType(SqlColumn sql_column)
{
#region Sample
//<UpdateParameters>
// <asp:Parameter Name="foo" Type="Boolean" />
// <asp:Parameter Name="foo" Type="Byte" />
// <asp:Parameter Name="foo" Type="Char" />
// <asp:Parameter Name="foo" Type="DateTime" />
// <asp:Parameter Name="foo" Type="DBNull" />
// <asp:Parameter Name="foo" Type="Decimal" />
// <asp:Parameter Name="foo" Type="Double" />
// <asp:Parameter Name="foo" Type="Empty" />
// <asp:Parameter Name="foo" Type="Int16" />
// <asp:Parameter Name="foo" Type="Int32" />
// <asp:Parameter Name="foo" Type="Int64" />
// <asp:Parameter Name="foo" Type="Object" />
// <asp:Parameter Name="foo" Type="SByte" />
// <asp:Parameter Name="foo" Type="Single" />
// <asp:Parameter Name="foo" Type="String" />
// <asp:Parameter Name="foo" Type="UInt16" />
// <asp:Parameter Name="foo" Type="UInt32" />
// <asp:Parameter Name="foo" Type="UInt64" />
//</UpdateParameters>
#endregion
switch (sql_column.SqlDataType)
{
case SqlDbType.BigInt: return("UInt64");
case SqlDbType.Binary: return("Object");
case SqlDbType.Bit: return("Boolean");
case SqlDbType.Char: return("Char");;
case SqlDbType.Date: return("DateTime");
case SqlDbType.DateTime: return("DateTime");
case SqlDbType.DateTime2: return("DateTime");
case SqlDbType.DateTimeOffset: return("DateTime");
case SqlDbType.Decimal: return("Decimal");
case SqlDbType.Float: return("Decimal");
case SqlDbType.Image: return("Object");
case SqlDbType.Int: return("Int32");
case SqlDbType.Money: return("Decimal");;
case SqlDbType.NChar: return("string");
case SqlDbType.NText: return("string");
case SqlDbType.NVarChar: return("string");
case SqlDbType.Real: return("Decimal");
case SqlDbType.SmallDateTime: return("DateTime");
case SqlDbType.SmallInt: return("Int16");
case SqlDbType.SmallMoney: return("Decimal");
case SqlDbType.Structured: return("Object");
case SqlDbType.Text: return("string");
case SqlDbType.Time: return("DateTime");
case SqlDbType.Timestamp: return("string");
case SqlDbType.TinyInt: return("Byte");
case SqlDbType.Udt: return("Object");
case SqlDbType.UniqueIdentifier: return("String");
case SqlDbType.VarBinary: return("Object");
case SqlDbType.VarChar: return("string");
case SqlDbType.Variant: return("Object");
case SqlDbType.Xml: return("string");
default:
return("Int32");
}
}