public virtual Expression Translate(MethodCallExpression methodCallExpression) { ThrowIf.Argument.IsNull(methodCallExpression, nameof(methodCallExpression)); if (ReferenceEquals(methodCallExpression.Method, _methodInfo)) { var argument = methodCallExpression.Arguments.Count == 1 ? (methodCallExpression.Arguments[0] as ConstantExpression)?.Value as string : null; var sqlArguments = new List <Expression>(); sqlArguments.Add(ConstantExpression.Constant("%")); if (argument != null) { sqlArguments.Add(Expression.Constant(argument)); } else { sqlArguments.Add(methodCallExpression.Arguments[0]); } sqlArguments.Add(ConstantExpression.Constant("%")); var concatFunctionExpression = new SqlFunctionExpression("concat", methodCallExpression.Type, sqlArguments); return(new LikeExpression( methodCallExpression.Object, concatFunctionExpression)); } return(null); }
public override Expression Visit(Expression node) { if (!RequiresParameterVisitor.RequiresParameter(node)) { Expression <Func <object> > funct = () => new object(); funct = Expression.Lambda <Func <object> >(Expression.Convert(node, typeof(object)), funct.Parameters); object res = funct.Compile()(); return(ConstantExpression.Constant(res, node.Type)); } return(base.Visit(node)); }
public static Expression <Func <T, bool> > GetParameterExpression <T>(string key, string value) { ParameterExpression parameterExpression = Expression.Parameter(typeof(T), "x"); Expression field = Expression.Property(parameterExpression, typeof(T).GetProperty(key)); Expression constantExpression = ConstantExpression.Constant(value, typeof(string)); MethodCallExpression equals = Expression.Call(field, typeof(T).GetMethod("Equals"), new Expression[] { constantExpression }); Expression <Func <T, bool> > lambda = Expression.Lambda <Func <T, bool> >(equals, new ParameterExpression[] { parameterExpression }); return(lambda); }
protected virtual Expression CompileRegistrationIntoExpression(IRegistration registration) { if (registration == null) { throw new ArgumentNullException("registration"); } // Check if it is an InstanceRegistration<> if (ReflectionHelper.IsInstanceOfGenericType(typeof(InstanceRegistration <>), registration) && _targetRegistration.CompileMode == CompileMode.Delegate) { var instance = registration.GetInstance(); return(ConstantExpression.Constant(instance)); } // Check if it is an IExpressionRegistration var expReg = registration as IExpressionRegistration; if (expReg != null) { // Check if the lifetime can be compiled if (expReg.Lifetime is TransientLifetime) { // Keep visiting and try to inline the expression of the Registration found // Recursive call until deepest nested ExpressionRegistration with TransientLifetime is found // About to visit a child to the current - current becomes parent and registration/child to visit becomes current var parent = _currentVisitedRegistration; _currentVisitedRegistration = expReg; // Visit - visit the body and return it so it is inlined within the original Expression<Func<IResolver, object>> var expression = Visit(expReg.Expression.Body); // Returned from visiting child - change it back again _currentVisitedRegistration = parent; return(expression); } if (expReg.Lifetime is ContainerLifetime && _targetRegistration.CompileMode == CompileMode.Delegate) { var instance = expReg.GetInstance(); return(ConstantExpression.Constant(instance)); } } // Returning null will make it keep visiting return(null); }
public static Expression GetNode(string token) { object constant = IsBool(token) ? Cast <bool>(token, bool.TryParse) : IsInt(token) ? Cast <int>(token, int.TryParse) : IsFloat(token) ? Cast <float>(token, float.TryParse) : IsChar(token) ? Cast <char>(token, char.TryParse) : IsDate(token) ? Cast <DateTime>(token, DateTime.TryParse) : IsProp(token) ? PropChecker.GetProp(token) : GetString(token); if (constant == null) { throw new Exception($"Invalid constant value {token}"); } return(ConstantExpression.Constant(constant)); }
private static void EscapeForLike(Expression exp, StringBuilder queryBuilder, Action <Expression> visitExpression) { var ce = exp as ConstantExpression; if (ce != null) { var value = ce.Value as string; if (value.IndexOfAny(EscapeChars) >= 0) { visitExpression(ConstantExpression.Constant(value.Replace(@"\", @"\\").Replace("_", "\\_").Replace("%", "\\%"), typeof(string))); } else { visitExpression(ce); } } else { queryBuilder.Append(" REPLACE(REPLACE(REPLACE("); visitExpression(exp); queryBuilder.Append(@", '\','\\'), '_','\_'), '%','\%') "); } }
private static void EscapeForLike(bool equal, bool ignoreCase, MethodCallExpression mce, StringBuilder queryBuilder, Action <Expression> visitExpression) { var first = mce.Arguments[0]; var second = mce.Arguments[1]; var ce = first as ConstantExpression; if (ce != null) { if (ignoreCase) { visitExpression(ConstantExpression.Constant((ce.Value as string).ToUpper(), ce.Type)); } else { visitExpression(ce); } } else { if (ignoreCase) { queryBuilder.Append(" UPPER("); visitExpression(first); queryBuilder.Append(")"); } else { visitExpression(first); } } if (equal) { queryBuilder.Append(" = "); } else { queryBuilder.Append(" <> "); } ce = second as ConstantExpression; if (ce != null) { if (ignoreCase) { visitExpression(ConstantExpression.Constant((ce.Value as string).ToUpper(), ce.Type)); } else { visitExpression(ce); } } else { if (ignoreCase) { queryBuilder.Append(" UPPER("); visitExpression(second); queryBuilder.Append(")"); } else { visitExpression(second); } } }
protected override Expression VisitMethodCall(MethodCallExpression m) { if (m.Method.Name == "Where" || m.Method.Name == "First" || m.Method.Name == "FirstOrDefault") { var lambda = (LambdaExpression)StripQuotes(m.Arguments[1]); var solrQueryTranslator = new LinqToSolrQueryTranslator(_service); var fq = solrQueryTranslator.Translate(lambda.Body); sb.AppendFormat("&fq={0}", fq); var arr = StripQuotes(m.Arguments[0]); Visit(arr); return(m); } if (m.Method.Name == "Take") { var takeNumber = (int)((ConstantExpression)m.Arguments[1]).Value; _service.Configuration.Take = takeNumber; Visit(m.Arguments[0]); return(m); } if (m.Method.Name == "Skip") { var skipNumber = (int)((ConstantExpression)m.Arguments[1]).Value; _service.Configuration.Start = skipNumber; Visit(m.Arguments[0]); return(m); } if (m.Method.Name == "OrderBy" || m.Method.Name == "ThenBy") { var lambda = (LambdaExpression)StripQuotes(m.Arguments[1]); _service.CurrentQuery.AddSorting(lambda.Body, SolrSortTypes.Asc); Visit(m.Arguments[0]); return(m); } if (m.Method.Name == "OrderByDescending" || m.Method.Name == "ThenByDescending") { var lambda = (LambdaExpression)StripQuotes(m.Arguments[1]); _service.CurrentQuery.AddSorting(lambda.Body, SolrSortTypes.Desc); Visit(m.Arguments[0]); return(m); } if (m.Method.Name == "Select") { _service.CurrentQuery.Select = new LinqSolrSelect(StripQuotes(m.Arguments[1])); Visit(m.Arguments[0]); return(m); } if (m.Method.Name == "Contains") { if (m.Method.DeclaringType == typeof(string)) { var str = string.Format("*{0}*", ((ConstantExpression)StripQuotes(m.Arguments[0])).Value); Visit(BinaryExpression.Equal(m.Object, ConstantExpression.Constant(str))); return(m); } else { var arr = (ConstantExpression)StripQuotes(m.Arguments[0]); Expression lambda; if (m.Arguments.Count == 2) { lambda = StripQuotes(m.Arguments[1]); Visit(lambda); Visit(arr); } else { var newExpr = Expression.Equal(m.Object, m.Arguments[0]); var expr = new LinqToSolrQueryTranslator(_service, m.Arguments[0].Type); expr.IsMultiList = true; var multilistfq = expr.Translate(newExpr); sb.AppendFormat("{0}", multilistfq); } return(m); } } if (m.Method.Name == "StartsWith") { if (m.Method.DeclaringType == typeof(string)) { var str = string.Format("{0}*", ((ConstantExpression)StripQuotes(m.Arguments[0])).Value); Visit(BinaryExpression.Equal(m.Object, ConstantExpression.Constant(str))); return(m); } } if (m.Method.Name == "EndsWith") { if (m.Method.DeclaringType == typeof(string)) { var str = string.Format("*{0}", ((ConstantExpression)StripQuotes(m.Arguments[0])).Value); Visit(BinaryExpression.Equal(m.Object, ConstantExpression.Constant(str))); return(m); } } if (m.Method.Name == "GroupBy") { _service.CurrentQuery.IsGroupEnabled = true; var arr = StripQuotes(m.Arguments[1]); #if PORTABLE || NETCORE var solrQueryTranslator = new LinqToSolrQueryTranslator(_service, ((MemberExpression)((LambdaExpression)arr).Body).Member.DeclaringType); #else var solrQueryTranslator = new LinqToSolrQueryTranslator(_service, ((MemberExpression)((LambdaExpression)arr).Body).Member.ReflectedType); #endif _service.CurrentQuery.GroupFields.Add(solrQueryTranslator.Translate(arr)); Visit(m.Arguments[0]); return(m); //throw new Exception("The method 'GroupBy' is not supported in Solr. For native FACETS support use SolrQuaryableExtensions.GroupBySolr instead."); } throw new NotSupportedException(string.Format("The method '{0}' is not supported", m.Method.Name)); }
protected override Expression VisitConstant(ConstantExpression c) { IQueryable q = c.Value as IQueryable; if (q == null && c.Value == null) { sb.Append("NULL"); } else if (q == null) { parameterString = _Symbolizer + parameterCount.ToString(); switch (Type.GetTypeCode(c.Value.GetType())) { case TypeCode.Boolean: //sb.Append(((bool)c.Value) ? 1 : 0); this.Write(parameterString); parameterCount++; AddParameter(new DBParameter { Name = parameterString, Type = c.Type.ToString(), Size = 100, ParameterDirection = ParameterDirection.Input, Value = c.Value }); break; case TypeCode.String: //sb.Append("'"); //sb.Append(c.Value); //sb.Append("'"); this.Write(parameterString); parameterCount++; AddParameter(new DBParameter { Name = parameterString, Type = c.Type.ToString(), Size = 100, ParameterDirection = ParameterDirection.Input, Value = c.Value }); break; case TypeCode.DateTime: //sb.Append("'"); //sb.Append(c.Value); //sb.Append("'"); this.Write(parameterString); parameterCount++; AddParameter(new DBParameter { Name = parameterString, Type = c.Type.ToString(), Size = 100, ParameterDirection = ParameterDirection.Input, Value = c.Value }); break; case TypeCode.Object: //throw new NotSupportedException(string.Format("The constant for '{0}' is not supported", c.Value)); if (c.Value.GetType().Name == "List`1") { foreach (var VARIABLE in c.Value as List <string> ) { parameterString = _Symbolizer + parameterCount.ToString(); inParameterString += _Symbolizer + parameterCount.ToString() + ","; AddParameter(new DBParameter { Name = parameterString, Type = c.Type.ToString(), Size = 100, ParameterDirection = ParameterDirection.Input, Value = VARIABLE }); parameterCount++; } inParameterString = inParameterString?.Substring(0, inParameterString.Length - 1); } else if (c.Value.GetType().IsNested&& c.Value.GetType().IsNestedPrivate) { VisitConstant(ConstantExpression.Constant(c.Value.GetType().GetFields().FirstOrDefault().GetValue(c.Value))); } break; default: //sb.Append(c.Value); this.Write(parameterString); parameterCount++; AddParameter(new DBParameter { Name = parameterString, Type = c.Type.ToString(), Size = 100, ParameterDirection = ParameterDirection.Input, Value = c.Value }); break; } } return(c); }
static void Main(string[] args) { Console.WriteLine("Start TestLinqExpression"); Func <int> f1 = () => 2 + 2; int res = f1(); Console.WriteLine(res); // Теперь повторим это через Expression (заменим слагаемые) Expression exp = BinaryExpression.Add(ConstantExpression.Constant(4), ConstantExpression.Constant(4)); var f2 = Expression.Lambda <Func <int> >(exp).Compile(); res = f2(); Console.WriteLine(res); // Теперь придумываем "язык" для формул exp. Язык будет объектным. Константа c целого типа будет // соответствовать выражению ConstantExpression.Constant(c), массив object[] {"Add", object1, object2} // будет BinaryExpression.Add(object1, object2) // Делаем транслятор object -> Expression Func <object, Expression> objectTranslator = null; objectTranslator = (object ob) => { if (ob is int) { return(ConstantExpression.Constant(ob)); } return(null); }; // испытываем Expression expr2 = objectTranslator(444); Console.WriteLine(Expression.Lambda <Func <int> >(expr2).Compile()()); // Делаем другую ветвь в трансляторе objectTranslator = (object ob) => { if (ob is int) { return(ConstantExpression.Constant(ob)); } if (ob is object[]) { object[] arr = (object[])ob; if ((string)arr[0] == "Add") { return(BinaryExpression.Add(objectTranslator(arr[1]), objectTranslator(arr[1]))); } } return(null); }; // испытываем object[] myexpression = new object[] { "Add", 2222, 4444 }; // Вот он язык описания вычислений!!! Expression expr3 = objectTranslator(myexpression); Console.WriteLine(Expression.Lambda <Func <int> >(expr3).Compile()()); myexpression = new object[] { "Add", new object[] { "Add", 2, 2 }, new object[] { "Add", 2, 2 } }; Console.WriteLine(Expression.Lambda <Func <int> >(objectTranslator(myexpression)).Compile()()); }
/// <summary> /// Build criteria based on current instance's search values. /// </summary> /// <returns>The Expression built. Null of not any of the proeprties has value.</returns> public Expression <Func <PeriodSchedule, bool> > BuildCriteria() { MethodInfo stringNullOrEmpty = typeof(string).GetMethod("IsNullOrEmpty"); MethodInfo contains = typeof(string).GetMethod("Contains", new[] { typeof(string) }); MethodInfo compareTo = typeof(string).GetMethod("CompareTo", new[] { typeof(string) }); MethodInfo startsWith = typeof(string).GetMethod("StartsWith", new[] { typeof(string) }); ParameterExpression param = Expression.Parameter(typeof(PeriodSchedule), "periodSchedules"); Expression expr = null; Expression startDateProperty = Expression.PropertyOrField(param, "DateDue"); Expression endDateProperty = Expression.PropertyOrField(param, "DateDue"); Expression balancedProperty = Expression.PropertyOrField(param, "Balance"); Expression unBalancedPropery = Expression.PropertyOrField(param, "Balance"); Expression propertyNoProperty = Expression.PropertyOrField(param, "PropertyNo"); Expression fromDateValue = ConstantExpression.Constant(this.StartDate, typeof(string)); Expression toDateValue = ConstantExpression.Constant(this.EndDate, typeof(string)); Expression includePaidValue = ConstantExpression.Constant(this.PaidFilter, typeof(bool)); Expression includeNotPaidValue = ConstantExpression.Constant(this.UnPaidFilter, typeof(bool)); Expression propertyNoValue = ConstantExpression.Constant(this.PropertyNo, typeof(string)); bool expressionAssigned = false; if (PaidFilter) { ConstantExpression zeroConstant = Expression.Constant(0); Expression temp = Expression.Equal(balancedProperty, zeroConstant); expr = temp; if (!expressionAssigned) { expressionAssigned = true; } } if (UnPaidFilter) { ConstantExpression zeroConst = Expression.Constant(0); if (!expressionAssigned) { Expression temp = Expression.GreaterThan(unBalancedPropery, zeroConst); expr = temp; expressionAssigned = true; } else { Expression temp = Expression.GreaterThan(unBalancedPropery, zeroConst); expr = Expression.AndAlso(expr, temp); } } if (!string.IsNullOrEmpty(StartDate)) { if (!expressionAssigned) { ConstantExpression constant = Expression.Constant(0); Expression temp = Expression.GreaterThanOrEqual( Expression.Call(startDateProperty, compareTo, fromDateValue), constant); expr = temp; expressionAssigned = true; } else { ConstantExpression constant = Expression.Constant(0); Expression temp = Expression.GreaterThanOrEqual( Expression.Call(startDateProperty, compareTo, fromDateValue), constant); expr = Expression.AndAlso(expr, temp); } } if (!string.IsNullOrEmpty(EndDate)) { if (!expressionAssigned) { ConstantExpression constant = Expression.Constant(0); Expression temp = Expression.LessThanOrEqual( Expression.Call(endDateProperty, compareTo, toDateValue), constant); expr = temp; expressionAssigned = true; } else { ConstantExpression constant = Expression.Constant(0); Expression temp = Expression.LessThanOrEqual( Expression.Call(endDateProperty, compareTo, toDateValue), constant); expr = Expression.AndAlso(expr, temp); } } if (!string.IsNullOrEmpty(PropertyNo)) { if (!expressionAssigned) { Expression temp = Expression.Call(propertyNoProperty, startsWith, propertyNoValue); expr = temp; expressionAssigned = true; } else { Expression temp = Expression.Call(propertyNoProperty, startsWith, propertyNoValue); expr = Expression.AndAlso(expr, temp); } } Expression <Func <PeriodSchedule, bool> > criteria = null; if (expr != null) { criteria = Expression.Lambda <Func <PeriodSchedule, bool> >(expr, param); } return(criteria); }
/// <summary> /// 访问方法 /// </summary> /// <param name="node"></param> /// <returns></returns> protected override Expression VisitMethodCall(MethodCallExpression node) { var methodInfo = node.Method; if (methodInfo.DeclaringType == typeof(QueryConditional) && methodInfo.Name == "Eq") { var dd = node.Object; this.Visit(dd); } if (methodInfo.DeclaringType == typeof(SQLMethod)) { bool tleft = isleft; isleft = true; if (node.Arguments.Count > 0) { this.Visit(node.Arguments[0]); } isleft = tleft; switch (methodInfo.Name) { case "IsNull": sb.Append(" is NULL"); break; case "IsNotNull": sb.Append(" is NOT NULL"); break; default: string methodName = methodInfo.Name.ToLower(); string funcName = Provider.GetFunctionNameCallback(methodName, null); if (!string.IsNullOrEmpty(funcName)) { sb.Append(funcName); } else { throw new NotSupportedException( string.Format("{0}数据库中不支持函数{1}", Provider.Database, methodName)); } break; } } if (methodInfo.DeclaringType == typeof(string)) { switch (methodInfo.Name) { case "Contains": VisitStringFuncByOneParamter(node, v => string.Format("%{0}%", v)); break; case "StartsWith": VisitStringFuncByOneParamter(node, v => string.Format("{0}%", v)); break; case "EndsWith": VisitStringFuncByOneParamter(node, v => string.Format("%{0}", v)); break; } } if (methodInfo.DeclaringType == typeof(Queryable)) { switch (methodInfo.Name) { case "Where": sb.Append("SELECT * FROM "); if (node.Arguments[0].NodeType == ExpressionType.Constant) { LambdaExpression tabLambda = (LambdaExpression)StripQuotes(node.Arguments[1]); if (!Ass.Keys.Contains(tabLambda.Parameters[0].Type)) { Ass.TryAdd(tabLambda.Parameters[0].Type, tabLambda.Parameters[0].Name); } this.Visit(node.Arguments[0]); sb.Append(" WHERE "); LambdaExpression lambda = (LambdaExpression)StripQuotes(node.Arguments[1]); this.Visit(lambda.Body); } else // flow join function { var d1 = (LambdaExpression)StripQuotes((node.Arguments[0] as MethodCallExpression).Arguments[2]); var db1 = (d1.Body as MemberExpression); //body.member is column var mem1 = db1.Expression as MemberExpression; string asName = string.Empty; if (mem1 != null) { asName = mem1.Member.Name; //body.expression.member is table As Name } else { asName = (db1.Expression as ParameterExpression).Name; } if (!Ass.Keys.Contains(db1.Member.DeclaringType)) { Ass.TryAdd(db1.Member.DeclaringType, asName); } var d2 = (LambdaExpression)StripQuotes((node.Arguments[0] as MethodCallExpression).Arguments[3]); if (!Ass.Keys.Contains(d2.Parameters[0].Type)) { Ass.TryAdd(d2.Parameters[0].Type, d2.Parameters[0].Name); } //var t1 = (node.Arguments[0] as MethodCallExpression).Arguments[0]; object obj = (IQueryable)Activator.CreateInstance( typeof(LinqQuery <>).MakeGenericType(db1.Member.DeclaringType)); this.Visit(ConstantExpression.Constant(obj)); this.Visit(node.Arguments[0]); sb.Append(" WHERE "); LambdaExpression lambda = (LambdaExpression)StripQuotes(node.Arguments[1]); this.Visit(lambda.Body); } break; case "OrderBy": this.Visit(node.Arguments[0]); sb.Append(" ORDER BY "); LambdaExpression ordbyExp = (LambdaExpression)StripQuotes(node.Arguments[1]); this.Visit(ordbyExp); sb.Append(" ASC "); break; case "OrderByDescending": this.Visit(node.Arguments[0]); sb.Append(" ORDER BY "); LambdaExpression ordbyDescExp = (LambdaExpression)StripQuotes(node.Arguments[1]); this.Visit(ordbyDescExp); sb.Append(" DESC "); break; case "Join": var me = node.Arguments[0] as MethodCallExpression; if (me != null) { this.Visit(me); } #region jj var d1v = (LambdaExpression)StripQuotes((node.Arguments[2])); var db1v = (d1v.Body as MemberExpression); //body.member is column var mem1v = db1v.Expression as MemberExpression; string asNamev = string.Empty; if (mem1v != null) { asNamev = mem1v.Member.Name; //body.expression.member is table As Name } else { asNamev = (db1v.Expression as ParameterExpression).Name; } if (!Ass.Keys.Contains(db1v.Member.DeclaringType)) { Ass.TryAdd(db1v.Member.DeclaringType, asNamev); } var d2v = (LambdaExpression)StripQuotes((node.Arguments[3])); if (!Ass.Keys.Contains(d2v.Parameters[0].Type)) { Ass.TryAdd(d2v.Parameters[0].Type, d2v.Parameters[0].Name); } #endregion sb.Append(" JOIN "); var t2 = node.Arguments[1]; this.Visit(t2); sb.Append(" ON "); var tv1 = (LambdaExpression)StripQuotes(node.Arguments[2]); this.Visit(tv1); sb.Append(" = "); var tv2 = (LambdaExpression)StripQuotes(node.Arguments[3]); this.Visit(tv2); break; case "Select": this.Visit(node.Arguments[0]); var newObjectV = (LambdaExpression)StripQuotes(node.Arguments[1]); SelectFormater sf = new SelectFormater(Ass); sf.Visit(newObjectV.Body); this.sb.Replace("*", sf.ToString()); break; default: this.Visit(node.Arguments[0]); break; } } if (methodInfo.DeclaringType == typeof(Enumerable)) { this.Visit(node.Arguments[1]); sb.Append(" In "); this.Visit(node.Arguments[0]); } if (methodInfo.DeclaringType == typeof(System.Convert)) { this.Visit(node.Arguments[0]); } return(node); }
/// <summary> /// Build criteria based on current instance's search values. /// </summary> /// <returns>The Expression built. Null of not any of the proeprties has value.</returns> public Expression <Func <Contract, bool> > BuildCriteria() { MethodInfo stringNullOrEmpty = typeof(string).GetMethod("IsNullOrEmpty"); MethodInfo contains = typeof(string).GetMethod("Contains", new[] { typeof(string) }); MethodInfo compareTo = typeof(string).GetMethod("CompareTo", new[] { typeof(string) }); MethodInfo startsWith = typeof(string).GetMethod("StartsWith", new[] { typeof(string) }); ParameterExpression param = Expression.Parameter(typeof(Contract), "contract"); Expression expr = null; Expression startDateProperty = Expression.PropertyOrField(param, "StartDate"); Expression endDateProperty = Expression.PropertyOrField(param, "EndDate"); Expression propertyNoProperty = Expression.PropertyOrField(param, "PropertyNo"); Expression customerNoProperty = Expression.PropertyOrField(param, "CustomerId"); Expression fromDateValue = ConstantExpression.Constant(this.StartDate, typeof(string)); Expression toDateValue = ConstantExpression.Constant(this.EndDate, typeof(string)); Expression propertyNoValue = ConstantExpression.Constant(this.PropertyNo, typeof(string)); Expression customerNoValue = ConstantExpression.Constant(this.CustomerNo, typeof(int)); bool expressionAssigned = false; if (!string.IsNullOrEmpty(PropertyNo)) { Expression temp = Expression.Call(propertyNoProperty, startsWith, propertyNoValue); expr = temp; if (!expressionAssigned) { expressionAssigned = true; } } if (CustomerNo > 0) { ConstantExpression trueConstant = Expression.Constant(true, typeof(bool)); if (!expressionAssigned) { Expression temp = Expression.Equal(customerNoProperty, customerNoValue); expr = temp; expressionAssigned = true; } else { Expression temp = Expression.Equal(customerNoProperty, customerNoValue); expr = Expression.AndAlso(expr, temp); } } if (!string.IsNullOrEmpty(StartDate)) { if (!expressionAssigned) { ConstantExpression constant = Expression.Constant(0); Expression temp = Expression.GreaterThanOrEqual( Expression.Call(startDateProperty, compareTo, fromDateValue), constant); expr = temp; expressionAssigned = true; } else { ConstantExpression constant = Expression.Constant(0); Expression temp = Expression.GreaterThanOrEqual( Expression.Call(startDateProperty, compareTo, fromDateValue), constant); expr = Expression.AndAlso(expr, temp); } } if (!string.IsNullOrEmpty(EndDate)) { if (!expressionAssigned) { ConstantExpression constant = Expression.Constant(0); Expression temp = Expression.LessThanOrEqual( Expression.Call(endDateProperty, compareTo, toDateValue), constant); expr = temp; expressionAssigned = true; } else { ConstantExpression constant = Expression.Constant(0); Expression temp = Expression.LessThanOrEqual( Expression.Call(endDateProperty, compareTo, toDateValue), constant); expr = Expression.AndAlso(expr, temp); } } Expression <Func <Contract, bool> > criteria = null; if (expr != null) { criteria = Expression.Lambda <Func <Contract, bool> >(expr, param); } //if (criteria != null) //{ // return criteria.Compile(); //} //else //{ // return null; //} return(criteria); }
public void GetIndexEvents_ExpressionTree() { using (var db = Helper.CreatePCORIDataContext(ConnectionString)) { ParameterExpression pe_proQueryType = ParameterExpression.Parameter(typeof(PCORIQueryBuilder.Model.ReportedOutcome), "pro"); ParameterExpression pe_clinicalTrialsQueryType = ParameterExpression.Parameter(typeof(ClinicalTrial), "tr"); ConstantExpression ce_daysBefore = ConstantExpression.Constant(0f, typeof(float)); ConstantExpression ce_daysAfter = ConstantExpression.Constant(300f, typeof(float)); ConstantExpression ce_proItemName = ConstantExpression.Constant("HOSPITALIZATION_DATE", typeof(string)); ConstantExpression ce_trialID = ConstantExpression.Constant("FAKE_TRIAL-15", typeof(string)); var outerSelector = Expression.Lambda(Expression.Property(pe_proQueryType, "PatientID"), pe_proQueryType); var innerSelector = Expression.Lambda(Expression.Property(pe_clinicalTrialsQueryType, "PatientID"), pe_clinicalTrialsQueryType); Type joinResultType = Objects.Dynamic.TypeBuilderHelper.CreateType("IndexEvents", new[] { new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "PRO_ID", Type = "System.String" }, new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "PatientID", Type = "System.String" }, new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "ItemName", Type = "System.String" }, new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "ResponseText", Type = "System.String" }, new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "ResponseNumber", Type = typeof(double?).ToString() }, new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "ResponseSequence", Type = "System.String" }, new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "ParticipantID", Type = "System.String" }, new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "TrialID", Type = "System.String" }, new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "TimeWindowAfter", Type = typeof(float).ToString() }, new DTO.QueryComposer.QueryComposerResponsePropertyDefinitionDTO { Name = "TimeWindowBefore", Type = typeof(float).ToString() } }); IEnumerable <MemberBinding> joinBindings = new[] { Expression.Bind(joinResultType.GetProperty("PRO_ID"), Expression.Property(pe_proQueryType, pe_proQueryType.Type.GetProperty("ID"))), Expression.Bind(joinResultType.GetProperty("PatientID"), Expression.Property(pe_proQueryType, pe_proQueryType.Type.GetProperty("PatientID"))), Expression.Bind(joinResultType.GetProperty("ItemName"), Expression.Property(pe_proQueryType, pe_proQueryType.Type.GetProperty("ItemName"))), Expression.Bind(joinResultType.GetProperty("ResponseText"), Expression.Property(pe_proQueryType, pe_proQueryType.Type.GetProperty("ResponseText"))), Expression.Bind(joinResultType.GetProperty("ResponseNumber"), Expression.Property(pe_proQueryType, pe_proQueryType.Type.GetProperty("ResponseNumber"))), Expression.Bind(joinResultType.GetProperty("ResponseSequence"), Expression.Property(pe_proQueryType, pe_proQueryType.Type.GetProperty("MeasureSequence"))), Expression.Bind(joinResultType.GetProperty("ParticipantID"), Expression.Property(pe_clinicalTrialsQueryType, pe_clinicalTrialsQueryType.Type.GetProperty("ParticipantID"))), Expression.Bind(joinResultType.GetProperty("TrialID"), Expression.Property(pe_clinicalTrialsQueryType, pe_clinicalTrialsQueryType.Type.GetProperty("TrialID"))), Expression.Bind(joinResultType.GetProperty("TimeWindowAfter"), Expression.Add(Expression.Convert(Expression.Property(pe_proQueryType, pe_proQueryType.Type.GetProperty("ResponseNumber")), typeof(float)), ce_daysAfter)), Expression.Bind(joinResultType.GetProperty("TimeWindowBefore"), Expression.Subtract(Expression.Convert(Expression.Property(pe_proQueryType, pe_proQueryType.Type.GetProperty("ResponseNumber")), typeof(float)), ce_daysBefore)) }; var pe_joinType = ParameterExpression.Parameter(joinResultType, "j"); var resultSelector = Expression.Lambda( Expression.MemberInit(Expression.New(joinResultType), joinBindings), pe_proQueryType, pe_clinicalTrialsQueryType ); MethodCallExpression joinCall = Expression.Call( typeof(Queryable), "Join", new Type[] { typeof(ReportedOutcome), //outer typeof(ClinicalTrial), //inner typeof(string), //key joinResultType }, new Expression[] { db.ReportedOutcomeCommonMeasures.AsQueryable().Expression, db.ClinicalTrials.AsQueryable().Expression, Expression.Quote(outerSelector), Expression.Quote(innerSelector), Expression.Quote(resultSelector) } ); BinaryExpression itemNameExprs = BinaryExpression.Equal(Expression.Property(pe_joinType, "ItemName"), ce_proItemName); BinaryExpression trialIDExprs = Expression.Equal(Expression.Property(pe_joinType, "TrialID"), ce_trialID); BinaryExpression requireResponseNumExprs = Expression.NotEqual(Expression.Property(pe_joinType, "ResponseNumber"), Expression.Constant(null)); BinaryExpression predicate = Expression.AndAlso(Expression.AndAlso(itemNameExprs, trialIDExprs), requireResponseNumExprs); var whereCall = Expression.Call(typeof(Queryable), "Where", new[] { joinResultType }, joinCall, Expression.Quote(Expression.Lambda(predicate, pe_joinType))); var query = db.ReportedOutcomeCommonMeasures.AsQueryable().Provider.CreateQuery(whereCall); Logger.Debug(query.Expression); Logger.Debug(query.ToTraceQuery()); } //left outer join on procedures where procedure ID is null (omit procedures), use group join //https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins }
/// <summary> /// Build criteria based on current instance's search values. /// </summary> /// <returns>The Expression built. Null of not any of the proeprties has value.</returns> public Func <SearchablePaymentDetails, bool> BuildCriteria() { MethodInfo stringNullOrEmpty = typeof(string).GetMethod("IsNullOrEmpty"); MethodInfo contains = typeof(string).GetMethod("Contains", new[] { typeof(string) }); MethodInfo compareTo = typeof(string).GetMethod("CompareTo", new[] { typeof(string) }); MethodInfo startsWith = typeof(string).GetMethod("StartsWith", new[] { typeof(string) }); ParameterExpression param = Expression.Parameter(typeof(SearchablePaymentDetails), "searchablePaymentDetails"); Expression expr = null; Expression sexProperty = Expression.PropertyOrField(param, "Sex"); Expression memberCodeProperty = Expression.PropertyOrField(param, "MemberCode"); Expression memberNamePropery = Expression.PropertyOrField(param, "MemberName"); Expression loanAmountProperty = Expression.PropertyOrField(param, "LoanAmount"); Expression femaleValue = ConstantExpression.Constant(Sex.Female, typeof(Sex)); Expression maleValue = ConstantExpression.Constant(Sex.Male, typeof(Sex)); Expression memberCodeValue = ConstantExpression.Constant(this.MemberCode, typeof(int)); Expression memberNameValue = ConstantExpression.Constant(this.MemberName, typeof(string)); Expression loanAmountValue = ConstantExpression.Constant(this.LoanAmount, typeof(decimal)); bool expressionAssigned = false; if (Female) { //ConstantExpression femalConstant = Expression.Constant(Sex.Female); Expression temp = Expression.Equal(sexProperty, femaleValue); expr = temp; if (!expressionAssigned) { expressionAssigned = true; } } if (Male) { //ConstantExpression maleConst = Expression.Constant(Sex.Male); if (!expressionAssigned) { Expression temp = Expression.Equal(sexProperty, maleValue); expr = temp; expressionAssigned = true; } else { Expression temp = Expression.Equal(sexProperty, maleValue); expr = Expression.AndAlso(expr, temp); } } if (!string.IsNullOrEmpty(MemberName)) { if (!expressionAssigned) { Expression temp = Expression.Call(memberNamePropery, contains, memberNameValue); expr = temp; expressionAssigned = true; } else { Expression temp = Expression.Call(memberNamePropery, contains, memberNameValue); expr = Expression.AndAlso(expr, temp); } } if (LoanAmount > 0) { if (!expressionAssigned) { Expression temp = Expression.Equal(loanAmountProperty, loanAmountValue); expr = temp; expressionAssigned = true; } else { Expression temp = Expression.Equal(loanAmountValue, loanAmountProperty); expr = Expression.AndAlso(expr, temp); } } if (MemberCode > 0) { if (!expressionAssigned) { Expression temp = Expression.Equal(Expression.Convert(memberCodeProperty, typeof(int)), memberCodeValue); expr = temp; expressionAssigned = true; } else { Expression temp = Expression.Equal(memberCodeProperty, memberCodeValue); expr = Expression.AndAlso(expr, temp); } } Expression <Func <SearchablePaymentDetails, bool> > criteria = null; if (expr != null) { criteria = Expression.Lambda <Func <SearchablePaymentDetails, bool> >(expr, param); } if (criteria != null) { return(criteria.Compile()); } else { return(null); } }
private static void Compare(bool before, bool after, MethodCallExpression methodCall, StringBuilder queryBuilder, Action <Expression> visitExpression, QueryContext context) { bool ignoreCase = false; ConstantExpression ce; if (methodCall.Arguments.Count == 2) { ce = methodCall.Arguments[1] as ConstantExpression; switch ((StringComparison)ce.Value) { case StringComparison.CurrentCulture: case StringComparison.InvariantCulture: case StringComparison.Ordinal: break; default: ignoreCase = true; break; } } if (context.InSelect) { queryBuilder.Append(" CASE WHEN"); } else { queryBuilder.Append("("); } ce = methodCall.Object as ConstantExpression; if (ce != null) { if (ignoreCase) { visitExpression(ConstantExpression.Constant((ce.Value as string).ToUpper(), ce.Type)); } else { visitExpression(ce); } } else { if (ignoreCase) { queryBuilder.Append(" UPPER("); visitExpression(methodCall.Object); queryBuilder.Append(")"); } else { visitExpression(methodCall.Object); } } bool asLike = before || after; if (asLike) { queryBuilder.Append(" LIKE "); } else { queryBuilder.Append(" = "); } if (before) { queryBuilder.Append("'%' || "); } ce = methodCall.Arguments[0] as ConstantExpression; if (ce != null) { if (asLike) { var value = ce.Value as string; if (ignoreCase) { value = value.ToUpper(); } value = value.Replace(@"\", @"\\").Replace(@"_", @"\_").Replace(@"%", @"\%"); visitExpression(ConstantExpression.Constant(value, ce.Type)); } else if (ignoreCase) { visitExpression(ConstantExpression.Constant((ce.Value as string).ToUpper(), ce.Type)); } else { visitExpression(ce); } } else { if (asLike) { if (ignoreCase) { queryBuilder.Append(" UPPER("); } queryBuilder.Append("REPLACE(REPLACE(REPLACE("); visitExpression(methodCall.Object); queryBuilder.Append(@", '\','\\'), '_','\_'), '%','\%')"); if (ignoreCase) { queryBuilder.Append(")"); } } else if (ignoreCase) { queryBuilder.Append(" UPPER("); visitExpression(methodCall.Object); queryBuilder.Append(")"); } else { visitExpression(methodCall.Object); } } if (after) { queryBuilder.Append(" || '%'"); } if (asLike) { queryBuilder.Append(@" ESCAPE '\' "); } if (context.InSelect) { queryBuilder.Append(" THEN 'Y' ELSE 'N' END"); } else { queryBuilder.Append(")"); } }
protected override Expression VisitMethodCall(MethodCallExpression m) { NamedValueExpression nve; if (m.Method.DeclaringType == typeof(string)) { switch (m.Method.Name) { case "StartsWith": this.Write("("); this.Visit(m.Object); this.Write(" LIKE "); nve = m.Arguments[0] as NamedValueExpression; if (nve != null) { ConstantExpression ce = nve.Value as ConstantExpression; if (ce != null) { this.Visit(ConstantExpression.Constant(ce.Value + "%", ce.Type)); } } else { this.Visit(m.Arguments[0]); } this.Write(")"); return(m); case "EndsWith": this.Write("("); this.Visit(m.Object); this.Write(" LIKE "); nve = m.Arguments[0] as NamedValueExpression; if (nve != null) { ConstantExpression ce = nve.Value as ConstantExpression; if (ce != null) { this.Visit(ConstantExpression.Constant("%" + ce.Value, ce.Type)); } } else { this.Visit(m.Arguments[0]); } this.Write(")"); return(m); case "Contains": this.Write("("); this.Visit(m.Object); this.Write(" LIKE "); nve = m.Arguments[0] as NamedValueExpression; if (nve != null) { ConstantExpression ce = nve.Value as ConstantExpression; if (ce != null) { this.Visit(ConstantExpression.Constant("%" + ce.Value + "%", ce.Type)); } } else { this.Visit(m.Arguments[0]); } this.Write(")"); return(m); case "Concat": IList <Expression> args = m.Arguments; if (args.Count == 1 && args[0].NodeType == ExpressionType.NewArrayInit) { args = ((NewArrayExpression)args[0]).Expressions; } for (int i = 0, n = args.Count; i < n; i++) { if (i > 0) { this.Write(" + "); } this.Visit(args[i]); } return(m); case "IsNullOrEmpty": this.Write("("); this.Visit(m.Arguments[0]); this.Write(" IS NULL OR "); this.Visit(m.Arguments[0]); this.Write(" = '')"); return(m); case "ToUpper": this.Write("UPPER("); this.Visit(m.Object); this.Write(")"); return(m); case "ToLower": this.Write("LOWER("); this.Visit(m.Object); this.Write(")"); return(m); case "Replace": this.Write("REPLACE("); this.Visit(m.Object); this.Write(", "); this.Visit(m.Arguments[0]); this.Write(", "); this.Visit(m.Arguments[1]); this.Write(")"); return(m); case "Substring": this.Write("SUBSTRING("); this.Visit(m.Object); this.Write(", "); this.Visit(m.Arguments[0]); this.Write(" + 1, "); if (m.Arguments.Count == 2) { this.Visit(m.Arguments[1]); } else { this.Write("8000"); } this.Write(")"); return(m); case "Remove": this.Write("STUFF("); this.Visit(m.Object); this.Write(", "); this.Visit(m.Arguments[0]); this.Write(" + 1, "); if (m.Arguments.Count == 2) { this.Visit(m.Arguments[1]); } else { this.Write("8000"); } this.Write(", '')"); return(m); case "IndexOf": this.Write("(CHARINDEX("); this.Visit(m.Arguments[0]); this.Write(", "); this.Visit(m.Object); if (m.Arguments.Count == 2 && m.Arguments[1].Type == typeof(int)) { this.Write(", "); this.Visit(m.Arguments[1]); this.Write(" + 1"); } this.Write(") - 1)"); return(m); case "Trim": this.Write("RTRIM(LTRIM("); this.Visit(m.Object); this.Write("))"); return(m); } } else if (m.Method.DeclaringType == typeof(DateTime)) { switch (m.Method.Name) { case "op_Subtract": if (m.Arguments[1].Type == typeof(DateTime)) { this.Write("DATEDIFF("); this.Visit(m.Arguments[0]); this.Write(", "); this.Visit(m.Arguments[1]); this.Write(")"); return(m); } break; case "AddYears": this.Write("DATEADD(YYYY,"); this.Visit(m.Arguments[0]); this.Write(","); this.Visit(m.Object); this.Write(")"); return(m); case "AddMonths": this.Write("DATEADD(MM,"); this.Visit(m.Arguments[0]); this.Write(","); this.Visit(m.Object); this.Write(")"); return(m); case "AddDays": this.Write("DATEADD(DAY,"); this.Visit(m.Arguments[0]); this.Write(","); this.Visit(m.Object); this.Write(")"); return(m); case "AddHours": this.Write("DATEADD(HH,"); this.Visit(m.Arguments[0]); this.Write(","); this.Visit(m.Object); this.Write(")"); return(m); case "AddMinutes": this.Write("DATEADD(MI,"); this.Visit(m.Arguments[0]); this.Write(","); this.Visit(m.Object); this.Write(")"); return(m); case "AddSeconds": this.Write("DATEADD(SS,"); this.Visit(m.Arguments[0]); this.Write(","); this.Visit(m.Object); this.Write(")"); return(m); case "AddMilliseconds": this.Write("DATEADD(MS,"); this.Visit(m.Arguments[0]); this.Write(","); this.Visit(m.Object); this.Write(")"); return(m); } } else if (m.Method.DeclaringType == typeof(Decimal)) { switch (m.Method.Name) { case "Add": case "Subtract": case "Multiply": case "Divide": case "Remainder": this.Write("("); this.VisitValue(m.Arguments[0]); this.Write(" "); this.Write(GetOperator(m.Method.Name)); this.Write(" "); this.VisitValue(m.Arguments[1]); this.Write(")"); return(m); case "Negate": this.Write("-"); this.Visit(m.Arguments[0]); this.Write(""); return(m); case "Ceiling": case "Floor": this.Write(m.Method.Name.ToUpper()); this.Write("("); this.Visit(m.Arguments[0]); this.Write(")"); return(m); case "Round": if (m.Arguments.Count == 1) { this.Write("ROUND("); this.Visit(m.Arguments[0]); this.Write(", 0)"); return(m); } else if (m.Arguments.Count == 2 && m.Arguments[1].Type == typeof(int)) { this.Write("ROUND("); this.Visit(m.Arguments[0]); this.Write(", "); this.Visit(m.Arguments[1]); this.Write(")"); return(m); } break; case "Truncate": this.Write("ROUND("); this.Visit(m.Arguments[0]); this.Write(", 0, 1)"); return(m); } } else if (m.Method.DeclaringType == typeof(Math)) { switch (m.Method.Name) { case "Abs": case "Acos": case "Asin": case "Atan": case "Cos": case "Exp": case "Log10": case "Sin": case "Tan": case "Sqrt": case "Sign": case "Ceiling": case "Floor": this.Write(m.Method.Name.ToUpper()); this.Write("("); this.Visit(m.Arguments[0]); this.Write(")"); return(m); case "Atan2": this.Write("ATN2("); this.Visit(m.Arguments[0]); this.Write(", "); this.Visit(m.Arguments[1]); this.Write(")"); return(m); case "Log": if (m.Arguments.Count == 1) { goto case "Log10"; } break; case "Pow": this.Write("POWER("); this.Visit(m.Arguments[0]); this.Write(", "); this.Visit(m.Arguments[1]); this.Write(")"); return(m); case "Round": if (m.Arguments.Count == 1) { this.Write("ROUND("); this.Visit(m.Arguments[0]); this.Write(", 0)"); return(m); } else if (m.Arguments.Count == 2 && m.Arguments[1].Type == typeof(int)) { this.Write("ROUND("); this.Visit(m.Arguments[0]); this.Write(", "); this.Visit(m.Arguments[1]); this.Write(")"); return(m); } break; case "Truncate": this.Write("ROUND("); this.Visit(m.Arguments[0]); this.Write(", 0, 1)"); return(m); } } if (m.Method.Name == "ToString") { if (m.Object.Type != typeof(string)) { this.Write("CONVERT(NVARCHAR, "); this.Visit(m.Object); this.Write(")"); } else { this.Visit(m.Object); } return(m); } else if (!m.Method.IsStatic && m.Method.Name == "CompareTo" && m.Method.ReturnType == typeof(int) && m.Arguments.Count == 1) { this.Write("(CASE WHEN "); this.Visit(m.Object); this.Write(" = "); this.Visit(m.Arguments[0]); this.Write(" THEN 0 WHEN "); this.Visit(m.Object); this.Write(" < "); this.Visit(m.Arguments[0]); this.Write(" THEN -1 ELSE 1 END)"); return(m); } else if (m.Method.IsStatic && m.Method.Name == "Compare" && m.Method.ReturnType == typeof(int) && m.Arguments.Count == 2) { this.Write("(CASE WHEN "); this.Visit(m.Arguments[0]); this.Write(" = "); this.Visit(m.Arguments[1]); this.Write(" THEN 0 WHEN "); this.Visit(m.Arguments[0]); this.Write(" < "); this.Visit(m.Arguments[1]); this.Write(" THEN -1 ELSE 1 END)"); return(m); } return(base.VisitMethodCall(m)); }