/// <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");
}
