private static bool GetPhraseQuery(PropertyInfo pi, object value, out PhraseQuery phraseQuery)
{
PhraseAttribute phraseAttr = pi.GetAttribute <PhraseAttribute>();
if (phraseAttr != null)
{
phraseQuery = new PhraseQuery(pi.Name, value.ToString(), phraseAttr);
return(true);
}
phraseQuery = null;
return(false);
}
public PhraseQuery(string propertyName, string input, PhraseAttribute phraseAttribute)
{
Tokens = Parse(input);
Parameters = new DynamicParameters();
var expressions = new List <string>();
int index = 0;
Tokens.ToList().ForEach((qt) =>
{
string paramName = $"{propertyName}{++index}";
Parameters.Add(paramName, FormatValue(qt.Value, phraseAttribute.ConcatenateWildcards), DbType.String);
expressions.Add(ParamExpression(qt.IsNegated, paramName, phraseAttribute.ConcatenateWildcards));
});
Expression = "(" + string.Join(" OR ", phraseAttribute.ColumnNames.Select(col => $"({string.Join(" AND ", expressions.Select(expr => $"{phraseAttribute.LeadingColumnDelimiter}{col}{phraseAttribute.EndingColumnDelimiter} {expr}"))})")) + ")";
}
public static string ResolveSql(string sql, object parameters, out DynamicParameters queryParams)
{
if (parameters == null)
{
queryParams = null;
return(RemoveTokens(sql));
}
string result = sql;
if (HasJoinToken(result))
{
result = ResolveJoins(result, parameters);
}
List <string> terms = new List <string>();
var queryProps = GetProperties(parameters, result, out IEnumerable <string> builtInParams);
queryParams = new DynamicParameters();
foreach (var prop in queryProps)
{
var value = prop.GetValue(parameters);
if (value != null && !prop.HasAttribute <PhraseAttribute>())
{
queryParams.Add(prop.Name, value);
}
}
var whereBuilder = GetWhereTokens();
string token;
if (FindWhereToken(result, out token))
{
// loop through this query's properties, but ignore base properties (like ResolvedSql and TraceCallback) since they are never part of WHERE clause
foreach (var pi in queryProps)
{
object value = pi.GetValue(parameters);
if (HasValue(value))
{
// built-in params are not part of the WHERE clause, so they are excluded from added terms
if (!builtInParams.Contains(pi.Name.ToLower()))
{
var cases = pi.GetCustomAttributes(typeof(CaseAttribute), false).OfType <CaseAttribute>();
var selectedCase = cases?.FirstOrDefault(c => c.Value.Equals(value));
if (selectedCase != null)
{
terms.Add(selectedCase.Expression);
}
else
{
WhereAttribute whereAttr = pi.GetAttribute <WhereAttribute>();
if (whereAttr != null)
{
terms.Add(whereAttr.Expression);
}
else
{
PhraseAttribute phraseAttr = pi.GetAttribute <PhraseAttribute>();
if (phraseAttr != null)
{
var phraseQuery = new PhraseQuery(pi.Name, value.ToString(), phraseAttr.ColumnNames, phraseAttr.LeadingColumnDelimiter, phraseAttr.EndingColumnDelimiter);
queryParams.AddDynamicParams(phraseQuery.Parameters);
terms.Add(phraseQuery.Expression);
}
}
}
}
}
}
result = ResolveWhereToken(token, result, terms);
}
if (sql.Contains(InternalStringExtensions.OffsetToken))
{
if (FindOffsetProperty(parameters, out PropertyInfo offsetProperty))
{
var offsetAttr = offsetProperty.GetCustomAttribute <OffsetAttribute>();
int page = (int)offsetProperty.GetValue(parameters);
result = result.Replace(InternalStringExtensions.OffsetToken, offsetAttr.GetOffsetFetchSyntax(page));
}
}
// remove any leftover tokens (i.e. {orderBy}, {join} etc)
var matches = Regex.Matches(result, "(?<!{)({[^{\r\n]*})(?!{)");
foreach (Match match in matches)
{
result = result.Replace(match.Value, string.Empty);
}
return(result);
}