public static WherePart Concat(WherePart left, string @operator, WherePart right)
{
return(new WherePart()
{
Parameters = left.Parameters.Union(right.Parameters).ToDictionary(kvp => kvp.Key, kvp => kvp.Value),
Sql = $"({left.Sql} {@operator} {right.Sql})"
});
}
public static WherePart Concat(string @operator, WherePart operand)
{
return(new WherePart()
{
Parameters = operand.Parameters,
Sql = $"({@operator} {operand.Sql})"
});
}
public virtual IQueryable <TEntity> Query(Expression <Func <TEntity, bool> > predict)
{
WhereBuilder whereBuilder = new WhereBuilder();
WherePart wherePart = whereBuilder.ToSql(predict);
string whereQuery = wherePart.Sql;
string query = $"SELECT * FROM {_tableName} WHERE {whereQuery}";
return(_connection.Query <TEntity>(query, wherePart.Parameters).AsQueryable());
}
/// <summary>
/// Cria o parser da clausula WHERE.
/// </summary>
/// <param name="wp"></param>
/// <param name="quoteExpressionBegin"></param>
/// <param name="quoteExpressionEnd"></param>
internal ParserToSqlCommand(WherePart wp, string quoteExpressionBegin, string quoteExpressionEnd)
{
_quoteExpressionBegin = quoteExpressionBegin;
_quoteExpressionEnd = quoteExpressionEnd;
foreach (SqlExpression exp in wp.Expressions)
{
ColumnName(exp);
}
}
protected void Where_ProjectKey(string value)
{
if (!string.IsNullOrEmpty(value))
{
WherePart.Add("r.projectKey = $projectkey");
AddParameter("$projectkey", value);
}
}
protected void Where_Subkey(string value)
{
if (!string.IsNullOrEmpty(value))
{
WherePart.Add("r.subKey = $subkey");
AddParameter("$subkey", value);
}
}
protected void Where_Level(string value)
{
if (!string.IsNullOrEmpty(value))
{
WherePart.Add("r.tag = $level");
AddParameter("$level", value);
}
}
protected void Where_Keyword(string value)
{
if (!string.IsNullOrEmpty(value))
{
WherePart.Add("r.exceptionMessage like $keyword");
AddParameter("$keyword", "%" + value + "%");
}
}
/// <summary>
/// Recupera as informações contidas dentro da clausula WHERE.
/// </summary>
/// <param name="wp"></param>
internal SelectStatement(ISelectStatementReferences references, WherePart wp)
{
if (references == null)
{
throw new ArgumentNullException("references");
}
_references = references;
foreach (SqlExpression se in wp.Expressions)
{
ColumnName(se);
}
_columnsInfoList = new List <ColumnInfo>(_columnsInfo.Values);
}
public string ToSql(SqlGenerator generator)
{
var sb = new StringBuilder();
sb.Append("Select ");
if (SelectPart.Any())
{
sb = SelectPart.Aggregate(sb, (s, i) => s.AppendFormat("{0},", i.ToSql(generator)));
sb.Remove(sb.Length - 1, 1);
}
else
{
sb.Append("*");
}
sb.Append(" From ");
sb = FromPart.Aggregate(sb, (s, i) => s.AppendFormat("{0} ", i.ToSql(generator)));
if (WherePart != null)
{
sb.AppendFormat("Where {0}", WherePart.ToSql(generator));
}
if (GroupPart.Any())
{
sb.Append(" Group By ");
sb = GroupPart.Aggregate(sb, (s, o) => s.AppendFormat("{0},", o.ToSql(generator, false)));
sb.Remove(sb.Length - 1, 1);
}
if (OrderPart.Any())
{
sb.Append(" Order By ");
sb = OrderPart.Aggregate(sb, (s, o) => s.AppendFormat("{0},", o.ToSql(generator)));
sb.Remove(sb.Length - 1, 1);
}
if (Take.HasValue)
{
sb.Append(" limit ");
if (Skip.HasValue)
{
sb.AppendFormat("{0},", Skip.Value);
}
sb.Append(Take.Value);
}
return(sb.ToString());
}
protected void Where_CreateTime(string value)
{
if (!string.IsNullOrEmpty(value))
{
string[] dates = value.Split(',');
if (!string.IsNullOrEmpty(dates[0]) && !string.IsNullOrEmpty(dates[1]))
{
WherePart.Add("r.createTime > $date1 and r.createTime < $date2");
AddParameter("$date1", dates[0].Trim().Replace(" ", "T"));
AddParameter("$date2", dates[1].Trim().Replace(" ", "T"));
}
}
}
internal ParserToSqlCommand(WherePart wp) : this(wp, "", "")
{
}
private WherePart Recurse(ref int i, Expression expression, bool isUnary = false, string prefix = null, string postfix = null)
{
if (expression is UnaryExpression)
{
var unary = (UnaryExpression)expression;
return(WherePart.Concat(NodeTypeToString(unary.NodeType), Recurse(ref i, unary.Operand, true)));
}
if (expression is BinaryExpression)
{
var body = (BinaryExpression)expression;
return(WherePart.Concat(Recurse(ref i, body.Left), NodeTypeToString(body.NodeType), Recurse(ref i, body.Right)));
}
if (expression is ConstantExpression)
{
var constant = (ConstantExpression)expression;
var value = constant.Value;
if (value is bool && isUnary)
{
return(WherePart.Concat(WherePart.IsParameter(i++, value), "=", WherePart.IsSql("1")));
}
if (value is string)
{
if (prefix == null && postfix == null)
{
value = (string)value;
}
else
{
value = prefix + (string)value + postfix;
}
}
return(WherePart.IsParameter(i++, value));
}
if (expression is MemberExpression)
{
var member = (MemberExpression)expression;
if (member.Member is PropertyInfo)
{
var property = (PropertyInfo)member.Member;
var colName = property.Name;
if (member.Expression is MemberExpression)
{
member = (MemberExpression)member.Expression;
if (member.Member is FieldInfo)
{
var value = GetValue(member);
return(WherePart.IsParameter(i++, property.GetValue(value)));
}
}
if (member.Expression is ConstantExpression)
{
var constant = (ConstantExpression)member.Expression;
var value = constant.Value;
return(WherePart.IsParameter(i++, property.GetValue(value)));
}
if (isUnary && member.Type == typeof(bool))
{
return(WherePart.Concat(Recurse(ref i, expression), "=", WherePart.IsParameter(i++, true)));
}
return(WherePart.IsSql("[" + colName + "]"));
}
if (member.Member is FieldInfo)
{
var value = GetValue(member);
if (value is string)
{
value = prefix + (string)value + postfix;
}
return(WherePart.IsParameter(i++, value));
}
throw new Exception($"Expression does not refer to a property or field: {expression}");
}
if (expression is MethodCallExpression)
{
var methodCall = (MethodCallExpression)expression;
// LIKE queries:
if (methodCall.Method == typeof(string).GetMethod("Contains", new[] { typeof(string) }))
{
return(WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], prefix: "%", postfix: "%")));
}
if (methodCall.Method == typeof(string).GetMethod("StartsWith", new[] { typeof(string) }))
{
return(WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], prefix: "", postfix: "%")));
}
if (methodCall.Method == typeof(string).GetMethod("EndsWith", new[] { typeof(string) }))
{
return(WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], prefix: "%", postfix: "")));
}
// IN queries:
if (methodCall.Method.Name == "Contains")
{
Expression collection;
Expression property;
if (methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 2)
{
collection = methodCall.Arguments[0];
property = methodCall.Arguments[1];
}
else if (!methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 1)
{
collection = methodCall.Object;
property = methodCall.Arguments[0];
}
else
{
throw new Exception("Unsupported method call: " + methodCall.Method.Name);
}
var values = (IEnumerable)GetValue(collection);
return(WherePart.Concat(Recurse(ref i, property), "IN", WherePart.IsCollection(ref i, values)));
}
throw new Exception("Unsupported method call: " + methodCall.Method.Name);
}
throw new Exception("Unsupported expression: " + expression.GetType().Name);
}
protected void Where_Type(string value)
{
WherePart.Add("r.type = $type");
AddParameter("$type", value);
}
public virtual SelectQuery Where(Expression.Expression condition)
{
wherePart = new WherePart(condition);
return(this);
}
/// <summary>
/// Creates a new <c>LikeClause</c> instance
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
public LikeClause(WherePart left, string right)
{
_left = left;
_right = new StringConstant(right + "%");
}
private IWherePart Recurse(ref int i, Expression expression, bool isUnary = false, string prefix = null, string postfix = null)
{
switch (expression)
{
case UnaryExpression unaryExpression:
var unary = unaryExpression;
return(WherePart.Concat(NodeTypeToString(unary.NodeType), Recurse(ref i, unary.Operand, true)));
case BinaryExpression binaryExpression:
var body = binaryExpression;
return(WherePart.Concat(Recurse(ref i, body.Left), NodeTypeToString(body.NodeType), Recurse(ref i, body.Right)));
case ConstantExpression constantExpression:
var constant = constantExpression;
var value = constant.Value;
if (value is int)
{
return(WherePart.IsSql(value.ToString()));
}
if (value is string)
{
value = prefix + (string)value + postfix;
}
if (value is bool && isUnary)
{
return(WherePart.Concat(WherePart.IsParameter(i++, value), "=", WherePart.IsSql("1")));
}
i++;
var constantParametrizedName = Fragments.ParametrizedValue.Format(i);
var constantResponse = WherePart.IsSql(Fragments.InlineValue.Format(constantParametrizedName));
constantResponse.Parameters.Add(constantParametrizedName, value);
return(constantResponse);
// return WherePart.IsParameter(i++, value);
case MemberExpression memberExpression:
var member = memberExpression;
var parametrizedName = "";
IWherePart response = null;
var valueSet = GetMemberValueSet(member);
switch (member.Member)
{
case PropertyInfo info:
var property = info;
var colName = _modelDescriptor.Members[property.Name].TargetName;
if (member.Type == typeof(bool))
{
parametrizedName = Fragments.ParametrizedValue.Format(colName);
response = WherePart.IsSql($"{colName} {Fragments.Markers.Equality} {Fragments.InlineValue.Format(parametrizedName)}");
response.Parameters.Add(parametrizedName, Fragments.Values.True);
}
else if (member.Expression is ConstantExpression)
{
parametrizedName = Fragments.ParametrizedValue.Format(colName);
response = WherePart.IsSql(Fragments.InlineValue.Format(parametrizedName));
response.Parameters.Add(parametrizedName, GetValue(member));
}
else
{
if (valueSet.HasValue)
{
response = WherePart.IsSql(Fragments.Column.Format(colName));
}
else
{
response = WherePart.IsSql(Fragments.Column.Format(colName));
}
}
break;
case FieldInfo _:
if (valueSet.HasValue)
{
i++;
parametrizedName = Fragments.ParametrizedValue.Format(i);
response = WherePart.IsSql(Fragments.InlineValue.Format(parametrizedName));
response.Parameters.Add(parametrizedName, valueSet.Value);
}
else
{
response = WherePart.IsSql(Fragments.Column.Format(valueSet.Member));
}
break;
default: throw new Exception($"Expression does not refer to a property or field: {expression}");
}
return(response);
case MethodCallExpression callExpression:
var methodCall = callExpression;
// LIKE queries:
if (methodCall.Method == typeof(string).GetMethod("Contains", new[] { typeof(string) }))
{
return(WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], prefix: "%", postfix: "%")));
}
if (methodCall.Method == typeof(string).GetMethod("StartsWith", new[] { typeof(string) }))
{
return(WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], postfix: "%")));
}
if (methodCall.Method == typeof(string).GetMethod("EndsWith", new[] { typeof(string) }))
{
return(WherePart.Concat(Recurse(ref i, methodCall.Object), "LIKE", Recurse(ref i, methodCall.Arguments[0], prefix: "%")));
}
// IN queries:
if (methodCall.Method.Name == "Contains")
{
Expression collection;
Expression property;
if (methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 2)
{
collection = methodCall.Arguments[0];
property = methodCall.Arguments[1];
}
else if (!methodCall.Method.IsDefined(typeof(ExtensionAttribute)) && methodCall.Arguments.Count == 1)
{
collection = methodCall.Object;
property = methodCall.Arguments[0];
}
else
{
throw new Exception("Unsupported method call: " + methodCall.Method.Name);
}
var values = (IEnumerable)GetValue(collection);
return(WherePart.Concat(Recurse(ref i, property), "IN", WherePart.IsCollection(ref i, values)));
}
throw new Exception("Unsupported method call: " + methodCall.Method.Name);
}
throw new Exception("Unsupported expression: " + expression.GetType().Name);
}
/// <summary>
/// Recupera as informações contidas dentro da clausula WHERE.
/// </summary>
/// <param name="wp"></param>
internal SelectStatement(WherePart wp) : this(NativeSelectStatementReferences.Instance, wp)
{
}
public string WherePart(WherePart part)
{
return $"{string.Join(".", part.Column.QualifiedName)} {part.Operator} {part.Parameter.Name}";
}
/// <summary>
/// Creates a new <c>EqualsClause</c> instance
/// </summary>
public EqualsClause(WherePart left, WherePart right)
{
_left = left;
_right = right;
}
