/// <summary>
/// Write this symbol out as a string for sql. This is just
/// the new name of the symbol (which could be the same as the old name).
/// We rename columns here if necessary.
/// </summary>
/// <param name="writer"> </param>
/// <param name="sqlGenerator"> </param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
if (NeedsRenaming)
{
int i;
if (sqlGenerator.AllColumnNames.TryGetValue(NewName, out i))
{
string newNameCandidate;
do
{
++i;
newNameCandidate = NewName + i.ToString(CultureInfo.InvariantCulture);
}
while (sqlGenerator.AllColumnNames.ContainsKey(newNameCandidate));
sqlGenerator.AllColumnNames[NewName] = i;
NewName = newNameCandidate;
}
// Add this column name to list of known names so that there are no subsequent
// collisions
sqlGenerator.AllColumnNames[NewName] = 0;
// Prevent it from being renamed repeatedly.
NeedsRenaming = false;
}
writer.Write(SqlGenerator.QuoteIdentifier(NewName));
}
/// <summary>
/// We delegate the writing of the fragment to the appropriate type.
/// </summary>
/// <param name="writer"> </param>
/// <param name="sqlGenerator"> </param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
if (null != _sqlFragments)
{
foreach (var o in _sqlFragments)
{
var str = (o as String);
if (null != str)
{
writer.Write(str);
}
else
{
var sqlFragment = (o as ISqlFragment);
if (null != sqlFragment)
{
sqlFragment.WriteSql(writer, sqlGenerator);
}
else
{
throw new InvalidOperationException();
}
}
}
}
}
/// <summary>
/// Write out the OFFSET part of sql select statement
/// It basically writes OFFSET X ROWS.
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
writer.Write("OFFSET ");
SkipCount.WriteSql(writer, sqlGenerator);
writer.Write(" ROWS");
writer.Write(" ");
}
/// <summary>
/// Write out the TOP part of sql select statement
/// It basically writes TOP (X) [WITH TIES].
/// The brackets around X are ommited for Sql8.
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
writer.Write("TOP ");
writer.Write("(");
TopCount.WriteSql(writer, sqlGenerator);
writer.Write(")");
writer.Write(" ");
Debug.Assert(!WithTies, "WITH TIES cannot be true for Top clause");
}
// This function generates OFFSET, FETCH clause from Top and Skip information.
// Note that this should be used only when Skip is present. For only Top, TopClause.WriteSql()
//
private static void WriteOffsetFetch(SqlWriter writer, TopClause top, SkipClause skip, SqlGenerator sqlGenerator)
{
DebugCheck.NotNull(skip);
skip.WriteSql(writer, sqlGenerator);
if (top != null)
{
writer.Write("FETCH NEXT ");
top.TopCount.WriteSql(writer, sqlGenerator);
writer.Write(" ROWS ONLY");
writer.Write(" ");
}
}
public static void VisitDbIsNullExpression_non_parameter_reference_expression_is_not_cast()
{
var primitiveType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String);
var typeUsage = TypeUsage.CreateStringTypeUsage(primitiveType, isUnicode: true, isFixedLength: false);
var isNullExpression = typeUsage.Constant("constant").IsNull();
var sqlGenerator = new SqlGenerator();
var sqlFragment = sqlGenerator.Visit(isNullExpression);
var builder = new StringBuilder();
using (var writer = new SqlWriter(builder))
{
sqlFragment.WriteSql(writer, sqlGenerator);
}
Assert.Equal("N'constant' IS NULL", builder.ToString());
}
public static void VisitDbIsNullExpression_variable_size_string_parameter_with_max_length_is_not_cast()
{
var primitiveType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String);
var typeUsage = TypeUsage.CreateStringTypeUsage(primitiveType, isUnicode: true, isFixedLength: false, maxLength: 100);
var isNullExpression = typeUsage.Parameter("parameterName").IsNull();
var sqlGenerator = new SqlGenerator();
var sqlFragment = sqlGenerator.Visit(isNullExpression);
var builder = new StringBuilder();
using (var writer = new SqlWriter(builder))
{
sqlFragment.WriteSql(writer, sqlGenerator);
}
Assert.Equal("@parameterName IS NULL", builder.ToString());
}
public static void VisitDbIsNullExpression_variable_size_binary_parameter_without_max_length_is_cast_to_image()
{
var primitiveType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Binary);
var typeUsage = TypeUsage.CreateBinaryTypeUsage(primitiveType, isFixedLength: false);
var isNullExpression = typeUsage.Parameter("parameterName").IsNull();
var sqlGenerator = new SqlGenerator();
var sqlFragment = sqlGenerator.Visit(isNullExpression);
var builder = new StringBuilder();
using (var writer = new SqlWriter(builder))
{
sqlFragment.WriteSql(writer, sqlGenerator);
}
Assert.Equal("cast(@parameterName as image) IS NULL", builder.ToString());
}
public static void VisitDbIsNullExpression_variable_size_string_parameter_without_max_length_is_cast_to_ntext()
{
var primitiveType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String);
var typeUsage = TypeUsage.CreateStringTypeUsage(primitiveType, isUnicode: true, isFixedLength: false);
var isNullExpression = typeUsage.Parameter("parameterName").IsNull();
var sqlGenerator = new SqlGenerator();
var sqlFragment = sqlGenerator.Visit(isNullExpression);
var builder = new StringBuilder();
using (var writer = new SqlWriter(builder))
{
sqlFragment.WriteSql(writer, sqlGenerator);
}
Assert.Equal("cast(@parameterName as ntext) IS NULL", builder.ToString());
}
public override void Visit(DbConstantExpression expression)
{
Check.NotNull(expression, "expression");
if (_createParameters)
{
var parameter = CreateParameter(expression.Value, expression.ResultType);
_commandText.Append(parameter.ParameterName);
}
else
{
using (var writer = new SqlWriter(_commandText.InnerBuilder))
{
_sqlGenerator.WriteSql(writer, expression.Accept(_sqlGenerator));
}
}
}
public static void VisitDbIsNullExpression_variable_size_binary_parameter_with_max_length_is_not_cast()
{
var primitiveType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Binary);
var typeUsage = TypeUsage.CreateBinaryTypeUsage(primitiveType, isFixedLength: false, maxLength: 100);
var isNullExpression = typeUsage.Parameter("parameterName").IsNull();
var sqlGenerator = new SqlGenerator();
var sqlFragment = sqlGenerator.Visit(isNullExpression);
var builder = new StringBuilder();
using (var writer = new SqlWriter(builder))
{
sqlFragment.WriteSql(writer, sqlGenerator);
}
Assert.Equal("@parameterName IS NULL", builder.ToString());
}
public static void VisitDbIsNullExpression_non_parameter_reference_expression_is_not_cast()
{
var primitiveType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.String);
var typeUsage = TypeUsage.CreateStringTypeUsage(primitiveType, isUnicode: true, isFixedLength: false);
var isNullExpression = typeUsage.Constant("constant").IsNull();
var sqlGenerator = new SqlGenerator();
var sqlFragment = sqlGenerator.Visit(isNullExpression);
var builder = new StringBuilder();
using (var writer = new SqlWriter(builder))
{
sqlFragment.WriteSql(writer, sqlGenerator);
}
Assert.Equal("N'constant' IS NULL", builder.ToString());
}
public void Visit_In_expression_with_empty_list_and_Visit_Constant_expression_false_generate_same_sql()
{
var generator = new SqlGenerator();
var inExpression = DbExpressionBuilder.In(
DbExpressionBuilder.Constant(5), new List <DbConstantExpression>());
var builder1 = new StringBuilder();
var builder2 = new StringBuilder();
using (var writer = new SqlWriter(builder1))
{
generator.Visit(DbExpressionBuilder.False).WriteSql(writer, null);
}
using (var writer = new SqlWriter(builder2))
{
generator.Visit(inExpression).WriteSql(writer, null);
}
Assert.Equal(builder1.ToString(), builder2.ToString());
}
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
// Symbol pair should never be part of a SqlBuilder.
Debug.Assert(false);
}
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
// Symbol pair should never be part of a SqlBuilder.
Debug.Assert(false);
}
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
#region Check if FROM aliases need to be renamed
// Create a list of the aliases used by the outer extents
// JoinSymbols have to be treated specially.
List<string> outerExtentAliases = null;
if ((null != outerExtents)
&& (0 < outerExtents.Count))
{
foreach (var outerExtent in outerExtents.Keys)
{
var joinSymbol = outerExtent as JoinSymbol;
if (joinSymbol != null)
{
foreach (var symbol in joinSymbol.FlattenedExtentList)
{
if (null == outerExtentAliases)
{
outerExtentAliases = new List<string>();
}
outerExtentAliases.Add(symbol.NewName);
}
}
else
{
if (null == outerExtentAliases)
{
outerExtentAliases = new List<string>();
}
outerExtentAliases.Add(outerExtent.NewName);
}
}
}
// An then rename each of the FromExtents we have
// If AllJoinExtents is non-null - it has precedence.
// The new name is derived from the old name - we append an increasing int.
var extentList = AllJoinExtents ?? fromExtents;
if (null != extentList)
{
foreach (var fromAlias in extentList)
{
if ((null != outerExtentAliases)
&& outerExtentAliases.Contains(fromAlias.Name))
{
var i = sqlGenerator.AllExtentNames[fromAlias.Name];
string newName;
do
{
++i;
newName = fromAlias.Name + i.ToString(CultureInfo.InvariantCulture);
}
while (sqlGenerator.AllExtentNames.ContainsKey(newName));
sqlGenerator.AllExtentNames[fromAlias.Name] = i;
fromAlias.NewName = newName;
// Add extent to list of known names (although i is always incrementing, "prefix11" can
// eventually collide with "prefix1" when it is extended)
sqlGenerator.AllExtentNames[newName] = 0;
}
// Add the current alias to the list, so that the extents
// that follow do not collide with me.
if (null == outerExtentAliases)
{
outerExtentAliases = new List<string>();
}
outerExtentAliases.Add(fromAlias.NewName);
}
}
#endregion
// Increase the indent, so that the Sql statement is nested by one tab.
writer.Indent += 1; // ++ can be confusing in this context
writer.Write("SELECT ");
if (IsDistinct)
{
writer.Write("DISTINCT ");
}
if (Top != null
&& Skip == null) // This is simple TOP case. There is no OFFSET.
{
Top.WriteSql(writer, sqlGenerator);
}
if ((null == @select)
|| Select.IsEmpty)
{
Debug.Assert(false); // we have removed all possibilities of SELECT *.
writer.Write("*");
}
else
{
Select.WriteSql(writer, sqlGenerator);
}
writer.WriteLine();
writer.Write("FROM ");
From.WriteSql(writer, sqlGenerator);
if ((null != @where)
&& !Where.IsEmpty)
{
writer.WriteLine();
writer.Write("WHERE ");
Where.WriteSql(writer, sqlGenerator);
}
if ((null != groupBy)
&& !GroupBy.IsEmpty)
{
writer.WriteLine();
writer.Write("GROUP BY ");
GroupBy.WriteSql(writer, sqlGenerator);
}
if ((null != orderBy)
&& !OrderBy.IsEmpty
&& (IsTopMost || Top != null || Skip != null))
{
writer.WriteLine();
writer.Write("ORDER BY ");
OrderBy.WriteSql(writer, sqlGenerator);
}
if (null != Skip)
{
writer.WriteLine();
WriteOffsetFetch(writer, Top, Skip, sqlGenerator); // Write OFFSET, FETCH clause.
}
--writer.Indent;
}
// This function generates OFFSET, FETCH clause from Top and Skip information.
// Note that this should be used only when Skip is present. For only Top, TopClause.WriteSql()
//
private static void WriteOffsetFetch(SqlWriter writer, TopClause top, SkipClause skip, SqlGenerator sqlGenerator)
{
DebugCheck.NotNull(skip);
skip.WriteSql(writer, sqlGenerator);
if (top != null)
{
writer.Write("FETCH NEXT ");
top.TopCount.WriteSql(writer, sqlGenerator);
writer.Write(" ROWS ONLY");
writer.Write(" ");
}
}
public override void Visit(DbConstantExpression expression)
{
Check.NotNull(expression, "expression");
if (_createParameters)
{
var parameter = CreateParameter(expression.Value, expression.ResultType);
_commandText.Append(parameter.ParameterName);
}
else
{
using (var writer = new SqlWriter(_commandText.InnerBuilder))
{
_sqlGenerator.WriteSql(writer, expression.Accept(_sqlGenerator));
}
}
}
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
#region Check if FROM aliases need to be renamed
// Create a list of the aliases used by the outer extents
// JoinSymbols have to be treated specially.
List <string> outerExtentAliases = null;
if ((null != outerExtents) &&
(0 < outerExtents.Count))
{
foreach (var outerExtent in outerExtents.Keys)
{
var joinSymbol = outerExtent as JoinSymbol;
if (joinSymbol != null)
{
foreach (var symbol in joinSymbol.FlattenedExtentList)
{
if (null == outerExtentAliases)
{
outerExtentAliases = new List <string>();
}
outerExtentAliases.Add(symbol.NewName);
}
}
else
{
if (null == outerExtentAliases)
{
outerExtentAliases = new List <string>();
}
outerExtentAliases.Add(outerExtent.NewName);
}
}
}
// An then rename each of the FromExtents we have
// If AllJoinExtents is non-null - it has precedence.
// The new name is derived from the old name - we append an increasing int.
var extentList = AllJoinExtents ?? fromExtents;
if (null != extentList)
{
foreach (var fromAlias in extentList)
{
if ((null != outerExtentAliases) &&
outerExtentAliases.Contains(fromAlias.Name))
{
var i = sqlGenerator.AllExtentNames[fromAlias.Name];
string newName;
do
{
++i;
newName = fromAlias.Name + i.ToString(CultureInfo.InvariantCulture);
}while (sqlGenerator.AllExtentNames.ContainsKey(newName));
sqlGenerator.AllExtentNames[fromAlias.Name] = i;
fromAlias.NewName = newName;
// Add extent to list of known names (although i is always incrementing, "prefix11" can
// eventually collide with "prefix1" when it is extended)
sqlGenerator.AllExtentNames[newName] = 0;
}
// Add the current alias to the list, so that the extents
// that follow do not collide with me.
if (null == outerExtentAliases)
{
outerExtentAliases = new List <string>();
}
outerExtentAliases.Add(fromAlias.NewName);
}
}
#endregion
// Increase the indent, so that the Sql statement is nested by one tab.
writer.Indent += 1; // ++ can be confusing in this context
writer.Write("SELECT ");
if (IsDistinct)
{
writer.Write("DISTINCT ");
}
if (Top != null &&
Skip == null) // This is simple TOP case. There is no OFFSET.
{
Top.WriteSql(writer, sqlGenerator);
}
if ((null == @select) ||
Select.IsEmpty)
{
Debug.Assert(false); // we have removed all possibilities of SELECT *.
writer.Write("*");
}
else
{
Select.WriteSql(writer, sqlGenerator);
}
writer.WriteLine();
writer.Write("FROM ");
From.WriteSql(writer, sqlGenerator);
if ((null != @where) &&
!Where.IsEmpty)
{
writer.WriteLine();
writer.Write("WHERE ");
Where.WriteSql(writer, sqlGenerator);
}
if ((null != groupBy) &&
!GroupBy.IsEmpty)
{
writer.WriteLine();
writer.Write("GROUP BY ");
GroupBy.WriteSql(writer, sqlGenerator);
}
if ((null != orderBy) &&
!OrderBy.IsEmpty &&
(IsTopMost || Top != null || Skip != null))
{
writer.WriteLine();
writer.Write("ORDER BY ");
OrderBy.WriteSql(writer, sqlGenerator);
}
if (null != Skip)
{
writer.WriteLine();
WriteOffsetFetch(writer, Top, Skip, sqlGenerator); // Write OFFSET, FETCH clause.
}
--writer.Indent;
}
public void Visit_In_expression_with_empty_list_and_Visit_Constant_expression_false_generate_same_sql()
{
var generator = new SqlGenerator();
var inExpression = DbExpressionBuilder.In(
DbExpressionBuilder.Constant(5), new List<DbConstantExpression>());
var builder1 = new StringBuilder();
var builder2 = new StringBuilder();
using (var writer = new SqlWriter(builder1))
{
generator.Visit(DbExpressionBuilder.False).WriteSql(writer, null);
}
using (var writer = new SqlWriter(builder2))
{
generator.Visit(inExpression).WriteSql(writer, null);
}
Assert.Equal(builder1.ToString(), builder2.ToString());
}