protected virtual bool ExpressionToQuery(SqlDaQuery queryToAddTo, IExpression expr,
ClassMapping mapping, string colPrefix, string booleanOperator)
{
string col;
Type dbDataType;
bool trueOrNot = expr.TrueOrNot();
// add the operator if one was specified.
queryToAddTo.Sql.Append(booleanOperator);
// Add some parends.
queryToAddTo.Sql.Append("(");
if (expr is BetweenExpression)
{
BetweenExpression between = (BetweenExpression)expr;
col = colPrefix + mapping.AllDataColsByObjAttrs[between.Property];
dbDataType = mapping.DataColTypesByObjAttr[between.Property];
queryToAddTo.Sql.Append(col);
queryToAddTo.Sql.Append(trueOrNot ? " >= " : " < ");
AppendParameter(queryToAddTo, between.Min, dbDataType);
queryToAddTo.Sql.Append(trueOrNot ? " AND " : " OR ");
queryToAddTo.Sql.Append(col);
queryToAddTo.Sql.Append(trueOrNot ? " <= " : " > ");
AppendParameter(queryToAddTo, between.Max, dbDataType);
}
else if (expr is EqualExpression)
{
EqualExpression equal = (EqualExpression)expr;
col = colPrefix + mapping.AllDataColsByObjAttrs[equal.Property];
if (equal.Value == null)
{
queryToAddTo.Sql.Append(col);
queryToAddTo.Sql.Append(trueOrNot ? " IS NULL" : " IS NOT NULL");
}
else
{
queryToAddTo.Sql.Append(col);
queryToAddTo.Sql.Append(trueOrNot ? " = " : " <> ");
dbDataType = mapping.DataColTypesByObjAttr[equal.Property];
AppendParameter(queryToAddTo, equal.Value, dbDataType);
}
}
else if (expr is EqualInsensitiveExpression)
{
EqualInsensitiveExpression iequal = (EqualInsensitiveExpression)expr;
col = colPrefix + mapping.AllDataColsByObjAttrs[iequal.Property];
if (iequal.Value == null)
{
queryToAddTo.Sql.Append(col);
queryToAddTo.Sql.Append(trueOrNot ? " IS NULL" : " IS NOT NULL");
}
else
{
string lower = _connDesc.LowerCaseFunction();
queryToAddTo.Sql.Append(lower).Append("(");
queryToAddTo.Sql.Append(col).Append(") ");
queryToAddTo.Sql.Append(trueOrNot ? "= " : "<> ").Append(lower).Append("(");
dbDataType = mapping.DataColTypesByObjAttr[iequal.Property];
AppendParameter(queryToAddTo, iequal.Value, dbDataType);
queryToAddTo.Sql.Append(")");
}
}
else if (expr is GreaterExpression)
{
GreaterExpression greater = (GreaterExpression)expr;
queryToAddTo.Sql.Append(colPrefix);
queryToAddTo.Sql.Append(mapping.AllDataColsByObjAttrs[greater.Property]);
queryToAddTo.Sql.Append(trueOrNot ? " > " : " <= ");
dbDataType = mapping.DataColTypesByObjAttr[greater.Property];
AppendParameter(queryToAddTo, greater.Value, dbDataType);
}
else if (expr is LesserExpression)
{
LesserExpression lesser = (LesserExpression)expr;
queryToAddTo.Sql.Append(colPrefix);
queryToAddTo.Sql.Append(mapping.AllDataColsByObjAttrs[lesser.Property]);
queryToAddTo.Sql.Append(trueOrNot ? " < " : " >= ");
dbDataType = mapping.DataColTypesByObjAttr[lesser.Property];
AppendParameter(queryToAddTo, lesser.Value, dbDataType);
}
else if (expr is BitwiseAndExpression)
{
BitwiseAndExpression bitwise = (BitwiseAndExpression)expr;
string colName = colPrefix + mapping.AllDataColsByObjAttrs[bitwise.Property];
SqlClauseWithValue clause = _connDesc.MakeBitwiseAndClause(colName);
queryToAddTo.Sql.Append(clause.PartBeforeValue);
dbDataType = mapping.DataColTypesByObjAttr[bitwise.Property];
AppendParameter(queryToAddTo, bitwise.Value, dbDataType);
queryToAddTo.Sql.Append(clause.PartAfterValue);
DbCaches.Clauses.Return(clause);
queryToAddTo.Sql.Append(trueOrNot ? " = " : ") <> ");
AppendParameter(queryToAddTo, bitwise.Value, dbDataType);
}
else if (expr is LikeExpression)
{
LikeExpression like = (LikeExpression)expr;
queryToAddTo.Sql.Append(colPrefix);
queryToAddTo.Sql.Append(mapping.AllDataColsByObjAttrs[like.Property]);
queryToAddTo.Sql.Append(trueOrNot ? " LIKE " : " NOT LIKE ");
dbDataType = mapping.DataColTypesByObjAttr[like.Property];
AppendParameter(queryToAddTo, like.Value, dbDataType);
}
else if (expr is LikeInsensitiveExpression)
{
LikeInsensitiveExpression ilike = (LikeInsensitiveExpression)expr;
col = colPrefix + mapping.AllDataColsByObjAttrs[ilike.Property];
if (_connDesc.HasCaseInsensitiveLikeOperator())
{
string iLikeOperator = _connDesc.CaseInsensitiveLikeOperator();
queryToAddTo.Sql.Append(col);
queryToAddTo.Sql.Append(trueOrNot
? String.Format(" {0} ", iLikeOperator)
: String.Format(" NOT {0} ", iLikeOperator));
dbDataType = mapping.DataColTypesByObjAttr[ilike.Property];
AppendParameter(queryToAddTo, ilike.Value, dbDataType);
}
else
{
string lower = _connDesc.LowerCaseFunction();
queryToAddTo.Sql.Append(lower).Append("(");
queryToAddTo.Sql.Append(col).Append(") ");
queryToAddTo.Sql.Append(trueOrNot ? "LIKE " : "NOT LIKE ").Append(lower).Append("(");
dbDataType = mapping.DataColTypesByObjAttr[ilike.Property];
AppendParameter(queryToAddTo, ilike.Value, dbDataType);
queryToAddTo.Sql.Append(")");
}
}
else if (expr is PropertyInListExpression)
{
PropertyInListExpression inList = (PropertyInListExpression)expr;
IEnumerable listVals = inList.Values;
queryToAddTo.Sql.Append(colPrefix);
queryToAddTo.Sql.Append(mapping.AllDataColsByObjAttrs[inList.Property]);
dbDataType = mapping.DataColTypesByObjAttr[inList.Property];
queryToAddTo.Sql.Append(trueOrNot ? " IN (" : " NOT IN (");
bool firstIn = true;
foreach (object val in listVals)
{
if (val == null)
{
throw new NullReferenceException(
"Cannot include a null value in a list of possible values for " +
inList.Property + ".");
}
if (firstIn)
{
firstIn = false;
}
else
{
queryToAddTo.Sql.Append(", ");
}
AppendParameter(queryToAddTo, val, dbDataType);
}
if (firstIn)
{
throw new ArgumentException("Cannot query for " + inList.Property +
" values in an empty list.");
}
queryToAddTo.Sql.Append(")");
}
else if (expr is CriteriaExpression)
{
CriteriaExpression critExp = (CriteriaExpression)expr;
queryToAddTo.Sql.Append(trueOrNot ? "(" : " NOT (");
// This is slightly hacky, but basically even though we're now partway through
// assembling a SQL statement, we might have an empty nested expression. So rather
// than having "AND () AND" which isn't valid, we put "1=1" for empty nested criteria.
if ((critExp.NestedCriteria.Expressions != null) && (critExp.NestedCriteria.Expressions.Count > 0))
{
ExpressionListToQuery(queryToAddTo, critExp.NestedCriteria.BoolType,
critExp.NestedCriteria.Expressions, mapping, colPrefix);
}
else
{
queryToAddTo.Sql.Append("1=1");
}
queryToAddTo.Sql.Append(")");
}
else if (expr is HandWrittenExpression)
{
if (!trueOrNot)
{
throw new ArgumentException("You'll have to manually NOT your custom SQL.");
}
HandWrittenExpression hand = (HandWrittenExpression)expr;
// We'll assume it's SQL, hopefully parameterized.
queryToAddTo.Sql.Append(hand.Expression);
// If there are any parameters, add 'em.
if (hand.Parameters != null)
{
foreach (object aParam in hand.Parameters)
{
queryToAddTo.Params.Add(aParam);
}
}
}
else
{
throw new NotSupportedException("Expression type '" + expr.GetType() + "' is not supported.");
}
// Remember to close the parend.
queryToAddTo.Sql.Append(")");
return true;
}