Convert Filters to Where Clause

13 posts, 0 answers
  1. Greg
    Greg avatar
    29 posts
    Member since:
    Oct 2007

    Posted 12 Jan 2011 Link to this post

    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

  2. Rossen Hristov
    Admin
    Rossen Hristov avatar
    2478 posts

    Posted 13 Jan 2011 Link to this post

    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
    Let us know about your Windows Phone 7 application built with RadControls and we will help you promote it. Learn more>>
  3. UI for WPF is Visual Studio 2017 Ready
  4. Greg
    Greg avatar
    29 posts
    Member since:
    Oct 2007

    Posted 13 Jan 2011 Link to this post

    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
  5. Rossen Hristov
    Admin
    Rossen Hristov avatar
    2478 posts

    Posted 14 Jan 2011 Link to this post

    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
    Let us know about your Windows Phone 7 application built with RadControls and we will help you promote it. Learn more>>
  6. Shankar
    Shankar avatar
    26 posts
    Member since:
    Dec 2011

    Posted 27 Jul 2012 Link to this post

    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,
  7. Vlad
    Admin
    Vlad avatar
    11100 posts

    Posted 30 Jul 2012 Link to this post

    Hi,

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

    Greetings,
    Vlad
    the Telerik team

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.

  8. Shankar
    Shankar avatar
    26 posts
    Member since:
    Dec 2011

    Posted 01 Aug 2012 Link to this post

    Hello Vlad,

    I am not using LINQ for my database transactions. Is there any other way? Please help.
  9. Rossen Hristov
    Admin
    Rossen Hristov avatar
    2478 posts

    Posted 02 Aug 2012 Link to this post

    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

    Explore the entire Telerik portfolio by downloading Telerik DevCraft Ultimate.

  10. Shankar
    Shankar avatar
    26 posts
    Member since:
    Dec 2011

    Posted 02 Aug 2012 Link to this post

    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.
  11. LesPinter
    LesPinter avatar
    3 posts
    Member since:
    Jul 2007

    Posted 29 Jan 2015 in reply to Greg Link to this post

    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.
  12. Dimitrina
    Admin
    Dimitrina avatar
    3769 posts

    Posted 03 Feb 2015 Link to this post

    Hello,

    Basically, you need to use an ItemsSource that implements IQueryable. For example the QueryableCollectionViewPlease 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
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  13. LP
    LP avatar
    1 posts
    Member since:
    Sep 2009

    Posted 04 Apr 2015 in reply to Vlad Link to this post

    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?

  14. Dimitrina
    Admin
    Dimitrina avatar
    3769 posts

    Posted 07 Apr 2015 Link to this post

    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
     

    See What's Next in App Development. Register for TelerikNEXT.

     
Back to Top
UI for WPF is Visual Studio 2017 Ready