I am building a query builder that works pretty well except I can’t figure out how to group the filters together by column to get something like: where (col1=1 or col1=2) and (col2=5 and col2=3). What I have is something like the following code. Everytime there is a traversal it means there is a new set of filters for a column that need to be grouped but I can’t figure out how to coax the expression to add the parenthesis?
public static GridFilterSet TraverseWhere<T>(GridFilterSet filterSet, SqlExpression<T> exp, ref int pCount)
{
if (filterSet != null)
{
foreach (var f in filterSet.Filters)
{
if (f.IsFilter())
{
CreateWhere(exp, filterSet.Logic, f, ref pCount);
}
else
{
TraverseWhere<T>(f, exp, ref pCount);
}
}
}
return filterSet;
}
public static void CreateWhere<T>(SqlExpression<T> exp, string Operator, GridFilter filter, ref int pCount)
{
if (Operator.ToUpper() == "AND")
{
exp.Where(filter.TranslateOperatorToSql(pCount), filter.Value);
}
else
{
exp.Or(filter.TranslateOperatorToSql(pCount), filter.Value);
}
pCount++;
}
Unfortunately that just gives me (col1=1) or (col1=2)… when I need ( ( col1=1) or (col1=2)) and… I think your post did help give me an idea so I’ll see of I can just save the where expressions for each grouping and add them at one time.
Otherwise if you’re trying to group C# Expressions you would need to combine them before adding them to the SqlExpression<T>, see this answer for some examples:
I know I didn’t add it to the post but the where clauses I’m building are strings:
filter.TranslateOperatorToSql(pCount) returns something like “customerId=1” and the Where auto adds the “AND” so I’m having trouble determining how to do this. Is my only option to manually build the string and just do q.WhereExpression= computedWhereString?
There’s no SQL Expression grouping builder in SqlExpression<T> if that’s what you’re looking for, you’d need to construct the condition with how you want it in your App. Even if there was your App’s logic would still need to determine when to start and end the groupings, in this case you’d pass in a StringBuilder to build a string SQL Expression and wrap the expression with (sql).
But I’d add the condition using the APIs, e.g. Where() or .UnsafeWhere() if you need to by-pass raw SQL checks.