static SqlFunction ConvertCase(bool canBeNull, Type systemType, ISqlExpression[] parameters, int start)
{
var len = parameters.Length - start;
var name = start == 0 ? "IIF" : "CASE";
var cond = parameters[start];
if (start == 0 && SqlExpression.NeedsEqual(cond))
{
cond = new SqlSearchCondition(
new SqlCondition(
false,
new SqlPredicate.ExprExpr(cond, SqlPredicate.Operator.Equal, new SqlValue(1), null)));
}
if (len == 3)
{
return new SqlFunction(systemType, name, cond, parameters[start + 1], parameters[start + 2])
{
CanBeNull = canBeNull
}
}
;
return(new SqlFunction(systemType, name,
cond,
parameters[start + 1],
ConvertCase(canBeNull, systemType, parameters, start + 2))
{
CanBeNull = canBeNull
});
}
}
void AddSearchConditions(SqlSearchCondition search, IEnumerable <SqlCondition> conditions)
{
if (_additionalFilter == null)
{
_additionalFilter = new Dictionary <SqlSearchCondition, SqlSearchCondition>();
}
if (!_additionalFilter.TryGetValue(search, out var value))
{
if (search.Conditions.Count > 0 && search.Precedence < Precedence.LogicalConjunction)
{
value = new SqlSearchCondition();
var prev = new SqlSearchCondition();
prev.Conditions.AddRange(search.Conditions);
search.Conditions.Clear();
search.Conditions.Add(new SqlCondition(false, value, false));
search.Conditions.Add(new SqlCondition(false, prev, false));
}
else
{
value = search;
}
_additionalFilter.Add(search, value);
}
value.Conditions.AddRange(conditions);
}
//this is for Tests.Linq.Common.CoalesceLike test
static SqlFunction ConvertCase(SqlFunction?func, Type systemType, ISqlExpression[] parameters, int start)
{
var len = parameters.Length - start;
var cond = parameters[start];
if (start == 0 && SqlExpression.NeedsEqual(cond))
{
cond = new SqlSearchCondition(
new SqlCondition(
false,
new SqlPredicate.ExprExpr(cond, SqlPredicate.Operator.Equal, new SqlValue(1), null)));
}
const string name = "CASE";
if (len == 3)
{
if (func != null && start == 0 && ReferenceEquals(parameters[start], cond))
{
return(func);
}
return(new SqlFunction(systemType, name, cond, parameters[start + 1], parameters[start + 2]));
}
return(new SqlFunction(systemType, name,
cond,
parameters[start + 1],
ConvertCase(null, systemType, parameters, start + 2)));
}
void OptimizeSearchCondition(SqlSearchCondition searchCondition)
{
var items = searchCondition.Conditions;
if (items.Any(c => c.IsOr))
{
return;
}
for (var i1 = 0; i1 < items.Count; i1++)
{
var c1 = items[i1];
var cmp = EvaluateLogical(c1);
if (cmp != null)
{
if (cmp.Value)
{
items.RemoveAt(i1);
--i1;
continue;
}
}
switch (c1.ElementType)
{
case QueryElementType.Condition:
case QueryElementType.SearchCondition:
{
var search = c1.Predicate as SqlSearchCondition;
if (search != null)
{
OptimizeSearchCondition(search);
if (search.Conditions.Count == 0)
{
items.RemoveAt(i1);
--i1;
continue;
}
}
break;
}
}
for (var i2 = i1 + 1; i2 < items.Count; i2++)
{
var c2 = items[i2];
if (CompareConditions(c2, c1))
{
searchCondition.Conditions.RemoveAt(i2);
--i2;
}
}
}
}
protected override bool BuildJoinType(JoinType joinType, SqlSearchCondition condition)
{
switch (joinType)
{
case JoinType.CrossApply: StringBuilder.Append("INNER JOIN LATERAL "); return(true);
case JoinType.OuterApply: StringBuilder.Append("LEFT JOIN LATERAL "); return(true);
}
return(base.BuildJoinType(joinType, condition));
}
public virtual void Visiting(SqlSearchCondition searchCondition)
{
if (searchCondition.Negative)
{
sqlBuilder.Append("Not ");
}
if (searchCondition.PrevSqlSearchCondition == null)
{
sqlBuilder.Append("(");
}
}
public override ISqlPredicate ConvertPredicateImpl(ISqlPredicate predicate, ConvertVisitor <RunOptimizationContext> visitor)
{
switch (predicate.ElementType)
{
case QueryElementType.ExprExprPredicate:
{
var expr = (SqlPredicate.ExprExpr)predicate;
// Oracle saves empty string as null to database, so we need predicate modification before sending query
//
if (expr.WithNull == true &&
(expr.Operator == SqlPredicate.Operator.Equal ||
expr.Operator == SqlPredicate.Operator.NotEqual ||
expr.Operator == SqlPredicate.Operator.GreaterOrEqual ||
expr.Operator == SqlPredicate.Operator.LessOrEqual))
{
if (expr.Expr1.SystemType == typeof(string) &&
expr.Expr1.TryEvaluateExpression(visitor.Context.OptimizationContext.Context, out var value1) && value1 is string string1)
{
if (string1 == "")
{
var sc = new SqlSearchCondition();
sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.ExprExpr(expr.Expr1, expr.Operator, expr.Expr2, null), true));
sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.IsNull(expr.Expr2, false), true));
return(sc);
}
}
if (expr.Expr2.SystemType == typeof(string) &&
expr.Expr2.TryEvaluateExpression(visitor.Context.OptimizationContext.Context, out var value2) && value2 is string string2)
{
if (string2 == "")
{
var sc = new SqlSearchCondition();
sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.ExprExpr(expr.Expr1, expr.Operator, expr.Expr2, null), true));
sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.IsNull(expr.Expr1, false), true));
return(sc);
}
}
}
break;
}
}
predicate = base.ConvertPredicateImpl(predicate, visitor);
return(predicate);
}
protected ISqlExpression AlternativeExists(SqlFunction func)
{
var query = (SelectQuery)func.Parameters[0];
if (query.Select.Columns.Count == 0)
{
query.Select.Columns.Add(new SqlColumn(query, new SqlExpression("'.'")));
}
query.Select.Take(1, null);
var sc = new SqlSearchCondition();
sc.Conditions.Add(
new SqlCondition(false, new SqlPredicate.IsNull(query, true)));
return(sc);
}
protected override ISqlExpression ConvertFunction(SqlFunction func)
{
func = ConvertFunctionParameters(func, false);
switch (func.Name)
{
case "CASE":
if (func.Parameters.Length <= 5)
{
func = ConvertCase(func.CanBeNull, func.SystemType, func.Parameters, 0);
}
break;
case "Coalesce":
if (func.Parameters.Length > 2)
{
var parms = new ISqlExpression[func.Parameters.Length - 1];
Array.Copy(func.Parameters, 1, parms, 0, parms.Length);
func = new SqlFunction(func.SystemType, func.Name, func.Parameters[0],
new SqlFunction(func.SystemType, func.Name, parms));
break;
}
var sc = new SqlSearchCondition();
sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.IsNull(func.Parameters[0], false)));
func = new SqlFunction(func.SystemType, "IIF", sc, func.Parameters[1], func.Parameters[0])
{
CanBeNull = func.CanBeNull
};
break;
}
return(base.ConvertFunction(func));
}
public virtual void GetListPropertySubselectAndAlias(IPropertyMap propertyMap, object hash, Hashtable columns, ArrayList order, string propPath, string suggestion)
{
if (hash == null)
{
hash = propertyMap;
}
ITableMap listTableMap = propertyMap.MustGetTableMap();
ITableMap parentTableMap = propertyMap.ClassMap.MustGetTableMap();
SqlTableAlias parentTable = sqlEmitter.GetTableAlias(parentTableMap, hash);
SqlSelectStatement subSelect = new SqlSelectStatement(parentTableMap.SourceMap);
//Hmmm....can an alias be redefined in a subselect?
//SqlTableAlias listTable = subSelect.GetSqlTableAlias(listTableMap, "cnt" + subSelect.GetNextTableAliasIndex());
SqlTableAlias listTable = subSelect.GetSqlTableAlias(listTableMap, "cnt" + sqlEmitter.Select.GetNextTableAliasIndex());
SqlCountFunction count = new SqlCountFunction();
subSelect.SqlSelectClause.AddSqlAliasSelectListItem(count);
subSelect.SqlFromClause.AddSqlAliasTableSource(listTable);
foreach (IColumnMap fkIdColumnMap in propertyMap.GetAllIdColumnMaps())
{
IColumnMap idColumnMap = fkIdColumnMap.MustGetPrimaryKeyColumnMap();
SqlColumnAlias fkIdColumn = listTable.GetSqlColumnAlias(fkIdColumnMap);
SqlColumnAlias idColumn = parentTable.GetSqlColumnAlias(idColumnMap);
SqlSearchCondition search = subSelect.SqlWhereClause.GetNextSqlSearchCondition();
search.GetSqlComparePredicate(fkIdColumn, SqlCompareOperatorType.Equals, idColumn);
}
if (suggestion == "")
{
suggestion = propPath;
}
SqlAliasSelectListItem countAlias = this.sqlEmitter.Select.SqlSelectClause.AddSqlAliasSelectListItem(subSelect, suggestion);
this.sqlEmitter.PropertyColumnMap[propPath] = countAlias.SqlExpressionAlias.Alias;
}
public virtual void Visited(SqlSearchCondition searchCondition)
{
if (searchCondition.NextSqlSearchCondition != null)
{
if (searchCondition.SqlPredicate != null || searchCondition.SubSqlSearchCondition != null)
{
if (searchCondition.OrNext)
{
sqlBuilder.Append(" Or ");
}
else
{
sqlBuilder.Append(" And ");
}
}
}
else
{
sqlBuilder.Append(")");
}
}
internal static void BuildJoin(
ExpressionBuilder builder,
SqlSearchCondition condition,
IBuildContext outerKeyContext, Expression outerKeySelector,
IBuildContext innerKeyContext, Expression innerKeySelector)
{
var predicate = builder.ConvertObjectComparison(
ExpressionType.Equal,
outerKeyContext, outerKeySelector,
innerKeyContext, innerKeySelector);
if (predicate == null)
{
predicate = new SqlPredicate.ExprExpr(
builder.ConvertToSql(outerKeyContext, outerKeySelector),
SqlPredicate.Operator.Equal,
builder.ConvertToSql(innerKeyContext, innerKeySelector),
Common.Configuration.Linq.CompareNullsAsValues ? true : null);
}
condition.Conditions.Add(new SqlCondition(false, predicate));
}
private void JoinNonPrimary(IPropertyMap iPropertyMap)
{
foreach (IColumnMap idColumn in iPropertyMap.GetAllIdColumnMaps())
{
SqlTableAlias thisTableAlias = this.sqlEmitter.Select.GetSqlTableAlias(idColumn.TableMap.Name);
SqlColumnAlias thisColAlias = thisTableAlias.GetSqlColumnAlias(idColumn.Name);
SqlTableAlias parentTableAlias = this.sqlEmitter.Select.GetSqlTableAlias(idColumn.PrimaryKeyTable);
SqlColumnAlias parentColAlias = parentTableAlias.GetSqlColumnAlias(idColumn.PrimaryKeyColumn);
if (!joinedNonPrimaries.ContainsKey(thisColAlias))
{
if (!(joinedNonPrimaries[thisColAlias] == parentColAlias))
{
SqlSearchCondition search = this.sqlEmitter.Select.SqlWhereClause.GetNextSqlSearchCondition();
search.GetSqlComparePredicate(parentColAlias, SqlCompareOperatorType.Equals, thisColAlias);
joinedNonPrimaries[thisColAlias] = parentColAlias;
}
}
}
}
protected override void BuildWhereSearchCondition(SelectQuery selectQuery, SqlSearchCondition condition)
{
if (NeedTake(selectQuery) && !NeedSkip(selectQuery) && selectQuery.OrderBy.IsEmpty && selectQuery.Having.IsEmpty)
{
BuildPredicate(
Precedence.LogicalConjunction,
new SqlPredicate.ExprExpr(
new SqlExpression(null, "ROWNUM", Precedence.Primary),
SqlPredicate.Operator.LessOrEqual,
selectQuery.Select.TakeValue));
if (base.BuildWhere(selectQuery))
{
StringBuilder.Append(" AND ");
BuildSearchCondition(Precedence.LogicalConjunction, condition);
}
}
else
{
BuildSearchCondition(Precedence.Unknown, condition);
}
}
private void AddConditions(SqlWhereClause where, ISqlTableSource table)
{
var keys = table.GetKeys(true);
if (keys == null)
{
return;
}
foreach (var key in keys.OfType <SqlField>())
{
var maxValue = GetMaxValue(key.DataType);
if (maxValue == null)
{
continue;
}
var cond = new SqlSearchCondition();
cond = cond.Expr(key).IsNull.Or;
if (maxValue is string)
{
cond.Expr(key).GreaterOrEqual.Expr(new SqlValue(maxValue));
}
else
{
cond.Expr(key).LessOrEqual.Expr(new SqlValue(maxValue));
}
where.ConcatSearchCondition(cond);
// only one field is enough
break;
}
}
public override ISqlPredicate ConvertSearchStringPredicate(SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext> visitor)
{
var like = base.ConvertSearchStringPredicate(predicate, visitor);
if (predicate.CaseSensitive.EvaluateBoolExpression(visitor.Context.OptimizationContext.Context) == true)
{
SqlPredicate.ExprExpr?subStrPredicate = null;
switch (predicate.Kind)
{
case SqlPredicate.SearchString.SearchKind.StartsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, new SqlFunction(
typeof(string), "LEFT", predicate.Expr1,
new SqlFunction(typeof(int), "Length", predicate.Expr2))),
SqlPredicate.Operator.Equal,
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2),
null
);
break;
}
case SqlPredicate.SearchString.SearchKind.EndsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, new SqlFunction(
typeof(string), "RIGHT", predicate.Expr1,
new SqlFunction(typeof(int), "Length", predicate.Expr2))),
SqlPredicate.Operator.Equal,
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2),
null
);
break;
}
case SqlPredicate.SearchString.SearchKind.Contains:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(int), "CHARINDEX",
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
predicate.Expr2),
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
predicate.Expr1)),
SqlPredicate.Operator.Greater,
new SqlValue(0), null);
break;
}
}
if (subStrPredicate != null)
{
var result = new SqlSearchCondition(
new SqlCondition(false, like, predicate.IsNot),
new SqlCondition(predicate.IsNot, subStrPredicate));
return(result);
}
}
return(like);
}
void AddSearchCondition(SqlSearchCondition search, SqlCondition condition)
{
AddSearchConditions(search, new[] { condition });
}
protected override void BuildFunction(SqlFunction func)
{
switch (func.Name)
{
case "Coalesce":
if (func.Parameters.Length > 2)
{
var parms = new ISqlExpression[func.Parameters.Length - 1];
Array.Copy(func.Parameters, 1, parms, 0, parms.Length);
BuildFunction(new SqlFunction(func.SystemType, func.Name, func.Parameters[0],
new SqlFunction(func.SystemType, func.Name, parms)));
return;
}
var sc = new SqlSearchCondition();
sc.Conditions.Add(new SqlCondition(false, new SqlPredicate.IsNull(func.Parameters[0], false)));
func = new SqlFunction(func.SystemType, "Iif", sc, func.Parameters[1], func.Parameters[0]);
break;
case "CASE": func = ConvertCase(func.SystemType, func.Parameters, 0); break;
case "CharIndex":
func = func.Parameters.Length == 2?
new SqlFunction(func.SystemType, "InStr", new SqlValue(1), func.Parameters[1], func.Parameters[0], new SqlValue(1)):
new SqlFunction(func.SystemType, "InStr", func.Parameters[2], func.Parameters[1], func.Parameters[0], new SqlValue(1));
break;
case "Convert":
switch (func.SystemType.ToUnderlying().GetTypeCodeEx())
{
case TypeCode.String: func = new SqlFunction(func.SystemType, "CStr", func.Parameters[1]); break;
case TypeCode.DateTime:
if (IsDateDataType(func.Parameters[0], "Date"))
{
func = new SqlFunction(func.SystemType, "DateValue", func.Parameters[1]);
}
else if (IsTimeDataType(func.Parameters[0]))
{
func = new SqlFunction(func.SystemType, "TimeValue", func.Parameters[1]);
}
else
{
func = new SqlFunction(func.SystemType, "CDate", func.Parameters[1]);
}
break;
default:
if (func.SystemType == typeof(DateTime))
{
goto case TypeCode.DateTime;
}
BuildExpression(func.Parameters[1]);
return;
}
break;
}
base.BuildFunction(func);
}
public override ISqlPredicate ConvertSearchStringPredicate <TContext>(MappingSchema mappingSchema, SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext <TContext> > visitor,
OptimizationContext optimizationContext)
{
var like = ConvertSearchStringPredicateViaLike(mappingSchema, predicate, visitor,
optimizationContext);
if (predicate.CaseSensitive.EvaluateBoolExpression(optimizationContext.Context) == true)
{
SqlPredicate.ExprExpr?subStrPredicate = null;
switch (predicate.Kind)
{
case SqlPredicate.SearchString.SearchKind.StartsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(string), "Substr", predicate.Expr1, new SqlValue(1),
new SqlFunction(typeof(int), "Length", predicate.Expr2)),
SqlPredicate.Operator.Equal,
predicate.Expr2, null);
break;
}
case SqlPredicate.SearchString.SearchKind.EndsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(string), "Substr", predicate.Expr1,
new SqlBinaryExpression(typeof(int),
new SqlFunction(typeof(int), "Length", predicate.Expr2), "*", new SqlValue(-1),
Precedence.Multiplicative)
),
SqlPredicate.Operator.Equal,
predicate.Expr2, null);
break;
}
case SqlPredicate.SearchString.SearchKind.Contains:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(int), "InStr", predicate.Expr1, predicate.Expr2),
SqlPredicate.Operator.Greater,
new SqlValue(0), null);
break;
}
}
if (subStrPredicate != null)
{
var result = new SqlSearchCondition(
new SqlCondition(false, like, predicate.IsNot),
new SqlCondition(predicate.IsNot, subStrPredicate));
return(result);
}
}
return(like);
}
public override ISqlPredicate ConvertSearchStringPredicate <TContext>(MappingSchema mappingSchema, SqlPredicate.SearchString predicate, ConvertVisitor <RunOptimizationContext <TContext> > visitor,
OptimizationContext optimizationContext)
{
var like = ConvertSearchStringPredicateViaLike(mappingSchema, predicate, visitor,
optimizationContext);
if (predicate.CaseSensitive.EvaluateBoolExpression(optimizationContext.Context) == true)
{
SqlPredicate.ExprExpr?subStrPredicate = null;
switch (predicate.Kind)
{
case SqlPredicate.SearchString.SearchKind.StartsWith:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
new SqlFunction(typeof(string), "SUBSTRING",
predicate.Expr1,
new SqlValue(1),
new SqlFunction(typeof(int), "Length", predicate.Expr2))),
SqlPredicate.Operator.Equal,
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2),
null
);
break;
}
case SqlPredicate.SearchString.SearchKind.EndsWith:
{
var indexExpression = new SqlBinaryExpression(typeof(int),
new SqlBinaryExpression(typeof(int),
new SqlFunction(typeof(int), "Length", predicate.Expr1),
"-",
new SqlFunction(typeof(int), "Length", predicate.Expr2)),
"+",
new SqlValue(1));
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
new SqlFunction(typeof(string), "SUBSTRING",
predicate.Expr1,
indexExpression,
new SqlFunction(typeof(int), "Length", predicate.Expr2))),
SqlPredicate.Operator.Equal,
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary, predicate.Expr2),
null
);
break;
}
case SqlPredicate.SearchString.SearchKind.Contains:
{
subStrPredicate =
new SqlPredicate.ExprExpr(
new SqlFunction(typeof(int), "CHARINDEX",
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
predicate.Expr2),
new SqlFunction(typeof(byte[]), "Convert", SqlDataType.DbVarBinary,
predicate.Expr1)),
SqlPredicate.Operator.Greater,
new SqlValue(0), null);
break;
}
}
if (subStrPredicate != null)
{
var result = new SqlSearchCondition(
new SqlCondition(false, like, predicate.IsNot),
new SqlCondition(predicate.IsNot, subStrPredicate));
return(result);
}
}
return(like);
}
private void BuildAssociationCondition(ExpressionBuilder builder, TableContext parent, AssociationDescriptor association, SqlSearchCondition condition)
{
for (var i = 0; i < association.ThisKey.Length; i++)
{
if (!parent.SqlTable.Fields.TryGetValue(association.ThisKey[i], out var field1))
{
throw new LinqException("Association key '{0}' not found for type '{1}.", association.ThisKey[i], parent.ObjectType);
}
if (!SqlTable.Fields.TryGetValue(association.OtherKey[i], out var field2))
{
throw new LinqException("Association key '{0}' not found for type '{1}.", association.OtherKey[i], ObjectType);
}
ISqlPredicate predicate = new SqlPredicate.ExprExpr(
field1, SqlPredicate.Operator.Equal, field2);
predicate = builder.Convert(parent, predicate);
condition.Conditions.Add(new SqlCondition(false, predicate));
}
if (ObjectType != OriginalType)
{
var predicate = Builder.MakeIsPredicate(this, OriginalType);
if (predicate.GetType() != typeof(SqlPredicate.Expr))
{
condition.Conditions.Add(new SqlCondition(false, predicate));
}
}
RegularConditionCount = condition.Conditions.Count;
ExpressionPredicate = Association.GetPredicate(parent.ObjectType, ObjectType);
if (ExpressionPredicate != null)
{
ExpressionPredicate = (LambdaExpression)Builder.ConvertExpressionTree(ExpressionPredicate);
var expr = Builder.ConvertExpression(ExpressionPredicate.Body.Unwrap());
Builder.BuildSearchCondition(
new ExpressionContext(parent.Parent, new IBuildContext[] { parent, this }, ExpressionPredicate),
expr,
condition.Conditions,
false);
}
}
