private void AddConstraint(string columnName, Comparison comp, object value)
{
Constraint c = new Constraint(ConstraintType.Where, columnName);
if(result.Count > 1)
c = new Constraint(ConstraintType.And, columnName);
//c.ParameterName = columnName;
if(comp == Comparison.StartsWith)
{
value = string.Format("{0}%", value);
comp = Comparison.Like;
}
else if(comp == Comparison.EndsWith)
{
value = string.Format("%{0}", value);
comp = Comparison.Like;
}
c.Comparison = comp;
c.ParameterValue = value;
result.Add(c);
}
///<summary>
/// Builds a SubSonic DELETE query from the passed-in object
///</summary>
public static ISqlQuery ToDeleteQuery <T>(this T item, IDataProvider provider) where T : class, new()
{
Type type = typeof(T);
ITable tbl = provider.FindOrCreateTable <T>();
var query = new Delete <T>(tbl, provider);
if (tbl != null)
{
IColumn pk = tbl.PrimaryKey;
var settings = item.ToDictionary();
if (pk != null)
{
var c = new Constraint(ConstraintType.Where, pk.Name)
{
ParameterValue = settings[pk.Name],
ParameterName = pk.Name,
ConstructionFragment = pk.Name
};
query.Constraints.Add(c);
}
else
{
query.Constraints = item.ToConstraintList();
}
}
return(query);
}
public IList<Constraint> GetConstraints(Expression ex)
{
constraints = new List<Constraint>();
current = new Constraint();
query = new SqlQuery();
var expression = Visit(ex);
return query.Constraints;
}
public SqlQuery Delete <T>(Expression <Func <T, bool> > column) where T : new()
{
LambdaExpression lamda = column;
SqlQuery result = new Delete <T>(this.Provider);
result = result.From <T>();
SubSonic.Query.Constraint c = lamda.ParseConstraint();
result.Constraints.Add(c);
return(result);
}
///<summary>
/// Builds a SubSonic UPDATE query from the passed-in object
///</summary>
public static ISqlQuery ToUpdateQuery <T>(this T item, IDataProvider provider) where T : class, new()
{
Type type = typeof(T);
var settings = item.ToDictionary();
ITable tbl = provider.FindOrCreateTable <T>();
Update <T> query = new Update <T>(tbl.Provider);
if (item is IActiveRecord)
{
var ar = item as IActiveRecord;
foreach (var dirty in ar.GetDirtyColumns())
{
if (!dirty.IsPrimaryKey && !dirty.IsReadOnly)
{
query.Set(dirty.Name).EqualTo(settings[dirty.Name]);
}
}
}
else
{
foreach (string key in settings.Keys)
{
IColumn col = tbl.GetColumnByPropertyName(key);
if (col != null)
{
if (!col.IsPrimaryKey && !col.IsReadOnly)
{
query.Set(col).EqualTo(settings[key]);
}
}
}
}
//add the PK constraint
Constraint c = new Constraint(ConstraintType.Where, tbl.PrimaryKey.Name)
{
ParameterValue = settings[tbl.PrimaryKey.Name],
ParameterName = tbl.PrimaryKey.Name,
ConstructionFragment = tbl.PrimaryKey.Name
};
query.Constraints.Add(c);
return(query);
}
protected void SetConstraintWildcards(Constraint constraint)
{
if (constraint.ParameterValue is string)
{
switch (constraint.Comparison)
{
case Comparison.StartsWith:
constraint.ParameterValue = constraint.ParameterValue + "%";
break;
case Comparison.EndsWith:
constraint.ParameterValue = "%" + constraint.ParameterValue;
break;
case Comparison.Like:
constraint.ParameterValue = "%" + constraint.ParameterValue + "%";
break;
}
}
}
protected override Expression VisitBinary(BinaryExpression b)
{
b = ConvertVbCompareString(b);
current = new Constraint();
if(b.NodeType == ExpressionType.AndAlso)
{
if(query.Constraints.Count > 0)
current.Condition = ConstraintType.And;
}
else if(b.NodeType == ExpressionType.OrElse || b.NodeType == ExpressionType.Or)
{
if(query.Constraints.Count > 0)
current.Condition = ConstraintType.Or;
}
else if(b.NodeType == ExpressionType.AndAlso) {}
else if(b.NodeType == ExpressionType.Or || b.NodeType == ExpressionType.OrElse)
{
if(query.Constraints.Count > 0)
current.Condition = ConstraintType.Or;
}
else if(b.NodeType == ExpressionType.NotEqual)
current.Comparison = Comparison.NotEquals;
else if(b.NodeType == ExpressionType.Equal)
current.Comparison = Comparison.Equals;
else if(b.NodeType == ExpressionType.GreaterThan)
current.Comparison = Comparison.GreaterThan;
else if(b.NodeType == ExpressionType.GreaterThanOrEqual)
current.Comparison = Comparison.GreaterOrEquals;
else if(b.NodeType == ExpressionType.LessThan)
current.Comparison = Comparison.LessThan;
else if(b.NodeType == ExpressionType.Not)
Visit(b);
else if(b.NodeType == ExpressionType.LessThanOrEqual)
current.Comparison = Comparison.LessOrEquals;
isLeft = true;
Expression left = Visit(b.Left);
isLeft = false;
Expression right = Visit(b.Right);
Expression conversion = Visit(b.Conversion);
if(b.NodeType == ExpressionType.AndAlso)
{
if(query.Constraints.Count > 0)
current.Condition = ConstraintType.And;
}
else if(b.NodeType == ExpressionType.OrElse || b.NodeType == ExpressionType.Or)
{
if(query.Constraints.Count > 0)
current.Condition = ConstraintType.Or;
}
//query.OpenExpression();
AddConstraint();
//query.CloseExpression();
//if (expressionOpen) {
// query.CloseExpression();
// expressionOpen = false;
//}
if (left != b.Left || right != b.Right || conversion != b.Conversion)
{
if(b.NodeType == ExpressionType.Coalesce && b.Conversion != null)
return Expression.Coalesce(left, right, conversion as LambdaExpression);
return Expression.MakeBinary(b.NodeType, left, right, b.IsLiftedToNull, b.Method);
}
return b;
}
/// <summary>
/// Generates the constraints.
/// </summary>
/// <returns></returns>
public virtual string GenerateConstraints()
{
string whereOperator = this.sqlFragment.WHERE;
if (query.Aggregates.Count > 0 && query.Aggregates.Any(x => x.AggregateType == AggregateFunction.GroupBy))
{
whereOperator = this.sqlFragment.HAVING;
}
StringBuilder sb = new StringBuilder();
sb.AppendLine();
bool isFirst = true;
//int paramCount;
bool expressionIsOpen = false;
int indexer = 0;
foreach (Constraint c in query.Constraints)
{
string columnName = String.Empty;
bool foundColumn = false;
if (c.ConstructionFragment == c.ColumnName && c.ConstructionFragment != "##")
{
IColumn col = FindColumn(c.ColumnName);
if (col != null)
{
columnName = col.QualifiedName;
c.ParameterName = string.Format("{0}{1}", GetParameterPrefix(), indexer);
c.DbType = col.DataType;
foundColumn = true;
}
}
if (!foundColumn && c.ConstructionFragment != "##")
{
bool isAggregate = false;
//this could be an expression
//string rawColumnName = c.ConstructionFragment;
if (c.ConstructionFragment.StartsWith("("))
{
//rawColumnName = c.ConstructionFragment.Replace("(", String.Empty);
expressionIsOpen = true;
}
//this could be an aggregate function
else if (c.IsAggregate ||
(c.ConstructionFragment.Contains("(") && c.ConstructionFragment.Contains(")")))
{
//rawColumnName = c.ConstructionFragment.Replace("(", String.Empty).Replace(")", String.Empty);
isAggregate = true;
}
IColumn col = FindColumn(c.ColumnName);
if (!isAggregate && col != null)
{
columnName = c.ConstructionFragment.FastReplace(col.Name, col.QualifiedName);
c.ParameterName = String.Concat(col.ParameterName, indexer.ToString());
c.DbType = col.DataType;
}
else
{
c.ParameterName = query.FromTables[0].Provider.ParameterPrefix + indexer;
columnName = c.ConstructionFragment;
}
}
//paramCount++;
if (!isFirst)
{
whereOperator = Enum.GetName(typeof(ConstraintType), c.Condition);
whereOperator = String.Concat(" ", whereOperator.ToUpper(), " ");
}
if (c.Comparison != Comparison.OpenParentheses && c.Comparison != Comparison.CloseParentheses)
{
sb.Append(whereOperator);
}
if (c.Comparison == Comparison.BetweenAnd)
{
sb.Append(columnName);
sb.Append(this.sqlFragment.BETWEEN);
sb.Append(c.ParameterName + "_start");
sb.Append(this.sqlFragment.AND);
sb.Append(c.ParameterName + "_end");
}
else if (c.Comparison == Comparison.In || c.Comparison == Comparison.NotIn)
{
sb.Append(columnName);
if (c.Comparison == Comparison.In)
{
sb.Append(this.sqlFragment.IN);
}
else
{
sb.Append(this.sqlFragment.NOT_IN);
}
sb.Append("(");
if (c.InSelect != null)
{
//create a sql statement from the passed-in select
string sql = c.InSelect.BuildSqlStatement();
sb.Append(sql);
}
else
{
//enumerate INs
IEnumerator en = c.InValues.GetEnumerator();
StringBuilder sbIn = new StringBuilder();
bool first = true;
int i = 1;
while (en.MoveNext())
{
if (!first)
{
sbIn.Append(",");
}
else
{
first = false;
}
sbIn.Append(String.Concat(c.ParameterName, "In", i));
i++;
}
string inList = sbIn.ToString();
//inList = Sugar.Strings.Chop(inList);
sb.Append(inList);
}
sb.Append(")");
}
else if (c.Comparison == Comparison.OpenParentheses)
{
expressionIsOpen = true;
sb.Append("(");
}
else if (c.Comparison == Comparison.CloseParentheses)
{
expressionIsOpen = false;
sb.Append(")");
}
else
{
if (columnName.StartsWith("("))
{
expressionIsOpen = true;
}
if (c.ConstructionFragment != "##")
{
sb.Append(columnName);
sb.Append(Constraint.GetComparisonOperator(c.Comparison));
if (c.Comparison == Comparison.Is || c.Comparison == Comparison.IsNot)
{
if (c.ParameterValue == null || c.ParameterValue == DBNull.Value)
{
sb.Append("NULL");
}
}
else
{
sb.Append(c.ParameterName);
}
}
}
indexer++;
isFirst = false;
}
string result = sb.ToString();
//a little help...
if (expressionIsOpen & !result.EndsWith(")"))
{
result = String.Concat(result, ")");
}
return(result);
}
private Constraint BuildAlwaysFalseConstraint()
{
var falseConstraint = new Constraint();
falseConstraint.ConstructionFragment = "1";
falseConstraint.ParameterValue = 0;
falseConstraint.ColumnName = String.Empty;
falseConstraint.Comparison = Comparison.Equals;
return falseConstraint;
}
private void BuildCollectionConstraint(MethodCallExpression methodCallExpression)
{
if (methodCallExpression.Arguments.Count == 2)
{
isLeft = true;
Visit(methodCallExpression.Arguments[1]);
isLeft = false;
var c = Visit(methodCallExpression.Arguments[0]) as ConstantExpression;
if (c != null)
{
// Constants
current.InValues = c.Value as IEnumerable;
}
else
{
// something parsed to parameter values
current.InValues = current.ParameterValue as IEnumerable;
}
current.Comparison = isNot ? Comparison.NotIn : Comparison.In;
if (current.InValues == null || !current.InValues.GetEnumerator().MoveNext())
{
current = BuildAlwaysFalseConstraint();
}
}
}
/// <summary>
/// Converts the string method calls Contains,EndsWith and StartsWith into queries
/// </summary>
/// <param name="methodCallExpression">The MethodCall we are attempting to map to a query.</param>
/// <returns>an expression tree.</returns>
protected override Expression VisitMethodCall(MethodCallExpression methodCallExpression)
{
// TODO: Here we only support member expressions -> Extend to solve http://github.com/subsonic/SubSonic-3.0/issues#issue/59
Expression result = methodCallExpression;
var obj = methodCallExpression.Object as MemberExpression;
if (obj != null)
{
var constraint = new Constraint();
switch (methodCallExpression.Method.Name)
{
case "Contains":
constraint.Comparison = Comparison.Like;
break;
case "EndsWith":
constraint.Comparison = Comparison.EndsWith;
break;
case "StartsWith":
constraint.Comparison = Comparison.StartsWith;
break;
default:
return base.VisitMethodCall(methodCallExpression);
}
// Set the starting / ending wildcards on the parameter value... not the best place to do this, but I'm
// attempting to constrain the scope of the change.
constraint.ConstructionFragment = obj.Member.Name;
// Set the current constraint... Visit will be using it, I don't know what it would do with multiple args....
current = constraint;
foreach (var arg in methodCallExpression.Arguments)
{
isLeft = false;
Visit(arg);
}
isLeft = true;
// After Visit, the current constraint will have some parameters, so set the wildcards on the parameter.
SetConstraintWildcards(constraint);
}
else
{
switch (methodCallExpression.Method.Name)
{
case "Contains":
case "Any":
BuildCollectionConstraint(methodCallExpression);
break;
default:
throw new InvalidOperationException(
String.Format("Method {0} is not supported in linq statement!",
methodCallExpression.Method.Name));
}
}
AddConstraint();
return methodCallExpression;
}
/// Converts the string method calls Contains,EndsWith and StartsWith into queries
/// </summary>
/// <param name="m">The MethodCall we are attempting to map to a query.</param>
/// <returns>an expression tree.</returns>
protected override Expression VisitMethodCall(MethodCallExpression methodCallExpression)
{
Expression result = methodCallExpression;
var obj = methodCallExpression.Object as MemberExpression;
if (obj != null)
{
var constraint = new Constraint();
switch (methodCallExpression.Method.Name)
{
case "Contains":
constraint.Comparison = Comparison.Like;
break;
case "EndsWith":
constraint.Comparison = Comparison.EndsWith;
break;
case "StartsWith":
constraint.Comparison = Comparison.StartsWith;
break;
default:
return base.VisitMethodCall(methodCallExpression);
}
// Set the starting / ending wildcards on the parameter value... not the best place to do this, but I'm
// attempting to constrain the scope of the change.
constraint.ConstructionFragment = obj.Member.Name;
// Set the current constraint... Visit will be using it, I don't know what it would do with multiple args....
current = constraint;
foreach (var arg in methodCallExpression.Arguments)
{
isLeft = false;
Visit(arg);
}
isLeft = true;
// After Visit, the current constraint will have some parameters, so set the wildcards on the parameter.
SetConstraintWildcards(constraint);
}
AddConstraint();
return methodCallExpression;
}
/// <summary>
/// 生成SqlQuery类调用时所需要的条件格式——目前查询条件中不能使用括号，如果需要须修改代码
/// </summary>
/// <param name="scd">SqlQuery条件类</param>
public static List<Constraint> Condition(List<SqlqueryCondition> scd)
{
if (scd == null)
return null;
var constrain = new List<Constraint>();
foreach (SqlqueryCondition item in scd) {
if (item != null) {
var con = new Constraint(item.SQConstraintType, item.SQColumnName);
con.Comparison = item.SQComparison;
con.ParameterValue = item.SQValue;
constrain.Add(con);
}
}
return constrain;
}
