/// <summary> /// 创建时间查询的SQL片段 /// </summary> /// <param name="FieldName">查询字段名称</param> /// <param name="Op">查询操作符</param> /// <param name="dt">日期时间</param> /// <param name="IgnoreTime">是否忽略时间</param> /// <returns></returns> public virtual string CreateDateTimeSQLSegment(string FieldName, QueryOperatorEnum Op, DateTime dt, bool IgnoreTime) { if (String.IsNullOrEmpty(FieldName)) { throw new ArgumentNullException("FieldName is empty"); } if (Op == QueryOperatorEnum.In || Op == QueryOperatorEnum.Inline || Op == QueryOperatorEnum.Like) { throw new ArgumentException("日期查询不支持当前操作符:" + QueryOperator.ConvertToOperator(Op)); } if (!FieldName.EndsWith(" "))//添加一个空格 { FieldName += " "; } //按照时间精确查询 if (!IgnoreTime) { return(FieldName + QueryOperator.ConvertToOperator(Op) + ConvertDateTimeToSQL(dt)); } //忽略时间,按照日期查询 string Date = dt.ToString("yyyy-MM-dd"); DateTime DtBegin = Convert.ToDateTime(Date + " 00:00:00"); DateTime DtEnd = Convert.ToDateTime(Date + " 23:59:59"); if (Op == QueryOperatorEnum.Equal) //在这一天之内 { return("(" + FieldName + ">=" + ConvertDateTimeToSQL(DtBegin) + " AND " + FieldName + "<=" + ConvertDateTimeToSQL(DtEnd) + ")"); } if (Op == QueryOperatorEnum.Great) //比这一天大 { return(FieldName + ">" + ConvertDateTimeToSQL(DtEnd)); } if (Op == QueryOperatorEnum.Less) //比这一天小 { return(FieldName + "<" + ConvertDateTimeToSQL(DtBegin)); } if (Op == QueryOperatorEnum.GreatAndEqual)//比这一天大,包括这一天 { return(FieldName + ">=" + ConvertDateTimeToSQL(DtBegin)); } if (Op == QueryOperatorEnum.LessAndEqual)//比这一天小,包括这一天 { return(FieldName + "<=" + ConvertDateTimeToSQL(DtEnd)); } if (Op == QueryOperatorEnum.NotEqual)//比这一天小或者比这一天大 { return("(" + FieldName + "<" + ConvertDateTimeToSQL(DtBegin) + " OR " + FieldName + ">" + ConvertDateTimeToSQL(DtEnd) + ")"); } else { throw new ArgumentException("日期查询不支持当前操作符:" + QueryOperator.ConvertToOperator(Op)); } }
/// <summary> /// /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="propertyName">实体字段名称</param> /// <param name="propertyValue">实体值</param> /// <param name="parameter">参数p实体前方形参如p.name </param> /// <param name="expressionType">操作类型</param> /// <returns></returns> public static Expression CreateConditionExpression( this string propertyName, object propertyValue, ParameterExpression parameter, QueryOperatorEnum expressionType) { if (parameter == null) { throw new NotImplementedException("请传入参数"); } Expression key = Expression.Property(parameter, propertyName); Expression value = Expression.Constant(propertyValue); Expression expression; switch (expressionType) { case QueryOperatorEnum.Contains: expression = Expression.Call(key, typeof(string).GetMethod("Contains", new Type[] { typeof(string) }), value); break; case QueryOperatorEnum.Equal: expression = Expression.Equal(key, Expression.Convert(value, key.Type)); break; case QueryOperatorEnum.GreaterThan: expression = Expression.GreaterThan(key, Expression.Convert(value, key.Type)); break; case QueryOperatorEnum.GreaterThanOrEqual: expression = Expression.GreaterThanOrEqual(key, Expression.Convert(value, key.Type)); break; case QueryOperatorEnum.LessThan: expression = Expression.LessThan(key, Expression.Convert(value, key.Type)); break; case QueryOperatorEnum.LessThanOrEqual: expression = Expression.LessThanOrEqual(key, Expression.Convert(value, key.Type)); break; case QueryOperatorEnum.NotEqual: expression = Expression.NotEqual(key, Expression.Convert(value, key.Type)); break; //case QueryOperatorEnum.In: // return ParaseIn(p, condition); //case QueryOperatorEnum.Between: // return ParaseBetween(p, condition); default: throw new NotImplementedException("不支持此操作"); } return(expression); }
/// <summary> /// 转换为操作符 /// </summary> /// <param name="OperatorEnum"></param> /// <returns></returns> public static string ConvertToOperator(QueryOperatorEnum OperatorEnum) { switch (OperatorEnum) { case QueryOperatorEnum.Equal: return(QueryOperator.Equal); case QueryOperatorEnum.Great: return(QueryOperator.Great); case QueryOperatorEnum.GreatAndEqual: return(QueryOperator.GreatAndEqual); case QueryOperatorEnum.In: return(QueryOperator.In); case QueryOperatorEnum.Inline: return(QueryOperator.Inline); case QueryOperatorEnum.Less: return(QueryOperator.Less); case QueryOperatorEnum.LessAndEqual: return(QueryOperator.LessAndEqual); case QueryOperatorEnum.Like: return(QueryOperator.Like); case QueryOperatorEnum.NotEqual: return(QueryOperator.NotEqual); } return("unknown Operator"); }