Convert Filters to Where Clause

[Greg]

I'm looking for a simple way to implement server-side filtering using the RadDataFilter for WPF. All examples I've seen so far seem too complex.

Is there an easy way to translate the collection of filterdescriptors into a SQL Where clause that can then be passed to a dynamic SQL Server stored procedure?

What I'm looking for is the code (VB .Net) to take the RadDataFilters/FilterDescriptors from a RadDataFilter control and translate those into the SQL Where clause.

Of course, if there was an easier way to pull the result set from the server using the RadDataFilter filters that would be nice too!

Thanks in advance ...

Greg

[Rossen Hristov]

Hi Greg,

That will not be possible if you don't write some code that will parse the descriptors and translate them to an SQL string.

However, I don't think this would be the best architecture. If you use a simple table with LINQ to SQL, you can simply pass your IQueryable the QueryableCollectionView's constructor and when you bind RadDataFilter to the QCV, it will automagically translate its filter descriptors to a Where done on directly on the server thanks to the IQueryProvider.

I hope this helps.

Best wishes,
Ross
the Telerik team

[Greg]

Hello Ross.

I've not done much with Linq to SQL so I'm wondering if you can explain this in a little more depth.

When you say "your IQueryable" - what is that, the Linq to SQL table? From where is that being created and how is it passed to QueryableCollectionView's constructor?

A code sample (VB .Net) would really help!

Thanks,

Greg

[Rossen Hristov]

Hi Greg,

Well, imagine that you had no RadControls for WPF at all, how would you display the data from your data-base in your WPF application?

You can start learning about LINQ to SQL and about LINQ queries from these articles:

Data Visualization in WPF with LINQ to SQL and Data Binding

WPF Data Binding, With LINQ

Build a WPF application that uses LINQ-to-SQL to access the NorthWind database

Using a Linq To SQL Query as a WPF DataContext

Of course, I am not saying that you must definitely use Linq to SQL. You can use one of the following:

• LINQ to Objects - access data coming from CLR collections
• LINQ to SQL - access data from Microsoft SQL databases
• LINQ to Entities - access data from ADO.NET entity providers, i.e. EntityFramework
• LINQ to Datasets - access data from typed or untyped datasets
• LINQ to XML - access data from XML sources

So, after reading the above articles, you can pass the query to the constructor of the class QueryableCollectionView. Then bind RadDataFilter to this QueryableCollectionView and it will start to translate its FilterDescriptors to the query automatically, which on its turn will go and filter the data on the server.

I hope this helps.

Best wishes,
Ross
the Telerik team

[Shankar]

Does this only work if we use LINQ to SQL? We are not using LINQ to access database calls..

I am looking for a similar functionality where I just use the RadDataFilter to present the users a UI which will create a SQL on the fly and query the database on the server.

Please let me know, thanks,

[Vlad]

Hi,

 You can use any LINQ provider - EF, Open Access, etc. Not only LINQ to SQL.

Greetings,
Vlad
the Telerik team

[Shankar]

Hello Vlad,

I am not using LINQ for my database transactions. Is there any other way? Please help.

[Rossen Hristov]

Hello,

RadDataFilter cannot simply "create SQL on the fly". What exact SQL should it create? For which database out of the possible hundreds of different databases that exist in the world should it create it? For which specific version of this database should it create it? I hope you get my point.

That is what LINQ Providers were invented for. In your .NET code you have a LINQ query and it stays the same no matter what the data base is. The LINQ provider is the one that knows how to translate this querty to SQL Server 2008, Oracle 11g, your XML DB, you name it.

But since you can't use any of these things, here is what you will need to do.

All the information that RadDataFilter has gathered from the user is stored in its FilterDescriptors property. Use your debugger to examine what is inside. You would have to read the information stored in RadDataFilter.FilterDescriptors, and then somehow construct your SQL query, which only you know how to construct, since only you know what your database expects. That's it.

That's what LINQ-to-SQL and LINQ-to-Entities is for, but if you really want to do everything by hand, then this is your call.

All the best,
Ross
the Telerik team

[Shankar]

I understand your point, I thought the control was flexible enough to map the operators you have to what it needs to map to (depending on the database you have, or from user's input in a config file or so).

If parsing and constructing the String is the only solution (other than LINQ to SQL), I will take that path. Thank you so much.

[Les]

I desperately need server-side filtering. Returning a million records and then filtering them is exactly what I'm trying to avoid. Ross said "you can simply pass your IQueryable the QueryableCollectionView's constructor and when you bind RadDataFilter to the QCV, it will automagically translate its filter descriptors to a Where done on directly on the server thanks to the IQueryProvider." That's EXACTLY what I need.

Now if I only had the slightest idea how to do that. What's my IQueryable? What's the QueryableCollectionView? The word "arcane" was invented for this situation. An example would be worth a lot more than these 36 words.

[Dimitrina]

Hello,

Basically, you need to use an ItemsSource that implements IQueryable. For example the QueryableCollectionView. Please note filtering is a data operation which means it is done by building and executing a LINQ query appending proper Where clause over the source collection. 

Regards,
Dimitrina
Telerik

 

 

[LP]

I'm trying to use a radDataFilter in my ViewModel for server-side data retrieval. Retrieving a million records to the UI before I filter them is not feasible. I saw a code example that looked like this:

     var query = mgr.INVENTORY.Where(UIForm.myFilter.FilterDescriptor)

but an hour of experimentation has produced nothing that's even syntactically correct. Do you have a complete working example of how to pass the FilterDescriptors to an E-F query?

[Dimitrina]

Hello Les,

You can check the Pure Server-Side Filtering with RadGridView and WCF RIA Services blog post on how to have the filtering happening on the served. The approach with RadDataFilter and EntityFrameworkDataSource would be the same. Please keep in mind RadDataFilter and RadGridView share the same data-engine.

Regards,
Dimitrina
Telerik