Skip to content

Instantly share code, notes, and snippets.

@Dillie-O
Created May 9, 2013 20:28
Show Gist options
  • Save Dillie-O/5550329 to your computer and use it in GitHub Desktop.
Save Dillie-O/5550329 to your computer and use it in GitHub Desktop.
Giving an existing (simple) ObjectQuery, this method dynamically adds the appropriate where clause and parameterized values based on the search criteria and parameters passed to it.
public static ObjectQuery<T> BuildObjectQuery<T>
(ObjectQuery<T> query,
NameValueCollection parameters,
Dictionary<string, SearchParameter> searchCriteria)
{
var results = query;
// If there are parameters in our collection, build a where clause
// using the search criteria dictionary. Otherwise the method ends
// and the query remains the same.
if (parameters.Count > 0)
{
var whereClause = new StringBuilder();
var isFirstParameter = true;
// Before processing parameters, check for from/to pairings for
// range queries. If a pairing exists, replace with the
// appropriate range key.
var keys = parameters.AllKeys.ToList();
foreach (var key in keys)
{
// Since parameters can be removed midloop, check for value
// before processing
if (!parameters.AllKeys.Contains(key)) continue;
// Test for from and to index values for simplified processing
var indexFrom = key.IndexOf("From", StringComparison.Ordinal);
var indexTo = key.IndexOf("To", StringComparison.Ordinal);
// Skip key if it is not a from/to parameter.
if (indexFrom <= 0 && indexTo <= 0) continue;
// Process parameter based on what name was found (from/to).
var keyPrefix = indexFrom > 0 ? key.Substring(0, indexFrom) : key.Substring(0, indexTo);
var fromKey = keyPrefix + "From";
var toKey = keyPrefix + "To";
// Make sure that there is a From and a To key to process.
if (!parameters.AllKeys.Contains(fromKey) || !parameters.AllKeys.Contains(toKey)) continue;
// Add range parameter, remove corresponding from/to
// parameters, and remove paired from/to key to prevent
// duplicate processing.
var rangeValue = parameters[fromKey] + "|" + parameters[toKey];
parameters.Add(keyPrefix + "Range", rangeValue);
parameters.Remove(keyPrefix + "From");
parameters.Remove(keyPrefix + "To");
}
for (var i = 0; i < parameters.Count; i++)
{
// Check not Global Search param
if (parameters.GetKey(i) == Globals.QS_GLOBAL_SEARCH) continue;
// Build Where clause
var itemKey = parameters.GetKey(i);
var itemValue = parameters.Get(i);
var itemClause = string.Empty;
SearchParameter criteria;
ObjectParameter queryParameter;
// Make sure search key exists
if (!searchCriteria.TryGetValue(itemKey, out criteria))
{
throw new Exception("Search Criteria Does Not Exist");
}
if (!isFirstParameter)
{
whereClause.Append(" AND ");
}
else
{
isFirstParameter = false;
}
// Most search criteria can simply be appended to the
// query. Some are multi-value selectors and need to be
// processed accordingly.
if (!criteria.HasMultiple)
{
// Most parameters are single values, but if there is a range
// parameter we must split the values up.
if (string.IsNullOrEmpty(criteria.VariableNameSecondary))
{
itemClause = String.Format(criteria.Criteria, criteria.VariableName);
whereClause.Append(itemClause);
// Add parameter to query
var valueText = itemValue;
if (criteria.IsWildcard)
{
valueText = "%" + valueText + "%";
}
// Most of the time we use the value specified. However, null values
// require the DBNull value.
queryParameter = valueText != "null" ? new ObjectParameter(criteria.ParameterName, Type.GetType(criteria.ParameterType, true)) { Value = valueText } :
new ObjectParameter(criteria.ParameterName, Type.GetType(criteria.ParameterType, true)) { Value = DBNull.Value };
query.Parameters.Add(queryParameter);
}
else
{
itemClause = String.Format(criteria.Criteria, criteria.VariableName,
criteria.VariableNameSecondary);
whereClause.Append(itemClause);
// Add parameter to query
var splitValue = itemValue.Split('|');
if (criteria.IsWildcard)
{
splitValue[0] = "%" + splitValue[0] + "%";
splitValue[1] = "%" + splitValue[1] + "%";
}
// Most of the time we use the value specified. However, null values
// require the DBNull value.
queryParameter = splitValue[0] != "null" ?
new ObjectParameter(criteria.ParameterName, Type.GetType(criteria.ParameterType, true)) { Value = splitValue[0] } :
new ObjectParameter(criteria.ParameterName, Type.GetType(criteria.ParameterType, true)) { Value = DBNull.Value };
query.Parameters.Add(queryParameter);
// Most of the time we use the value specified. However, null values
// require the DBNull value.
queryParameter = splitValue[1] != "null" ?
new ObjectParameter(criteria.ParameterNameSecondary, Type.GetType(criteria.ParameterTypeSecondary, true)) { Value = splitValue[1] } :
new ObjectParameter(criteria.ParameterNameSecondary, Type.GetType(criteria.ParameterTypeSecondary, true)) { Value = DBNull.Value };
query.Parameters.Add(queryParameter);
}
}
else
{
// Status description potentially contains multiple
// values, white are split into a nested OR clause.
var values = itemValue.Split(',').Select(Convert.ToString).ToList();
if (values.Count > 0)
{
for (var j = 0; j <= values.Count - 1; j++)
{
// Jump to next item in the loop if empty.
if (String.IsNullOrEmpty(values[j])) continue;
if (j != 0)
{
whereClause.Append(" OR ");
}
else if (values.Count > 1)
{
whereClause.Append(" (");
}
itemClause = String.Format(criteria.Criteria, criteria.VariableName + j);
whereClause.Append(itemClause);
// Add parameter to query
var valueText = values[j];
if (criteria.IsWildcard)
{
valueText = "%" + valueText + "%";
}
// Add parameter to query
queryParameter = new ObjectParameter
(criteria.ParameterName + j, Type.GetType(criteria.ParameterType, true)) { Value = valueText };
query.Parameters.Add(queryParameter);
}
if (values.Count > 1)
{
whereClause.Append(") ");
}
}
}
}
// Append where clause to results.
results = query.Where(whereClause.ToString());
}
return results;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment