public static SqlExpression GenericPad(SqlFunctionCall node)
{
string paddingFunction;
switch (node.FunctionType)
{
case SqlFunctionType.PadLeft:
paddingFunction = "lpad";
break;
case SqlFunctionType.PadRight:
paddingFunction = "rpad";
break;
default:
throw new InvalidOperationException();
}
var operand = node.Arguments[0];
var result = SqlDml.Case();
result.Add(
SqlDml.CharLength(operand) < node.Arguments[1],
SqlDml.FunctionCall(paddingFunction, node.Arguments));
result.Else = operand;
return(result);
}
public void SqlCaseReplacingTest()
{
SqlCase c1 = SqlDml.Case();
SqlCase c2 = SqlDml.Case(SqlDml.Literal(1));
c2.Else = SqlDml.Null;
c2[1] = 2;
c2[2] = 3;
c2[4] = 5;
c2[6] = 7;
c1.ReplaceWith(c2);
bool passed = false;
try {
c1.ReplaceWith(1);
}
catch {
passed = true;
}
Assert.IsTrue(passed);
Assert.AreNotSame(c1, c2);
//Possibly NUnit compares items of IEnumirable
//and if they the same it sugests two objects are equals
//Assert.AreNotEqual(c1, c2);
Assert.AreEqual(c1.NodeType, c2.NodeType);
Assert.AreEqual(c1.Value, c2.Value);
Assert.AreEqual(c1.Else, c2.Else);
Assert.AreEqual(c1.Count, c2.Count);
foreach (KeyValuePair <SqlExpression, SqlExpression> p in c1)
{
Assert.AreEqual(p.Value, c2[p.Key]);
}
}
public void Test012()
{
string nativeSql = @"SELECT
CASE p.staff_id
WHEN 1 THEN 'STAFF_1'
WHEN 2 THEN 'STAFF_2'
ELSE 'STAFF_OTHER'
END AS Staff,
SUM(p.amount) AS Total
FROM
payment p
GROUP BY
p.staff_id";
SqlTableRef payment = SqlDml.TableRef(schema.Tables["payment"], "p");
SqlSelect select = SqlDml.Select(payment);
SqlCase totalPayment = SqlDml.Case(payment["staff_id"]);
totalPayment[1] = SqlDml.Literal("STAFF_1");
totalPayment[2] = SqlDml.Literal("STAFF_2");
totalPayment.Else = SqlDml.Literal("STAFF_OTHER");
select.Columns.Add(totalPayment, "Staff");
select.Columns.Add(SqlDml.Sum(payment["amount"]), "Total");
select.GroupBy.AddRange(payment["staff_id"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test012()
{
string nativeSql = @"SELECT
CASE il.TrackId
WHEN 1 THEN 'STAFF_1'
WHEN 2 THEN 'STAFF_2'
ELSE 'STAFF_OTHER'
END AS shippers,
SUM(il.UnitPrice) AS TotalUnits
FROM [invoiceline] il
GROUP BY il.TrackId";
SqlTableRef invoiceLine = SqlDml.TableRef(schema.Tables["invoiceline"], "il");
SqlSelect select = SqlDml.Select(invoiceLine);
SqlCase totalPayment = SqlDml.Case(invoiceLine["TrackId"]);
totalPayment[1] = SqlDml.Literal("STAFF_1");
totalPayment[2] = SqlDml.Literal("STAFF_2");
totalPayment.Else = SqlDml.Literal("STAFF_OTHER");
select.Columns.Add(totalPayment, "shippers");
select.Columns.Add(SqlDml.Sum(invoiceLine["UnitPrice"]), "TotalUnits");
select.GroupBy.AddRange(invoiceLine["TrackId"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
private SqlExpression GenericPad(SqlFunctionCall node)
{
var operand = node.Arguments[0];
var actualLength = SqlDml.CharLength(operand);
var requiredLength = node.Arguments[1];
var paddingExpression = node.Arguments.Count > 2
? SqlDml.FunctionCall("REPLICATE", node.Arguments[2], requiredLength - actualLength)
: SqlDml.FunctionCall("SPACE", requiredLength - actualLength);
SqlExpression resultExpression;
switch (node.FunctionType)
{
case SqlFunctionType.PadLeft:
resultExpression = paddingExpression + operand;
break;
case SqlFunctionType.PadRight:
resultExpression = operand + paddingExpression;
break;
default:
throw new InvalidOperationException();
}
var result = SqlDml.Case();
result.Add(actualLength < requiredLength, resultExpression);
result.Else = operand;
return(result);
}
private static SqlExpression Max(SqlExpression left, SqlExpression right)
{
var result = SqlDml.Case();
result.Add(left > right, left);
result.Else = right;
return(result);
}
/// <summary>
/// Performs "rounding as tought in school" on the specified argument.
/// </summary>
/// <param name="value">The value to round.</param>
/// <returns>Result of rounding.</returns>
public static SqlExpression RegularRound(SqlExpression value)
{
var result = SqlDml.Case();
result.Add(value > 0, SqlDml.Truncate(value + 0.5));
result.Else = SqlDml.Truncate(value - 0.5);
return(result);
}
/// <summary>
/// Converts the specified interval expression to expression
/// that represents absolute value (duration) of the specified interval.
/// This is a generic implementation that uses comparison with zero interval.
/// It's suitable for any server, but can be inefficient.
/// </summary>
/// <param name="source">The source.</param>
/// <returns>Result of conversion.</returns>
public static SqlExpression IntervalAbs(SqlExpression source)
{
var result = SqlDml.Case();
result.Add(source >= SqlDml.Literal(new TimeSpan(0)), source);
result.Else = SqlDml.IntervalNegate(source);
return(result);
}
/// <summary>
/// Performs banker's rounding on the specified argument.
/// </summary>
/// <param name="value">The value to round.</param>
/// <returns>Result of rounding.</returns>
public static SqlExpression BankersRound(SqlExpression value)
{
var mainPart = 2 * SqlDml.Floor((value + 0.5) / 2);
var extraPart = SqlDml.Case();
extraPart.Add(value - mainPart > 0.5, 1);
extraPart.Else = 0;
return(mainPart + extraPart);
}
public static SqlExpression StringCompare(
[Type(typeof(string))] SqlExpression strA,
[Type(typeof(string))] SqlExpression strB)
{
var result = SqlDml.Case();
result.Add(strA > strB, SqlDml.Literal(1));
result.Add(strA < strB, SqlDml.Literal(-1));
result.Else = SqlDml.Literal(0);
return(result);
}
public static SqlExpression DateTimeDaysInMonth(
[Type(typeof(int))] SqlExpression year,
[Type(typeof(int))] SqlExpression month)
{
var februaryCase = SqlDml.Case();
februaryCase.Add(DateTimeIsLeapYear(year), 29);
februaryCase.Else = 28;
var result = SqlDml.Case();
result.Add(SqlDml.In(month, SqlDml.Array(1, 3, 5, 7, 8, 10, 12)), 31);
result.Add(month == 2, februaryCase);
result.Else = 30;
return(result);
}
public void SqlCaseCloneTest()
{
SqlCase c = SqlDml.Case();
c.Value = SqlDml.Literal(1);
c[SqlDml.Literal(1)] = SqlDml.Literal("A");
c[SqlDml.Literal(2)] = SqlDml.Literal("B");
c.Else = SqlDml.Literal("C");
SqlCase cClone = (SqlCase)c.Clone();
Assert.AreNotEqual(c, cClone);
Assert.AreNotEqual(c.Value, cClone.Value);
Assert.AreNotEqual(c.Else, cClone.Else);
Assert.AreEqual(c.NodeType, cClone.NodeType);
Assert.AreEqual(c.Value.NodeType, cClone.Value.NodeType);
Assert.AreEqual(c.Else.NodeType, cClone.Else.NodeType);
Assert.AreEqual(c.Count, cClone.Count);
}
public SqlExpression BooleanToInt(SqlExpression expression)
{
// optimization: omitting BooleanToInt(IntToBoolean(x)) sequences
if (expression.NodeType == SqlNodeType.NotEquals)
{
var binary = (SqlBinary)expression;
var left = binary.Left;
var right = binary.Right as SqlLiteral <int>;
if (!ReferenceEquals(right, null) && right.Value == 0)
{
return(left);
}
}
var result = SqlDml.Case();
result.Add(expression, 1);
result.Else = 0;
return(SqlDml.Cast(result, booleanType));
}
private SqlSelect BuildTypedQuery(IndexInfo index)
{
var underlyingIndex = index.UnderlyingIndexes[0];
var baseQuery = BuildProviderQuery(underlyingIndex);
var query = SqlDml.Select(baseQuery.From);
query.Where = baseQuery.Where;
var baseColumns = baseQuery.Columns.ToList();
var typeIdColumnIndex = index.Columns
.Select((c, i) => new { c, i })
.Single(p => p.c.Field.IsTypeId).i;
var type = index.ReflectedType;
var typeIdColumn = SqlDml.ColumnRef(
SqlDml.Column(SqlDml.Literal(TypeIdRegistry[type])),
WellKnown.TypeIdFieldName);
var discriminatorMap = type.Hierarchy.TypeDiscriminatorMap;
if (discriminatorMap != null)
{
var discriminatorColumnIndex = underlyingIndex.Columns.IndexOf(discriminatorMap.Column);
var discriminatorColumn = baseQuery.From.Columns[discriminatorColumnIndex];
var sqlCase = SqlDml.Case(discriminatorColumn);
foreach (var pair in discriminatorMap)
{
var discriminatorValue = GetDiscriminatorValue(discriminatorMap, pair.First);
var typeId = TypeIdRegistry[pair.Second];
sqlCase.Add(SqlDml.Literal(discriminatorValue), SqlDml.Literal(typeId));
}
if (discriminatorMap.Default != null)
{
sqlCase.Else = SqlDml.Literal(TypeIdRegistry[discriminatorMap.Default]);
}
typeIdColumn = SqlDml.ColumnRef(
SqlDml.Column(sqlCase),
WellKnown.TypeIdFieldName);
}
baseColumns.Insert(typeIdColumnIndex, typeIdColumn);
query.Columns.AddRange(baseColumns);
return(query);
}
protected override SqlExpression VisitConditional(ConditionalExpression expression)
{
var check = Visit(expression.Test);
var ifTrue = Visit(expression.IfTrue);
var ifFalse = Visit(expression.IfFalse);
SqlContainer container = ifTrue as SqlContainer;
if (container != null)
{
ifTrue = TryUnwrapEnum(container);
}
container = ifFalse as SqlContainer;
if (container != null)
{
ifFalse = TryUnwrapEnum(container);
}
var boolCheck = fixBooleanExpressions
? booleanExpressionConverter.BooleanToInt(check)
: check;
var varCheck = boolCheck as SqlVariant;
if (!varCheck.IsNullReference())
{
return(SqlDml.Variant(varCheck.Id, ifFalse, ifTrue));
}
if (fixBooleanExpressions && IsBooleanExpression(expression))
{
var c = SqlDml.Case();
c[check] = booleanExpressionConverter.BooleanToInt(ifTrue);
c.Else = booleanExpressionConverter.BooleanToInt(ifFalse);
return(booleanExpressionConverter.IntToBoolean(c));
}
else
{
var c = SqlDml.Case();
c[check] = ifTrue;
c.Else = ifFalse;
return(c);
}
}
private SqlExpression VisitCast(UnaryExpression cast, SqlExpression operand)
{
var sourceType = cast.Operand.Type.StripNullable();
var targetType = cast.Type.StripNullable();
if (sourceType == targetType || targetType == typeof(object) || sourceType == typeof(object))
{
return(operand);
}
if (IsEnumUnderlyingType(sourceType, targetType) || IsEnumUnderlyingType(targetType, sourceType))
{
return(operand);
}
// Special case for boolean cast
if (fixBooleanExpressions && IsBooleanExpression(cast.Operand))
{
var result = SqlDml.Case();
result.Add(operand, 1);
result.Else = 0;
operand = result;
}
return(SqlDml.Cast(operand, driver.MapValueType(targetType)));
}
public override string Translate(SqlCompilerContext context, SqlMatch node, MatchSection section)
{
switch (section)
{
case MatchSection.Entry:
//MATCH is not supported by PostgreSQL, we need some workaround
SqlRow row = node.Value as SqlRow;
if (row != null)
{
SqlSelect finalQuery = SqlDml.Select();
finalQuery.Columns.Add(5);
switch (node.MatchType)
{
#region SIMPLE
case SqlMatchType.None: {
bool existsNull = false;
SqlCase c = SqlDml.Case();
{
bool subQueryNeeded = true;
//if any of the row elements is NULL then true
if (row.Count > 0)
{
bool allLiteral = true; //if true then there is no NULL element
SqlExpression when1 = null;
for (int i = 0; i < row.Count; i++)
{
bool elementIsNotLiteral = row[i].NodeType != SqlNodeType.Literal;
//if the row element is the NULL value
if (row[i].NodeType == SqlNodeType.Null)
{
existsNull = true;
break;
}
if (allLiteral && elementIsNotLiteral)
{
allLiteral = false;
}
if (elementIsNotLiteral)
{
if (when1 == null)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 || SqlDml.IsNull(row[i]);
}
}
}
if (existsNull)
{
//Some row element is the NULL value, MATCH result is true
subQueryNeeded = false;
}
else if (allLiteral)
{
//No row element is the NULL value
subQueryNeeded = true;
}
else //(!whenNotNeeded)
{
//Check if any row element is NULL
c.Add(when1 == null ? true : when1, true);
subQueryNeeded = true;
}
}
//find row in subquery
if (subQueryNeeded)
{
SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(1);
{
SqlTableColumnCollection columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
if (i == 0)
{
where = columns[i] == row[i];
}
else
{
where = where && columns[i] == row[i];
}
if (node.Unique)
{
q1.GroupBy.Add(columns[i]);
}
}
q1.Where = where;
if (node.Unique)
{
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
}
//c.Add(Sql.Exists(q1), true);
c.Else = SqlDml.Exists(q1);
}
}
if (c.Else == null)
{
c.Else = false;
}
if (existsNull)
{
finalQuery.Where = null;
}
else if (c.Count > 0)
{
finalQuery.Where = c;
}
else
{
finalQuery.Where = c.Else;
}
break;
}
#endregion
#region FULL
case SqlMatchType.Full: {
SqlCase c1 = SqlDml.Case();
{
bool noMoreWhenNeeded = false;
bool allNull = true;
SqlExpression when1 = true;
//if all row elements are null then true
if (row.Count > 0)
{
bool whenNotNeeded = false;
for (int i = 0; i < row.Count; i++)
{
//if any row element is surely not the NULL value
if (row[i].NodeType == SqlNodeType.Literal)
{
whenNotNeeded = true;
break;
}
if (allNull && row[i].NodeType != SqlNodeType.Null)
{
allNull = false;
}
if (i == 0)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 && SqlDml.IsNull(row[i]);
}
}
if (allNull)
{
when1 = true;
}
if (!whenNotNeeded)
{
c1.Add(when1, true);
}
}
if (!noMoreWhenNeeded)
{
bool whenNotNeeded = false;
bool allLiteral = true;
SqlExpression when2 = true;
//if no row elements are null then subcase
for (int i = 0; i < row.Count; i++)
{
if (row[i].NodeType == SqlNodeType.Null)
{
whenNotNeeded = true;
when2 = false;
break;
}
if (allLiteral && row[i].NodeType != SqlNodeType.Literal)
{
allLiteral = false;
}
if (i == 0)
{
when2 = SqlDml.IsNotNull(row[i]);
}
else
{
when2 = when2 && SqlDml.IsNotNull(row[i]);
}
}
if (allLiteral)
{
when2 = true;
}
if (!whenNotNeeded)
{
//find row in subquery
SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(1);
{
SqlTableColumnCollection columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
if (i == 0)
{
where = columns[i] == row[i];
}
else
{
where = where && columns[i] == row[i];
}
if (node.Unique)
{
q1.GroupBy.Add(columns[i]);
}
}
q1.Where = where;
if (node.Unique)
{
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
}
c1.Add(when2, SqlDml.Exists(q1));
}
}
//else false
c1.Else = false;
}
if (c1.Count > 0)
{
finalQuery.Where = c1;
}
else
{
finalQuery.Where = false;
}
break;
}
#endregion
#region PARTIAL
case SqlMatchType.Partial: {
bool allNull = true;
SqlCase c1 = SqlDml.Case();
{
SqlExpression when1 = true;
//if all row elements are null then true
if (row.Count > 0)
{
bool whenNotNeeded = false;
for (int i = 0; i < row.Count; i++)
{
//if any row element is surely not the NULL value
if (row[i].NodeType == SqlNodeType.Literal)
{
allNull = false;
whenNotNeeded = true;
break;
}
if (allNull && row[i].NodeType != SqlNodeType.Null)
{
allNull = false;
}
if (i == 0)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 && SqlDml.IsNull(row[i]);
}
}
if (allNull)
{
when1 = true;
}
if (!whenNotNeeded)
{
c1.Add(when1, true);
}
}
//otherwise
if (!allNull)
{
//find row in subquery
SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(8);
{
SqlTableColumnCollection columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
//if row[i] would be NULL then c3 would result in true,
if (row[i].NodeType != SqlNodeType.Null)
{
SqlCase c3 = SqlDml.Case();
c3.Add(SqlDml.IsNull(row[i]), true);
c3.Else = row[i] == columns[i];
if (where == null)
{
where = c3;
}
else
{
where = where && c3;
}
}
if (node.Unique)
{
SqlCase c4 = SqlDml.Case();
c4.Add(SqlDml.IsNull(row[i]), 0);
c4.Else = columns[i];
q1.GroupBy.Add(c4);
}
}
q1.Where = where;
if (node.Unique)
{
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
}
c1.Else = SqlDml.Exists(q1);
}
}
if (c1.Else == null)
{
c1.Else = false;
}
if (allNull)
{
finalQuery.Where = null;
}
else if (c1.Count > 0)
{
finalQuery.Where = c1;
}
else
{
finalQuery.Where = c1.Else;
}
}
break;
#endregion
}
SqlMatch newNode = SqlDml.Match(SqlDml.Row(), SqlDml.SubQuery(finalQuery).Query, node.Unique, node.MatchType);
node.ReplaceWith(newNode);
return("EXISTS(SELECT '");
}
else
{
throw new InvalidOperationException(Strings.ExSqlMatchValueMustBeAnSqlRowInstance);
}
case MatchSection.Specification:
return("' WHERE EXISTS");
case MatchSection.Exit:
return(")");
}
return(string.Empty);
}
private void BuildSelectForUniqueMatchNone(SqlMatch node, SqlRow row, SqlSelect finalQuery)
{
var existsNull = false;
var @case = SqlDml.Case();
var subQueryNeeded = true;
//if any of the row elements is NULL then true
if (row.Count > 0)
{
var allLiteral = true; //if true then there is no NULL element
SqlExpression when = null;
for (var i = 0; i < row.Count; i++)
{
var elementIsNotLiteral = row[i].NodeType != SqlNodeType.Literal;
//if the row element is the NULL value
if (row[i].NodeType == SqlNodeType.Null)
{
existsNull = true;
break;
}
if (allLiteral && elementIsNotLiteral)
{
allLiteral = false;
}
if (elementIsNotLiteral)
{
when = when == null
? SqlDml.IsNull(row[i])
: when || SqlDml.IsNull(row[i]);
}
}
if (existsNull)
{
//Some row element is the NULL value, MATCH result is true
subQueryNeeded = false;
}
else if (allLiteral)
{
//No row element is the NULL value
subQueryNeeded = true;
}
else //(!whenNotNeeded)
//Check if any row element is NULL
{
_ = @case.Add(when == null ? true : when, true);
subQueryNeeded = true;
}
}
//find row in subquery
if (subQueryNeeded)
{
var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
var subquery = SqlDml.Select(originalQuery);
subquery.Columns.Add(1);
var columns = originalQuery.Columns;
SqlExpression where = null;
for (var i = 0; i < columns.Count; i++)
{
if (i == 0)
{
where = columns[i] == row[i];
}
else
{
where = where && columns[i] == row[i];
}
if (node.Unique)
{
subquery.GroupBy.Add(columns[i]);
}
}
subquery.Where = where;
if (node.Unique)
{
subquery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
//c.Add(Sql.Exists(q1), true);
@case.Else = SqlDml.Exists(subquery);
}
if (@case.Else == null)
{
@case.Else = false;
}
if (existsNull)
{
finalQuery.Where = null;
}
else if (@case.Count > 0)
{
finalQuery.Where = @case;
}
else
{
finalQuery.Where = @case.Else;
}
}
private void BuildSelectForUniqueMatchFull(SqlMatch node, SqlRow row, SqlSelect finalQuery)
{
var @case = SqlDml.Case();
bool noMoreWhenNeeded = false;
bool allNull = true;
SqlExpression when1 = true;
//if all row elements are null then true
if (row.Count > 0)
{
var whenNotNeeded = false;
for (var i = 0; i < row.Count; i++)
{
//if any row element is surely not the NULL value
if (row[i].NodeType == SqlNodeType.Literal)
{
whenNotNeeded = true;
break;
}
if (allNull && row[i].NodeType != SqlNodeType.Null)
{
allNull = false;
}
if (i == 0)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 && SqlDml.IsNull(row[i]);
}
}
if (allNull)
{
when1 = true;
}
if (!whenNotNeeded)
{
_ = @case.Add(when1, true);
}
}
if (!noMoreWhenNeeded)
{
var whenNotNeeded = false;
var allLiteral = true;
SqlExpression when2 = true;
//if no row elements are null then subcase
for (var i = 0; i < row.Count; i++)
{
if (row[i].NodeType == SqlNodeType.Null)
{
whenNotNeeded = true;
when2 = false;
break;
}
if (allLiteral && row[i].NodeType != SqlNodeType.Literal)
{
allLiteral = false;
}
if (i == 0)
{
when2 = SqlDml.IsNotNull(row[i]);
}
else
{
when2 = when2 && SqlDml.IsNotNull(row[i]);
}
}
if (allLiteral)
{
when2 = true;
}
if (!whenNotNeeded)
{
//find row in subquery
var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
var subQuery = SqlDml.Select(originalQuery);
subQuery.Columns.Add(1);
var columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
if (i == 0)
{
where = columns[i] == row[i];
}
else
{
where = where && columns[i] == row[i];
}
if (node.Unique)
{
subQuery.GroupBy.Add(columns[i]);
}
}
subQuery.Where = where;
if (node.Unique)
{
subQuery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
_ = @case.Add(when2, SqlDml.Exists(subQuery));
}
}
//else false
@case.Else = false;
finalQuery.Where = @case.Count > 0 ? @case : (SqlExpression)false;
}
private void BuildSelectForUniqueMatchPartial(SqlMatch node, SqlRow row, SqlSelect finalQuery)
{
bool allNull = true;
var @case = SqlDml.Case();
SqlExpression when = true;
//if all row elements are null then true
if (row.Count > 0)
{
var whenNotNeeded = false;
for (var i = 0; i < row.Count; i++)
{
//if any row element is surely not the NULL value
if (row[i].NodeType == SqlNodeType.Literal)
{
allNull = false;
whenNotNeeded = true;
break;
}
if (allNull && row[i].NodeType != SqlNodeType.Null)
{
allNull = false;
}
when = i == 0
? SqlDml.IsNull(row[i])
: when && SqlDml.IsNull(row[i]);
}
if (allNull)
{
when = true;
}
if (!whenNotNeeded)
{
_ = @case.Add(when, true);
}
}
//otherwise
if (!allNull)
{
//find row in subquery
var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
var subQuery = SqlDml.Select(originalQuery);
subQuery.Columns.Add(8);
var columns = originalQuery.Columns;
SqlExpression where = null;
for (var i = 0; i < columns.Count; i++)
{
//if row[i] would be NULL then c3 would result in true,
if (row[i].NodeType != SqlNodeType.Null)
{
SqlCase c3 = SqlDml.Case();
_ = c3.Add(SqlDml.IsNull(row[i]), true);
c3.Else = row[i] == columns[i];
where = where == null ? c3 : where && c3;
}
if (node.Unique)
{
var c4 = SqlDml.Case();
_ = c4.Add(SqlDml.IsNull(row[i]), 0);
c4.Else = columns[i];
subQuery.GroupBy.Add(c4);
}
}
subQuery.Where = where;
if (node.Unique)
{
subQuery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
@case.Else = SqlDml.Exists(subQuery);
}
if (@case.Else == null)
{
@case.Else = false;
}
if (allNull)
{
finalQuery.Where = null;
}
else if (@case.Count > 0)
{
finalQuery.Where = @case;
}
else
{
finalQuery.Where = @case.Else;
}
}