bool CompareExpressions(SqlPredicate.ExprExpr expr1, SqlPredicate.ExprExpr expr2) { if (expr1.Operator != expr2.Operator) { return(false); } if (expr1.ElementType != expr2.ElementType) { return(false); } switch (expr1.ElementType) { case QueryElementType.ExprExprPredicate: { return(CompareExpressions(expr1.Expr1, expr2.Expr1) == true && CompareExpressions(expr1.Expr2, expr2.Expr2) == true || CompareExpressions(expr1.Expr1, expr2.Expr2) == true && CompareExpressions(expr1.Expr2, expr2.Expr1) == true); } } return(false); }
public override ISqlPredicate ConvertPredicateImpl <TContext>(MappingSchema mappingSchema, ISqlPredicate predicate, ConvertVisitor <RunOptimizationContext <TContext> > visitor, OptimizationContext optimizationContext) { if (predicate is SqlPredicate.ExprExpr exprExpr) { var leftType = QueryHelper.GetDbDataType(exprExpr.Expr1); var rightType = QueryHelper.GetDbDataType(exprExpr.Expr2); if ((IsDateTime(leftType) || IsDateTime(rightType)) && !(exprExpr.Expr1.TryEvaluateExpression(optimizationContext.Context, out var value1) && value1 == null || exprExpr.Expr2.TryEvaluateExpression(optimizationContext.Context, out var value2) && value2 == null)) { if (!(exprExpr.Expr1 is SqlFunction func1 && (func1.Name == "$Convert$" || func1.Name == "DateTime"))) { var left = new SqlFunction(leftType.SystemType, "$Convert$", SqlDataType.GetDataType(leftType.SystemType), new SqlDataType(leftType), exprExpr.Expr1); exprExpr = new SqlPredicate.ExprExpr(left, exprExpr.Operator, exprExpr.Expr2, null); } if (!(exprExpr.Expr2 is SqlFunction func2 && (func2.Name == "$Convert$" || func2.Name == "DateTime"))) { var right = new SqlFunction(rightType.SystemType, "$Convert$", new SqlDataType(rightType), new SqlDataType(rightType), exprExpr.Expr2); exprExpr = new SqlPredicate.ExprExpr(exprExpr.Expr1, exprExpr.Operator, right, null); } predicate = exprExpr; } } predicate = base.ConvertPredicateImpl(mappingSchema, predicate, visitor, optimizationContext); return(predicate); }
protected override void BuildPredicate(ISqlPredicate predicate) { var newpredicate = predicate; switch (predicate.ElementType) { case QueryElementType.LikePredicate: var p = (SqlPredicate.Like)predicate; var param2 = GetParm(p.Expr2 as IValueContainer, p.Expr1.SystemType); if (param2 != null) { if (param2 is SqlValue value && value.Value == null) { if (p.IsNot) { newpredicate = new SqlPredicate.ExprExpr(p.Expr1, SqlPredicate.Operator.NotEqual, p.Expr2); } else { newpredicate = new SqlPredicate.ExprExpr(p.Expr1, SqlPredicate.Operator.Equal, p.Expr2); } } else { newpredicate = new SqlPredicate.Like(p.Expr1, p.IsNot, param2, p.Escape); } } break;
public override ISqlPredicate ConvertPredicateImpl(ISqlPredicate predicate, ConvertVisitor <RunOptimizationContext> visitor) { if (predicate is SqlPredicate.ExprExpr exprExpr) { var leftType = QueryHelper.GetDbDataType(exprExpr.Expr1); var rightType = QueryHelper.GetDbDataType(exprExpr.Expr2); if ((IsDateTime(leftType) || IsDateTime(rightType)) && !(exprExpr.Expr1.TryEvaluateExpression(visitor.Context.OptimizationContext.Context, out var value1) && value1 == null || exprExpr.Expr2.TryEvaluateExpression(visitor.Context.OptimizationContext.Context, out var value2) && value2 == null)) { if (!(exprExpr.Expr1 is SqlFunction func1 && (func1.Name == PseudoFunctions.CONVERT || func1.Name == "DateTime"))) { var left = PseudoFunctions.MakeConvert(new SqlDataType(leftType), new SqlDataType(leftType), exprExpr.Expr1); exprExpr = new SqlPredicate.ExprExpr(left, exprExpr.Operator, exprExpr.Expr2, null); } if (!(exprExpr.Expr2 is SqlFunction func2 && (func2.Name == PseudoFunctions.CONVERT || func2.Name == "DateTime"))) { var right = PseudoFunctions.MakeConvert(new SqlDataType(rightType), new SqlDataType(rightType), exprExpr.Expr2); exprExpr = new SqlPredicate.ExprExpr(exprExpr.Expr1, exprExpr.Operator, right, null); } predicate = exprExpr; } } predicate = base.ConvertPredicateImpl(predicate, visitor); return(predicate); }
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); } }
static void BuildSubQueryJoin( ExpressionBuilder builder, IBuildContext outerKeyContext, Expression outerKeySelector, Expression innerKeySelector, IBuildContext subQueryKeyContext, SelectQuery subQuerySelect) { var predicate = builder.ConvertObjectComparison( ExpressionType.Equal, outerKeyContext, outerKeySelector, subQueryKeyContext, innerKeySelector); if (predicate == null) { predicate = new SqlPredicate.ExprExpr( builder.ConvertToSql(outerKeyContext, outerKeySelector), SqlPredicate.Operator.Equal, builder.ConvertToSql(subQueryKeyContext, innerKeySelector), Common.Configuration.Linq.CompareNullsAsValues ? true : null); } subQuerySelect.Where.SearchCondition.Conditions.Add(new SqlCondition(false, predicate)); }
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 : (bool?)null); } condition.Conditions.Add(new SqlCondition(false, predicate)); }
static void BuildJoin( ExpressionBuilder builder, SqlFromClause.Join join, 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)); predicate = builder.Convert(outerKeyContext, predicate); } join.JoinedTable.Condition.Conditions.Add(new SqlCondition(false, predicate)); }
public AssociatedTableContext( [JetBrains.Annotations.NotNull] ExpressionBuilder builder, [JetBrains.Annotations.NotNull] TableContext parent, [JetBrains.Annotations.NotNull] AssociationDescriptor association ) : base(builder, parent.SelectQuery) { if (builder == null) { throw new ArgumentNullException(nameof(builder)); } if (parent == null) { throw new ArgumentNullException(nameof(parent)); } if (association == null) { throw new ArgumentNullException(nameof(association)); } var type = association.MemberInfo.GetMemberType(); var left = association.CanBeNull; if (typeof(IEnumerable).IsSameOrParentOf(type)) { var eTypes = type.GetGenericArguments(typeof(IEnumerable <>)); type = eTypes != null && eTypes.Length > 0 ? eTypes[0] : type.GetListItemType(); IsList = true; } OriginalType = type; ObjectType = GetObjectType(); EntityDescriptor = Builder.MappingSchema.GetEntityDescriptor(ObjectType); InheritanceMapping = EntityDescriptor.InheritanceMapping; SqlTable = new SqlTable(builder.MappingSchema, ObjectType); Association = association; ParentAssociation = parent; SqlJoinedTable join; var queryMethod = Association.GetQueryMethod(parent.ObjectType, ObjectType); if (queryMethod != null) { var dcParam = queryMethod.Parameters[1]; var resultParam = Expression.Parameter(ObjectType); Expression dcConst = Expression.Constant(builder.DataContext); if (dcParam.Type != typeof(IDataContext)) { dcConst = Expression.Convert(dcConst, dcParam.Type); } var body = queryMethod.Body.Transform(e => e == dcParam ? dcConst : e); body = Expression.Convert(body, typeof(IEnumerable <>).MakeGenericType(ObjectType)); queryMethod = Expression.Lambda(body, queryMethod.Parameters[0]); var selectManyMethodInfo = _selectManyMethodInfo.MakeGenericMethod(parent.ObjectType, ObjectType, ObjectType); var resultLamba = Expression.Lambda(resultParam, Expression.Parameter(parent.ObjectType), resultParam); var selectManyMethod = Expression.Call(null, selectManyMethodInfo, parent.Expression, queryMethod, resultLamba); var ownerTableSource = SelectQuery.From.Tables[0]; _innerContext = builder.BuildSequence(new BuildInfo(this, selectManyMethod, new SelectQuery()) { IsAssociationBuilt = true }); var associationQuery = _innerContext.SelectQuery; if (associationQuery.Select.From.Tables.Count < 1) { throw new LinqToDBException("Invalid association query. It is not possible to inline query."); } var foundIndex = associationQuery.Select.From.Tables.FindIndex(t => t.Source is SqlTable sqlTable && QueryHelper.IsEqualTables(sqlTable, parent.SqlTable)); if (foundIndex < 0) { throw new LinqToDBException("Invalid association query. It is not possible to inline query. Can not find owner table."); } var sourceToReplace = associationQuery.Select.From.Tables[foundIndex]; if (left) { foreach (var joinedTable in sourceToReplace.Joins) { if (joinedTable.JoinType == JoinType.Inner) { joinedTable.JoinType = JoinType.Left; } else if (joinedTable.JoinType == JoinType.CrossApply) { joinedTable.JoinType = JoinType.OuterApply; } joinedTable.IsWeak = true; } } ownerTableSource.Joins.AddRange(sourceToReplace.Joins); // prepare fields mapping to replace fields that will be generated by association query _replaceMap = ((SqlTable)sourceToReplace.Source).Fields.Values.ToDictionary(f => (ISqlExpression)f, f => parent.SqlTable.Fields[f.Name]); ownerTableSource.Walk(false, e => { if (_replaceMap.TryGetValue(e, out var newField)) { return(newField); } return(e); }); ParentAssociationJoin = sourceToReplace.Joins.FirstOrDefault(); join = ParentAssociationJoin; // add rest of tables SelectQuery.From.Tables.AddRange(associationQuery.Select.From.Tables.Where(t => t != sourceToReplace)); } else { var psrc = parent.SelectQuery.From[parent.SqlTable]; join = left ? SqlTable.WeakLeftJoin().JoinedTable : SqlTable.WeakInnerJoin().JoinedTable; ParentAssociationJoin = join; psrc.Joins.Add(join); 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); } // join.Field(field1).Equal.Field(field2); ISqlPredicate predicate = new SqlPredicate.ExprExpr( field1, SqlPredicate.Operator.Equal, field2); predicate = builder.Convert(parent, predicate); join.Condition.Conditions.Add(new SqlCondition(false, predicate)); } if (ObjectType != OriginalType) { var predicate = Builder.MakeIsPredicate(this, OriginalType); if (predicate.GetType() != typeof(SqlPredicate.Expr)) { join.Condition.Conditions.Add(new SqlCondition(false, predicate)); } } RegularConditionCount = join.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, join.Condition.Conditions, false); } } if (!association.AliasName.IsNullOrEmpty() && join != null) { join.Table.Alias = association.AliasName; } else { if (!Common.Configuration.Sql.AssociationAlias.IsNullOrEmpty() && join != null) { join.Table.Alias = string.Format(Common.Configuration.Sql.AssociationAlias, association.MemberInfo.Name); } } Init(false); }
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); }
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 AssociatedTableContext( [JetBrains.Annotations.NotNull] ExpressionBuilder builder, [JetBrains.Annotations.NotNull] TableContext parent, [JetBrains.Annotations.NotNull] AssociationDescriptor association ) : base(builder, parent.SelectQuery) { if (builder == null) { throw new ArgumentNullException(nameof(builder)); } if (parent == null) { throw new ArgumentNullException(nameof(parent)); } if (association == null) { throw new ArgumentNullException(nameof(association)); } var type = association.MemberInfo.GetMemberType(); var left = association.CanBeNull; if (typeof(IEnumerable).IsSameOrParentOf(type)) { var eTypes = type.GetGenericArguments(typeof(IEnumerable <>)); type = eTypes != null && eTypes.Length > 0 ? eTypes[0] : type.GetListItemType(); IsList = true; } OriginalType = type; ObjectType = GetObjectType(); EntityDescriptor = Builder.MappingSchema.GetEntityDescriptor(ObjectType); InheritanceMapping = EntityDescriptor.InheritanceMapping; SqlTable = new SqlTable(builder.MappingSchema, ObjectType); var psrc = parent.SelectQuery.From[parent.SqlTable]; var join = left ? SqlTable.WeakLeftJoin() : SqlTable.WeakInnerJoin(); if (!association.AliasName.IsNullOrEmpty()) { join.JoinedTable.Table.Alias = association.AliasName; } else { if (!Common.Configuration.Sql.AssociationAlias.IsNullOrEmpty()) { join.JoinedTable.Table.Alias = string.Format(Common.Configuration.Sql.AssociationAlias, association.MemberInfo.Name); } } Association = association; ParentAssociation = parent; ParentAssociationJoin = join.JoinedTable; psrc.Joins.Add(join.JoinedTable); 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); } // join.Field(field1).Equal.Field(field2); ISqlPredicate predicate = new SqlPredicate.ExprExpr( field1, SqlPredicate.Operator.Equal, field2); predicate = builder.Convert(parent, predicate); join.JoinedTable.Condition.Conditions.Add(new SqlCondition(false, predicate)); } if (ObjectType != OriginalType) { var predicate = Builder.MakeIsPredicate(this, OriginalType); if (predicate.GetType() != typeof(SqlPredicate.Expr)) { join.JoinedTable.Condition.Conditions.Add(new SqlCondition(false, predicate)); } } RegularConditionCount = join.JoinedTable.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, join.JoinedTable.Condition.Conditions, false); } Init(false); }
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); }