/// <summary>
/// 增加查询项
/// </summary>
/// <param name="fieldName"></param>
/// <param name="conjuction"></param>
/// <param name="operand"></param>
/// <param name="value"></param>
public virtual void AddQueryItem(string fieldName, Conjuction conjuction, Operand operand, object value, DbType dbType)
{
QueryParameter p = new QueryParameter();
p.FieldName = fieldName;
p.ParameterValue = value;
p.ParameterDbType = dbType;
p.Op = operand;
p.Conj = conjuction;
string whereClause = "";
switch (p.Op)
{
case Operand.Equal:
p.ParameterName = GetParameterName(fieldName);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + " = " + p.ParameterName;
break;
case Operand.NotEqual:
p.ParameterName = GetParameterName(fieldName);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + "<>" + p.ParameterName;
break;
case Operand.GreaterThan:
p.ParameterName = GetParameterName(fieldName);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + ">" + p.ParameterName;
break;
case Operand.LessThan:
p.ParameterName = GetParameterName(fieldName);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + "<" + p.ParameterName;
break;
case Operand.LessThanOrEqual:
p.ParameterName = GetParameterName(fieldName);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + "<=" + p.ParameterName;
break;
case Operand.GreaterThanOrEqual:
p.ParameterName = GetParameterName(fieldName);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + ">=" + p.ParameterName;
break;
case Operand.Like:
p.ParameterName = GetParameterName(fieldName);
p.ParameterValue = string.Format("%{0}%", p.ParameterValue);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + " like " + p.ParameterName;
break;
case Operand.RightLike:
p.ParameterName = GetParameterName(fieldName);
p.ParameterValue = string.Format("%{0}", p.ParameterValue);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + " like " + p.ParameterName;
break;
case Operand.LeftLike:
p.ParameterName = GetParameterName(fieldName);
p.ParameterValue = string.Format("{0}%", p.ParameterValue);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + " like " + p.ParameterName;
break;
case Operand.NotLike:
p.ParameterName = GetParameterName(fieldName);
p.ParameterValue = string.Format("%{0}%", p.ParameterValue);
this.filterParameters.Add(p);
whereClause += " " + p.FieldName + " not like " + p.ParameterName;
break;
case Operand.Contains:
whereClause += " Contains ( " + p.FieldName + " , " + p.ParameterName + " )";
break;
case Operand.IsNull:
whereClause += " " + p.FieldName + " is null";
break;
case Operand.IsNotNull:
whereClause += " " + p.FieldName + " is not null";
break;
case Operand.In: //in 需要参数格式 @p1, @p2, @p3,...
p.ParameterName = AddInParam(fieldName, value, dbType);
if (!string.IsNullOrEmpty(p.ParameterName))
{ //判断参数有效 此条件有效
whereClause += " " + p.FieldName + " in (" + p.ParameterName + ") ";
}
else
{ //如果 in条件为空. 返回空列表
whereClause += " 1 = 0";
}
break;
case Operand.NotIn:
p.ParameterName = AddInParam(fieldName, value, dbType);
if (!string.IsNullOrEmpty(p.ParameterName))
{ //判断参数有效 此条件有效
whereClause += " " + p.FieldName + " not in (" + p.ParameterName + ") ";
}
else
{ //如参数为空,忽略此参数 返回所有
}
break;
case Operand.FieldEqual:
whereClause += " " + p.FieldName + " = " + p.ParameterValue.ToString();
break;
case Operand.FieldGreaterThan:
whereClause += " " + p.FieldName + " > " + p.ParameterValue.ToString();
break;
case Operand.FieldLessThan:
whereClause += " " + p.FieldName + " < " + p.ParameterValue.ToString();
break;
case Operand.FieldGreaterThanOrEqual:
whereClause += " " + p.FieldName + " >= " + p.ParameterValue.ToString();
break;
case Operand.FieldLessThanOrEqual:
whereClause += " " + p.FieldName + " <= " + p.ParameterValue.ToString();
break;
}
if (!lastIsConjuction)
{
string conjuctionStr = "";
if (p.Conj == Conjuction.And)
{
conjuctionStr = " and";
}
else if (p.Conj == Conjuction.Or)
{
conjuctionStr = " or";
}
this.filterSQLString += conjuctionStr;
}
if (!string.IsNullOrEmpty(whereClause))
{
this.filterSQLString += whereClause;
this.ItemList.Add(p);
lastIsConjuction = false;
}
}
/// <summary>
/// 对静态方法或实例方法的调用
/// </summary>
/// <param name="exp"></param>
/// <param name="isNotOprand"></param>
/// <returns></returns>
private QueryParameter MethodCallExpressionProvider(Expression exp, bool isNotOprand = false)
{
QueryParameter qp = null;
object name = null;
object value = null;
DbType parameterDbType = DbType.Object;
Operand op = Operand.Equal;
MethodCallExpression mce = exp as MethodCallExpression;
switch (mce.Method.Name)
{
case "Equals":
name = MemberExpressionProvider(mce.Object);
value = Expression.Lambda(mce.Arguments[0]).Compile().DynamicInvoke().ToString();
op = isNotOprand ? Operand.NotEqual : Operand.Equal;
parameterDbType = DbTypeConvertor.GetDbType(value);
break;
case "Contains":
if (mce.Object != null)
{
if (mce.Object is MemberExpression && ((MemberExpression)mce.Object).Member.MemberType == MemberTypes.Property)
{ // task.Name.Contains("T1"); task.Name.Contains(keywords);
name = AtomExpressionRouter(mce.Object);
value = AtomExpressionRouter(mce.Arguments[0]);
op = isNotOprand ? Operand.NotLike : Operand.Like;
parameterDbType = DbTypeConvertor.GetDbType(value);
}
else
{ //permIds.Contains(a.Id)
name = AtomExpressionRouter(mce.Arguments[0]);
value = AtomExpressionRouter(mce.Object);
parameterDbType = DbTypeConvertor.TypeToDbType(mce.Arguments[0].Type);
op = isNotOprand ? Operand.NotIn : Operand.In;
}
}
else if (mce.Arguments.Count >= 2)
{ //int与int?类型 list:List<int?>
name = AtomExpressionRouter(mce.Arguments[1]);
value = AtomExpressionRouter(mce.Arguments[0]);
parameterDbType = DbTypeConvertor.TypeToDbType(mce.Arguments[1].Type);
op = isNotOprand ? Operand.NotIn : Operand.In;
}
else
{
throw new Exception($"不支持的方法:{mce.ToString()}");
}
break;
case "StartsWith":
name = MemberExpressionProvider(mce.Object);
//value = ConstantExpressionProvider(mce.Arguments[0]);
value = Expression.Lambda(mce.Arguments[0]).Compile().DynamicInvoke().ToString();
op = Operand.LeftLike;
parameterDbType = DbTypeConvertor.GetDbType(value);
break;
case "EndsWith":
name = MemberExpressionProvider(mce.Object);
//value = ConstantExpressionProvider(mce.Arguments[0]);
value = Expression.Lambda(mce.Arguments[0]).Compile().DynamicInvoke().ToString();
op = Operand.RightLike;
parameterDbType = DbTypeConvertor.GetDbType(value);
break;
default:
throw new Exception("不支持的方法:" + mce.Method.Name);
}
qp = new QueryParameter()
{
FieldName = name?.ToString(),
Op = op,
ParameterValue = value,
ParameterDbType = parameterDbType
};
return(qp);
}
/// <summary>
/// 增加查询项
/// </summary>
/// <param name="qp"></param>
/// <returns></returns>
public virtual void AddQueryItem(QueryParameter qp)
{
AddQueryItem(qp.FieldName, qp.Conj, qp.Op, qp.ParameterValue, qp.ParameterDbType);
}