SQL Filter in C# Collection View

Question

I have a window that displays a DataGrid in C# WPF. Each line of the datagrid is a Custom object. Inside my XAML datagrid definition I map the properties of the custom object to the columns of the DataGrid.

For Example

<DataGrid Name="Mydg">
 <DataGrid.Columns>
  <DataGridTextColumn Binding="{Binding Path=p1}" Header="Prop1"/>
  <DataGridTextColumn Binding="{Binding Path=p2}" Header="Prop2"/>    
 </DataGrid.Columns>
</DataGrid>

I create the window like this:

ObservableCollection<Custom_Object> records = new ObservableCollection<Custom_Object> { }; //A list of objects to display
records.Add(cust_obj1);
records.Add(cust_obj2);

CollectionView view = new CollectionView(records);
Custom_Window a_win = new Custom_Window();
a_win.Mydg.ItemsSource = view;

Now I need to implement a filter or rather a powerful query mechanism using plain SQL input. I have done this previously using DataTables.RowFilter property and providing an SQL like string typed by the user. However for my case above I only seem to be able to make a filter using a predicate which (to my limited knowledge of the language) can be used only for static and simple filtering.

Is there a quick way to implement an SQL like querying mechanism in my case?

This may help https://stackoverflow.com/questions/48868277/how-to-get-predicatet-from-string-expressions-at-runtime — M.Hassan, Mar 26 '18 at 21:41

score 1 · Answer 1 · answered Mar 26 '18 at 21:34

I'm not so sure about using sql scripts, but maybe what you mean is you want flexibility and it doesn't have to absolutely be sql.

Predicates can be pretty complicated, if you put your mind to it. Please see this: https://social.technet.microsoft.com/wiki/contents/articles/26673.wpf-collectionview-tips.aspx#Complicated_Filtering

Essentially, that builds a collection of predicates and checks they're all true. The user picks which ones to add into the list.

But you could use Linq on wherever your collection comes from. Linq queries can be chained so you can write one based on the output of another and optionally apply these. You would need to put together all your various options and have some way of selecting which to apply. That would also allow grouping and suchlike. Very simple introduction to chaining: https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/chaining-queries-example

And a bit more complicated which will maybe give you a better flavour. http://www.codestore.net/store.nsf/unid/BLOG-20110307-0346

All the answers in point to the same direction. That is that I have to implement some kind of filtering logic by myself. — msmechanized, Mar 27 '18 at 20:05
The predicates do need writing, but they're often pretty simple pieces of c#. Would it really be that much harder than using rowfilter? — Andy, Mar 27 '18 at 21:11

score 1 · Answer 2 · answered Mar 27 '18 at 10:21

You can't filter an in-memory ObservableCollection<Custom_Object> or CollectionView using SQL.

Assuming you are actually populating the collection with data coming from a database, you could filter this data using SQL before you populate the collection but to filter the actual CollectionView you should use a Predicate<object>.

This is simply a delegate that represents a method that accepts an object and returns a bool. You could define it as an anonymous method:

CollectionView view = new CollectionView(records);
view.Filter = o =>
{
    Custom_Object record = o as Custom_Object;
    return record != null && record.p1 == "?" && record.p2 == "?";
};

Or as a regular method:

private bool Filter(object o)
{
    Custom_Object record = o as Custom_Object;
    return record != null && record.p1 == "?" && record.p2 == "?";
}
...
view.Filter = Filter;

SQL Filter in C# Collection View

2 Answers2