Radzen DataGrid Custom Filtering

I am moving to Blazor and attempting to work out how to use custom filtering in Radzen's DataGrid. Here is an example of what I am looking to do (from the old app I need to migrate):

As you can see, there is a filtering section above the grid. Of course I know that Radzen has built-in filtering in the grid itself, but that doesn't help when the filter you want to apply is to a column that is not visible on the grid..

So basically: how do you filter on columns that are not in the grid? I am using OData.. if there is a way I can modify the OData filter when the search button is clicked and reload the grid, that would be helpful.

You should apply the filter to your OData service and set the Data property of the RadzenDataGrid with the result. Something like this:

<RadzenDataGrid Data=@MyData>
</RadzenDataGrid>

@code {

async Task OnSearchClicked()
{
     var filter = BuildFilterQueryFromFilterUI();

     MyData = await MyOdataService.GetData(filter);
}

}

Thanks for the reply.. Sorry, I don't follow though. I have my setup quite similar to this:

https://blazor.radzen.com/datagrid-odata

I have a demo of what I am doing in this code: https://github.com/gordon-matt/Extenso/tree/develop

Look at the develop branch and find the demo named, "Demo.Extenso.AspNetCore.Blazor.OData"

If you could please look at the People.razor file in there and help me figure out what I am missing, I'd really appreciate it.

Note: From your answer, I'm guessing there's a special ODataService I can use instead of using HttpClient as per the Radzen demo I linked to above (and am using in my demo). If so, that'd be great.. is that a separate NuGet package? I think I may have seen it when installing the other packages. Do you have a link to the documentation for how to use that?

Thanks

The OData service used in our demos is generated by Radzen and you can check it here:

In that case, I guess I just need help with the BuildFilterQueryFromFilterUI() part. How do I build a filter from values in textboxes, etc?

You need to construct OData url from your filtering input components.

Well yes, obviously.. are you saying I need to manually do something like: string query = "$filter=Name eq '" + NameTextBox.Value + "'"; ? What I'm looking for is a helper class or something.. isn't there something like that?

There is an extension method that might help you and it is used in the service I've already posted:

The method itself is not something very special:

I appreciate your quick responses, but I'm not sure we're on the same page here. I am well aware of that and am in fact using it. Here's a screenshot of me using that and look at the inspected value of args.Filter:

You can see it's an OData expression, pased from the grid. That's not helpful.. I was hoping there would be something like this:

string odataFilter = ODataFilterBuilder.Create
{
	new Filter
	{
		LogicOperator = LogicOperator.And,
		Field = "Name",
		Operator = FIlterOperator.Contains,
		Value = "ABC"
	},
	new Filter
	{
		LogicOperator = LogicOperator.Or,
		Field = "Foo",
		Operator = FilterOperator.Contains,
		Value = "Bar"
	}
};

// etc..
uri = uri.GetODataUri(filter: odataFilter, top: top, skip: skip, orderby: orderby, expand: expand, select: select, count: count);
// etc...

Perhaps I am missing something obvious though.. but it looks like I will have to manually build an OData filter. If that's the case, that's fine.. I was just hoping to find a better way.

You can probably use the Filters property of the LoadDataArgs.

Thanks Korchev.

That did look like it might work.. I tested with this:

var filters = args.Filters.ToList();
filters.Add(new FilterDescriptor
{
	FilterOperator = FilterOperator.Contains,
	FilterValue = "ABC",
	LogicalFilterOperator = LogicalFilterOperator.And,
	Property = "FamilyName"
});
args = new LoadDataArgs
{
	Filters = filters
};
var result = await ODataService.FindAsync(filter: args.Filter, top: args.Top, skip: args.Skip, orderby: args.OrderBy, count: true);

However, even though the Filters property was modified, the Filter property does not get updated. I wonder if you guys could maybe do something there? Like this:

public IEnumerable<FilterDescriptor> Filters
{
	get => filters;
	set
	{
		filters = value;
		if (value != null && value.Count() > 0)
		{
			// TODO: Update `Filter` property with correct OData..
		}
	}
}

Or alternatively: how do you guys convert from a collection of FilterDescriptor to an OData filter string anyway? Is whatever helper class that does that public? If not, can you make it public in the next version? Then we can easily do something like this instead:

var filters = args.Filters.ToList();
filters.Add(new FilterDescriptor
{
	FilterOperator = FilterOperator.Contains,
	FilterValue = "ABC",
	LogicalFilterOperator = LogicalFilterOperator.And,
	Property = "FamilyName"
});
string odataFilterExpression = RadzenODataHelpers.GetODataFilterString(filters);

And I can pass that to the GetODataUri() extension method..

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?