public static string?GetSqlComparisonOperator(this FilteringConfiguration.Filter filter)
{
switch (filter.Operator)
{
case FilteringConfiguration.FilterComparisonOperator.DateEquals:
case FilteringConfiguration.FilterComparisonOperator.Equals:
return("=");
case FilteringConfiguration.FilterComparisonOperator.Contains:
return("LIKE");
case FilteringConfiguration.FilterComparisonOperator.GreaterThan:
return(">");
case FilteringConfiguration.FilterComparisonOperator.LowerThan:
return("<");
case FilteringConfiguration.FilterComparisonOperator.In:
return("IN");
case FilteringConfiguration.FilterComparisonOperator.IsNull:
return("IS NULL");
case FilteringConfiguration.FilterComparisonOperator.IsNotNull:
return("IS NOT NULL");
case FilteringConfiguration.FilterComparisonOperator.Between:
return("BETWEEN");
}
return(null);
}
private static string?GetBetweenOperatorParameterExpression <TResult>(FilteringConfiguration.Filter filter, string fieldName)
{
var names = GetBetweenOperatorParameterNames <TResult>(filter, fieldName);
return(names == null
? null
: $"{names[0]} AND {names[1]}");
}
private static string GetInOperatorParameterName <TResult>(FilteringConfiguration.Filter filter, string fieldName)
{
var filterValues = GetFilterValuesList <TResult>(filter);
var paramNames = GetInOperatorParamNames(fieldName, filterValues);
var inClause = string.Join(", ", paramNames);
return($"({inClause})");
}
public static object?ConvertToFieldType <TResult>(this FilteringConfiguration.Filter filter, object?value)
{
try
{
var fieldType = GetFieldType <TResult>(filter);
if (value == null || fieldType == null)
{
return(null);
}
fieldType = Nullable.GetUnderlyingType(fieldType) ?? fieldType;
if (value is JValue jValue)
{
value = jValue.Value;
}
if (IsDateTime(value, fieldType))
{
return(value);
}
if (IsEnum(fieldType))
{
// kd: Dapper treats enums as ints, so we pretend it is string not an enum.
return(ParseEnum(value, fieldType)?.ToString());
}
if (IsGuid(fieldType))
{
return(Guid.TryParse(value.ToString(), out var guidResult)
? (Guid?)guidResult
: null);
}
if (IsBool(fieldType))
{
return(Boolean.TryParse(value.ToString(), out var booleanResult)
? (Boolean?)booleanResult
: null);
}
var intTypes = new[] { typeof(long), typeof(int), typeof(short) };
var isInt = intTypes.Contains(fieldType) && intTypes.Contains(GetValueType(value));
if (GetValueType(value) != fieldType && !isInt)
{
return(null);
}
return(Convert.ChangeType(value, fieldType));
}
catch (InvalidCastException)
{
return(null);
}
}
private static object?GetFilterValue <TResult>(FilteringConfiguration.Filter filter)
{
var value = filter.ConvertToFieldType <TResult>(filter.Value);
if (filter.Operator == FilteringConfiguration.FilterComparisonOperator.Contains)
{
return($"%{value}%");
}
return(value);
}
private static List <object>?GetFilterValuesList <TResult>(FilteringConfiguration.Filter filter)
{
if (!(filter.Value is IEnumerable <object> values))
{
return(null);
}
var enumerable = values.Select(filter.ConvertToFieldType <TResult>).OfType <object>();
return(enumerable.ToList());
}
private static string GetFilterSql(FilteringConfiguration.Filter filter)
{
var sqlOperator = filter.GetSqlComparisonOperator();
var fieldName = filter.GetFieldName <TResult>() !;
var paramName = filter.GetParameterName <TResult>();
if (filter.Operator == FilteringConfiguration.FilterComparisonOperator.DateEquals)
{
return($"CAST({fieldName} AS DATE) {sqlOperator} CAST({paramName} AS DATE)");
}
return($"{fieldName} {sqlOperator} {paramName}".Trim());
}
public static Type?GetInnerFieldType <TResult>(this FilteringConfiguration.Filter filter)
{
if (filter.Field != null)
{
var type = GetField <TResult>(filter.Field)?.PropertyType;
if (type != null)
{
return(Nullable.GetUnderlyingType(type) ?? type);
}
}
return(null);
}
private static string[]? GetBetweenOperatorParameterNames <TResult>(FilteringConfiguration.Filter filter, string fieldName)
{
var filterValues = GetFilterValuesList <TResult>(filter);
if (filterValues?.Count != 2)
{
return(null);
}
return(new[]
{
$"@Filtering_{fieldName}_Left",
$"@Filtering_{fieldName}_Right"
});
}
public static Dictionary <string, object>?GetArguments <TResult>(this FilteringConfiguration.Filter filter)
{
if (filter.Operator == FilteringConfiguration.FilterComparisonOperator.IsNotNull ||
filter.Operator == FilteringConfiguration.FilterComparisonOperator.IsNull)
{
return(new Dictionary <string, object>());
}
if (filter.Operator == FilteringConfiguration.FilterComparisonOperator.In)
{
var fieldName = filter.GetFieldName <TResult>() !;
var filterValues = GetFilterValuesList <TResult>(filter);
if (filterValues == null)
{
return(null);
}
var paramNames = GetInOperatorParamNames(fieldName, filterValues);
return(paramNames.Zip(filterValues, (n, v) => new { n, v })
.ToDictionary(x => x.n, x => x.v));
}
if (filter.Operator == FilteringConfiguration.FilterComparisonOperator.Between)
{
var fieldName = filter.GetFieldName <TResult>() !;
var filterValues = GetFilterValuesList <TResult>(filter);
if (filterValues == null || filterValues.Count != 2)
{
return(null);
}
var paramNames = GetBetweenOperatorParameterNames <TResult>(filter, fieldName);
return(paramNames.Zip(filterValues, (n, v) => new { n, v })
.ToDictionary(x => x.n, x => x.v));
}
var paramName = filter.GetParameterName <TResult>();
var value = GetFilterValue <TResult>(filter);
var args = new Dictionary <string, object> {
{ paramName, value }
};
return(args);
}
public static string?GetParameterName <TResult>(this FilteringConfiguration.Filter filter)
{
var fieldName = filter.GetFieldName <TResult>() !;
switch (filter.Operator)
{
case FilteringConfiguration.FilterComparisonOperator.IsNotNull:
case FilteringConfiguration.FilterComparisonOperator.IsNull:
return("");
case FilteringConfiguration.FilterComparisonOperator.In:
return(GetInOperatorParameterName <TResult>(filter, fieldName));
case FilteringConfiguration.FilterComparisonOperator.Between:
return(GetBetweenOperatorParameterExpression <TResult>(filter, fieldName));
default:
return($"@Filtering_{fieldName}");
}
}
private static Type?GetFieldType <TResult>(FilteringConfiguration.Filter filter)
{
return(GetField <TResult>(filter.Field !)?.PropertyType);
}
public static string?GetFieldName <TResult>(this FilteringConfiguration.Filter filter)
{
return(GetFieldName <TResult>(filter.Field !));
}
