How to filter on expanded tables


I tried to apply your example for filtering a datagrid with a search term from a separate text box on my case but I'm not sure how to do this correctly. Let me exmplain:

In the page Load event, I get a table called "Customer" and also set the $expand property to "Contact", another table which holds the contact information of my customers from the customer table (simple foreign key relationship).

This allows me to show the customers in a datagrid. Due to settings $expand to "Contact", my datagrid can not only show columns from the "Customer" table, but also from "Contact", e.g. "Contact.Name".

That is fine so far and no problem. Now I added a textbox and a button to the page where the button click event sets the value of the textbox to a property called "searchTerm" that I want to use to filter my customers from grid on certain fields. However, I can only create filters for columns from the "Customer" table, not from the expanded "Contact" table.

Any idea, how I can filter also on columns from a table that has been added using the $expand feature?

Thanks for your help!

Best regards,


Hi @JustJoe,

You can use raw filter expression:

1 Like

Thanks a lot! That did the trick! :slight_smile:

Thank you for this, helped us a lot!

Hi enchev!

Is there a smooth way of using this query builder to include a search with AND-condition?
We have implemented your above example and it works great, but would also like to be able to write something like this with a delimiter in the search field, looking at values in different columns:
Not started AND Low Priority

Thanks in advance, hope you're having a great wednesday!

Filter logical operator can be changed to AND, check the first screenshot in my post.

Yes, I know you can change from OR to AND. I'm just not sure how to implement it. For example now we have these lines:

How would I go about to use the AND-filter if the user wants to use this search entry:
ExampleHeader AND ExampleDescription
Is the phrase "AND" a standard delimiter? So all I have to do is enter these same lines in the query builder but using the AND-filter?

The OR next Filter in the top left corner is drop down

Yes, I know about this:

Currently our filter just looks for the search string in any of the columns. Do we need to make some group filter expression to be able to search for two values in different columns at the same time? Ideally I guess you should use some collection of all column properties to not have to add every property line by line.

Yes, you can use group filters. There is separate AND/OR for every group filter.

Raw filter expressions seems to be the way to get it to work. Will be looooong strings to code (we have 50+ columns).

Edit: When using the query builder with group filtering, it's really buggy and deletes entries from time to time etc. You could probably reproduce those bugs very easily by playing around with it a bit.

For long, complex, custom filters you might use partial method:

1 Like

Our search filter is almost complete, but we're having a hard time with some DateTime-values. We've been trying with this kind of raw expression: LastModified.ToString().Contains("${search}"). It works for DateTimes looking like the right one below, but not the left one. Any ideas why? If I would search for "2022-03" it would only find it in the DesiredClosingDate column below, even though LastModified also contains the string.

Would be grateful if anyone had any ideas as to why the above expression does not work for DateTime-format with timestamp... I've tried extending the expression with DateTime.Date to get the same string format but then I get an error that DateTime doesn't have a method/enum called Date.

If it works with one of the DateTime properties should work with the other as well. Can you check if it’s a DateTime and not string? Or DateTimeOffset?

Hi enchev!
I realized we have this template expression on the column:
But if I remove ToString() in LastModified.ToString().Contains("${search}") it gives me this error in browser:

Any ideas?

DataGrid column Template is not related to filtering - only what will be rendered in the cell. Also if the property is nullable such expression will fail in case of null LastModified.ToString().Contains("${search}")

Yeah, you are right about the template, I should've understood that but I'm grasping for anything right now.

We have several nullable DateTime-columns that does work with the expression:

I'm very puzzled as to why it works with some of the DateTimes and not others...

You can use also SQL Profiler or Radzen output to check what SQL expressions EF will generate for filtering. Here is an example with CRUD pages with filtering of Northwind Order where I've added OrderDate (which is nullable) as raw filter:

np() function is the null propagation in dynamic LINQ

1 Like

Thank you for the reply!
We can find the DateTimes as long as we just enter a number, but if we try something like "2022-" it doesn't work. In your example above, can you find the orderdates if you put in the whole string like "7/15/1996" ?