Radzen DataGrid Custom Filtering

Radzen.Blazor Components
12

Okay, so I found a solution.. based on your QueryableExtensions in: /Radzen.Blazor/QueryableExtension.cs

I made the following helpers:

public static class FilterDescriptorExtensions
{
	internal static readonly IDictionary<FilterOperator, string> ODataFilterOperators = new Dictionary<FilterOperator, string>
	{
		{FilterOperator.Equals, "eq"},
		{FilterOperator.NotEquals, "ne"},
		{FilterOperator.LessThan, "lt"},
		{FilterOperator.LessThanOrEquals, "le"},
		{FilterOperator.GreaterThan, "gt"},
		{FilterOperator.GreaterThanOrEquals, "ge"},
		{FilterOperator.StartsWith, "startswith"},
		{FilterOperator.EndsWith, "endswith"},
		{FilterOperator.Contains, "contains"},
		{FilterOperator.DoesNotContain, "DoesNotContain"}
	};

	private static string GetColumnODataFilter<T>(RadzenDataGrid<T> dataGrid, FilterDescriptor column, bool second = false)
	{
		var property = column.Property.Replace('.', '/');

		var columnFilterOperator = !second ? column.FilterOperator : column.SecondFilterOperator;

		var value = !second ? (string)Convert.ChangeType(column.FilterValue, typeof(string)) :
			(string)Convert.ChangeType(column.SecondFilterValue, typeof(string));

		var filterPropertyType = column.Property.GetType();

		if (dataGrid.FilterCaseSensitivity == FilterCaseSensitivity.CaseInsensitive && filterPropertyType == typeof(string))
		{
			property = $"tolower({property})";
		}

		if (filterPropertyType == typeof(string))
		{
			if (!string.IsNullOrEmpty(value) && columnFilterOperator == FilterOperator.Contains)
			{
				return dataGrid.FilterCaseSensitivity == FilterCaseSensitivity.CaseInsensitive ?
					$"contains({property}, tolower('{value}'))" :
					$"contains({property}, '{value}')";
			}
			else if (!string.IsNullOrEmpty(value) && columnFilterOperator == FilterOperator.DoesNotContain)
			{
				return dataGrid.FilterCaseSensitivity == FilterCaseSensitivity.CaseInsensitive ?
					$"not(contains({property}, tolower('{value}')))" :
					$"not(contains({property}, '{value}'))";
			}
			else if (!string.IsNullOrEmpty(value) && columnFilterOperator == FilterOperator.StartsWith)
			{
				return dataGrid.FilterCaseSensitivity == FilterCaseSensitivity.CaseInsensitive ?
					$"startswith({property}, tolower('{value}'))" :
					$"startswith({property}, '{value}')";
			}
			else if (!string.IsNullOrEmpty(value) && columnFilterOperator == FilterOperator.EndsWith)
			{
				return dataGrid.FilterCaseSensitivity == FilterCaseSensitivity.CaseInsensitive ?
					$"endswith({property}, tolower('{value}'))" :
					$"endswith({property}, '{value}')";
			}
			else if (!string.IsNullOrEmpty(value) && columnFilterOperator == FilterOperator.Equals)
			{
				return dataGrid.FilterCaseSensitivity == FilterCaseSensitivity.CaseInsensitive ?
					$"{property} eq tolower('{value}')" :
					$"{property} eq '{value}'";
			}
			else if (!string.IsNullOrEmpty(value) && columnFilterOperator == FilterOperator.NotEquals)
			{
				return dataGrid.FilterCaseSensitivity == FilterCaseSensitivity.CaseInsensitive ?
					$"{property} ne tolower('{value}')" :
					$"{property} ne '{value}'";
			}
		}
		else if (typeof(IEnumerable).IsAssignableFrom(column.Property.GetType()) && filterPropertyType != typeof(string))
		{
		}
		else if (PropertyAccess.IsNumeric(filterPropertyType))
		{
			return $"{property} {ODataFilterOperators[columnFilterOperator]} {value}";
		}
		else if (filterPropertyType == typeof(bool) || filterPropertyType == typeof(bool?))
		{
			return $"{property} eq {value.ToLower()}";
		}
		else if (filterPropertyType == typeof(DateTime) ||
				filterPropertyType == typeof(DateTime?) ||
				filterPropertyType == typeof(DateTimeOffset) ||
				filterPropertyType == typeof(DateTimeOffset?))
		{
			return $"{property} {ODataFilterOperators[columnFilterOperator]} {DateTime.Parse(value, null, System.Globalization.DateTimeStyles.RoundtripKind).ToString("yyyy-MM-ddTHH:mm:ss.fffZ")}";
		}
		else if (filterPropertyType == typeof(Guid) || filterPropertyType == typeof(Guid?))
		{
			return $"{property} {ODataFilterOperators[columnFilterOperator]} {value}";
		}

		return string.Empty;
	}

	public static string ToODataFilterString<T>(this IEnumerable<FilterDescriptor> columns, RadzenDataGrid<T> dataGrid)
	{
		Func<FilterDescriptor, bool> canFilter = (c) => c.Property != null &&
			!(c.FilterValue == null || c.FilterValue as string == string.Empty) && c.Property != null;

		if (columns.Where(canFilter).Any())
		{
			var gridLogicalFilterOperator = columns.FirstOrDefault()?.LogicalFilterOperator;
			var gridBooleanOperator = gridLogicalFilterOperator == LogicalFilterOperator.And ? "and" : "or";

			var whereList = new List<string>();
			foreach (var column in columns.Where(canFilter))
			{
				var property = column.Property.Replace('.', '/');

				var value = (string)Convert.ChangeType(column.FilterValue, typeof(string));
				var secondValue = (string)Convert.ChangeType(column.SecondFilterValue, typeof(string));

				if (!string.IsNullOrEmpty(value))
				{
					var linqOperator = ODataFilterOperators[column.FilterOperator];
					if (linqOperator == null)
					{
						linqOperator = "==";
					}

					var booleanOperator = column.LogicalFilterOperator == LogicalFilterOperator.And ? "and" : "or";

					if (string.IsNullOrEmpty(secondValue))
					{
						whereList.Add(GetColumnODataFilter(dataGrid, column));
					}
					else
					{
						whereList.Add($"({GetColumnODataFilter(dataGrid, column)} {booleanOperator} {GetColumnODataFilter(dataGrid, column, true)})");
					}
				}
			}

			return string.Join($" {gridBooleanOperator} ", whereList.Where(i => !string.IsNullOrEmpty(i)));
		}

		return "";
	}
}

I can use it like this:

var filters = args.Filters.ToList();
filters.Add(new FilterDescriptor
{
	FilterOperator = FilterOperator.Contains,
	FilterValue = "Jord",
	LogicalFilterOperator = LogicalFilterOperator.And,
	Property = "FamilyName"
});
string odataFilter = filters.ToODataFilterString(DataGrid);

So that's working nicely.. I just pass the result of that to uri.GetODataUri(). Voila!

And I'd like to leave you with an idea I had for a new component: How about a query builder, like the following?:

And being able to have the grid and query builder connected with a common data source, like this:

That would be ideal..

Any thoughts on this?