This is a migrated thread and some comments may be shown as answers.

RadGrid filtering with dropdowns

28 Answers 786 Views
Grid
This is a migrated thread and some comments may be shown as answers.
improwise
Top achievements
Rank 1
Iron
Iron
improwise asked on 05 Oct 2010, 11:15 PM
I've been looking into ways of doing filtering in a RadGrid by simply selecting a foreign key from a dropdown-list, like "Categories", "OrderStatus" etc. but no matter how hard I look, I can't seem to find a solution that doesn't feel very complicated, for what should really be one of the most common tasks used with a Grid. The other solutions probably work, but they seem over complicated, and thus harder to maintain over time etc.RadGrid has so many exceptional features, like most of the support for filtering, that it is very odd that it seems to almost lack support for what must be one of the most common tasks - filtering based on dropdowns.

When googling for this, I happend to stumble upon this blog entry, which I think really sums it ut very well, so instead of writing the same thing here all over again, I just refer just to http://www.tunaslut.com/telerik-radgrid-and-filtering-on-listtextfield-with-a-griddropdowncolumn/ and humly ask if there is still no easy way of doing this as of October 2010?

What is the recommended way of implementing simple drop down filtering? I've must have seen examples/code only on the Telerik site describing at lest 3-4 different ways of doing it (client side, serverside, custom class etc) and I am guessing that perhaps some of them are mainly vaild for older versions of the Telerik controls, lacking the features of the latest versions.

28 Answers, 1 is accepted

Sort by
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 06 Oct 2010, 03:01 PM
It seems to be down to either filtering clientside or serverside, using the technique described here:

http://www.telerik.com/help/aspnet-ajax/radgrid-filter-template.html

Even though I am a bit uncertain if the serverside example above does work 100%, since it seems to replace any current FilterExpression with one just caring about what i selected in dropdown (it replaces the FilterExpression rather than appending to it). If I am correct in this, where can we find proper source code that takes this into consideration (especially the fact that it might or might not be an AND required).

Any recommendations which one is to favour here, client side or server side? Will the client side version actually make trigger an AJAX call to the server, or will it only affect what is shown in the browser? 

0
Dimo
Telerik team
answered on 11 Oct 2010, 10:08 AM
Hi Patrik,

Filtering involves control rebinding, so it is never executed in the browser and always triggers a postback / AJAX request / call to a web service.

If you require the ability to set complex filter expressions with AND / OR operators, you can use RadFilter:

http://demos.telerik.com/aspnet-ajax/filter/examples/integration/defaultcs.aspx?product=grid


Regards,
Dimo
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 11 Oct 2010, 10:13 AM
Is there any recommened way of "exporting" the FilterExpression to a function in a Business Layer or similar? Also, any recommendations on if you should do all filtering in the RadGrid (which would probably require you to load ALL data into it) or try send the filterexpression to the database instead (using the above mentioned BL function and a DAL)?
0
Dimo
Telerik team
answered on 13 Oct 2010, 12:06 PM
Hi Patrik,

RadGrid may or may not load all the data and filter internally, depending on the datasource. If you use a DataSource control, which supports filtering (LinqDataSource, OpenAccessDataSource, EntityDataSource), the RadGrid will load only the filtered data.

You can retrieve the filter expressions like this:

RadFilter - use the ApplyExpressions event and iterate the expressions in e.ExpressionRoot. Alternatively, use a query provider and build an SQL-friendly filter string:

void RadFilter1_ApplyExpressions(object sender, RadFilterApplyExpressionsEventArgs e)
{
    RadFilterSqlQueryProvider provider = new RadFilterSqlQueryProvider();
    provider.ProcessGroup(e.ExpressionRoot);
    var r = provider.Result;
}


RadGrid - retrieve the filter expressions of the tableview(s):

http://www.telerik.com/help/aspnet-ajax/grdoperatewithfilterexpression.html

http://www.telerik.com/help/aspnet-ajax/telerik.web.ui-telerik.web.ui.gridtableview-filterexpression.html


Kind regards,
Dimo
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 13 Oct 2010, 09:47 PM
Well, thouse data sources might be good for quick & dirty development, but as soon as you want to make a real application with a business layer, it get's complicated. Seems like all we have is Microsoft ObjectDataSource, which hasn't received much work in the last couple of years it seems. We actually use the Extended Object Data Source (EODS) instead, which is a great extension of the ODS which I can really recommend. But I dont think it "supports filtering" in a way that can be used here, anyone knows?
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 14 Oct 2010, 06:16 PM
What exactly, from a technical point of view, does this mean "RadGrid may or may not load all the data and filter internally, depending on the datasource. If you use a DataSource control, which supports filtering (LinqDataSource, OpenAccessDataSource, EntityDataSource), the RadGrid will load only the filtered data."?

Or rather, what does the "support filtering" mean, will it work with any DataSource control that has built in support for filtering, and if so, how do you set it up? How does RadGrid (etc.) "evaluate" the DataSource control to determine if it supports filtering or not.

It seems like the EODS has much better support for filtering than the ODS, but what's the best way to use it with RadGrid?

More info about the EODS and filtering: http://www.manuelabadia.com/livedemo/extendedobjectdatasource/Filtering.aspx

Thanks!

0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 14 Oct 2010, 06:19 PM
Perhaps we should mention that we use Entity Framework, so we are not using DataTables/DataSets.
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 15 Oct 2010, 12:36 AM
We've done some futher digging into this and it seems like one way to do is it to hook up the selecting event of the ODS/EODS with something like this:

    protected void extendedObjectDataSourceTest_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
    {
        e.InputParameters["filterExpression"] = radGridTest.MasterTableView.FilterExpression;
    }

and then make sure the EnableLinqExpressions = true (seem to be default with .NET 4.0). Then you would get the filterExpression in the Business Layer and do the filering you need. But is this the best way to do it? Ideal would be if there was some automatic way of doing this, thus our questions on how the RadGrid does this with an EntityDataSource for example? Also, how would you go about preventing the RadGrid from trying to filter the data on its own?

BTW, am I correct to assume that with .NET 4.0, you dont need to bother about the "DynamicLinq" extensions any more? Or rather, that it is built in?

This is a quite good thread to look at also:
http://www.telerik.com/community/forums/aspnet-ajax/grid/filtering-with-linq-through-an-objectdatasource.aspx
0
Dimo
Telerik team
answered on 15 Oct 2010, 08:56 AM
Hello Patrik,

>> If you use a DataSource control, which supports filtering (LinqDataSource, OpenAccessDataSource, EntityDataSource), the RadGrid will load only the filtered data."?

Yes, because it will be passed from the datasource control.


>> what does the "support filtering" mean, will it work with any DataSource control that has built in support for filtering, and if so, how do you set it up?

Well, "support filtering" means that the datasource control is able to accept a filter expression from another control, such as RadGrid, and return filtered data. You don't have to set anything up in the case of LinqDataSource, etc. However, it seems that you have to add some code for this in your case.


>> But is this the best way to do it?

I am afraid we cannot provide definite advice on other third party controls, but from RadGrid's point of view - yes.


>> How would you go about preventing the RadGrid from trying to filter the data on its own?

You have to clear the RadGrid's filter expression before binding the control and add it back in PreRender, so that it is displayed correctly in the control's filter item.


>> Am I correct to assume that with .NET 4.0, you dont need to bother about the "DynamicLinq" extensions any more? Or rather, that it is built in?

Yes.


Sincerely yours,
Dimo
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 15 Oct 2010, 09:44 AM
Thanks for the quick answer!

We are currently testing this, and we do get the filterExpression sent down do our Business Layer function, but then we get som problems. Our BL test method looks like this:

        public List<Test> GetTest(int? TestID, int userID, string filterExpression)
        {
            using (var context = new HIBSEntities())
            {

                var theTest = context.Test
                    .Where(String.IsNullOrEmpty(filterExpression) ? "1 == 1" : filterExpression);

                return theTest.ToList();

            }
        }

And the filter expression generate by RadGrid looks kind like this:

filterExpression = "(iif(TestName == null, \"\", TestName).ToString().StartsWith(\"My\"))"

but then we get the following error:

'iif' cannot be resolved into a valid type or function. Near simple identifier, line 6, column 2.

I guess there might be an easy solution for this but so far we have not been able to find it, please help! :)
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 15 Oct 2010, 09:52 AM
My initial thought, is the IIF function really be meant to be sent down as a lambra expression or is it supposed to be evaluated before and then the output of that is to be sent down to the WHERE?
0
Dimo
Telerik team
answered on 20 Oct 2010, 03:30 PM
Hi Patrik,

So as mentioned earlier, RadGrid uses Dinamic LINQ. You can use the filter expressions directly the way you are trying to, only if you include the Dynamic Query Library to your project, which provides extension methods to the IQueryable interface.

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx


Regards,
Dimo
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 20 Oct 2010, 04:46 PM
Well, but you also wrote

>> Am I correct to assume that with .NET 4.0, you dont need to bother about the "DynamicLinq" extensions any more? Or rather, that it is built in?

Yes.


So please understand if I am a a bit confused here :)

Or is it so that it is now built in so that you dont have to download anything, but that we still need to add a reference so some System.Data.XXX or similar?
0
Dimo
Telerik team
answered on 21 Oct 2010, 08:31 AM
Patrik,

The idea of the Dynamic Query Library is to enable you to use string expressions (which also can be generated at runtime) for the Where method parameter, instead of for example lambda expressions, which cannot be generated at runtime.

RadGrid includes and uses the Dynamic Query Library, but it is not publicly exposed, so you need to include it yourself as well, if you want to use string filtering expressions for your own purposes. And yes, you need to add this to your code-behind:

using System.Linq.Dynamic;

Regards,
Dimo
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 27 Oct 2010, 12:48 PM
After counless hours of error searching, we finally decided to have a look more in detail of the LINQ generated by the RadGrid and notices that it was all wrong. Something we should perhaps have noticed before maybe, but that wasn't really our first assumption.

As mentioned before, the filterExpressions we get out of RadGrid looks like this:

filterExpression = "(iif(TestName == null, \"\", TestName).ToString().StartsWith(\"Test\"))"

but that is way of for use with Entity Framework, where the expressions should look like:

@"it.TestName LIKE ""Test%""";

According to this page http://www.telerik.com/help/aspnet-ajax/grdoperatewithfilterexpression.html it seems like the RadGrid tailors the generated LINQ depending on the DataSource, but we don't use one of those listed there, but an ObjectDataSource (really an ExtendedObjectDataSource).

How can we tell RadGrid to output LINQ that we can use, which in this case seem to be exactly the same as for the EntityDataSource (perhaps no suprise really)?

0
Dimo
Telerik team
answered on 27 Oct 2010, 01:28 PM
Hello Patrik,

Yes, RadGrid generates filtering LINQ expression depending on the datasource. I am aftraid there is no way to tell the control what kind of expressions to generate.

Another way of retrieving the RadGrid filter expressions is from the control's filter command event arguments. You may find this approach easier:

http://www.telerik.com/help/aspnet-ajax/grdoperatewithfilterexpression.html

Regards,
Dimo
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 27 Oct 2010, 02:50 PM
Thanks for the information.  As far as I know, the ObjectDataSource (and versions of it) are the only DataSource usable when you build a "real" application with a Business Layer, and the lack of this feature is a big disadvantage.

But how does the RadGrid work then with regards to this, as it obviously does generate some kind of LINQ, not just the right one (for our use). Does it use some kind of fallback if it can't identify the DataSource? How does the RadGrid identify each datasource? Is there really no way of tricking the RadGrid into generate the correct LINQ?

I looked at http://www.telerik.com/help/aspnet-ajax/grdoperatewithfilterexpression.html (same page as I refered to) but dont really see anything there that could be an alternative to this? Even if I extract "TestColumnName", "Contains" and "test", that wouldnt really be that useful to us (meaning, we would have to write the entire filter function ourselves that is). And it also does seem like it can only support one filter, ie not combining 2 different filters at the same time, is this correct?
0
Dimo
Telerik team
answered on 29 Oct 2010, 02:39 PM
Hello Patrik,

Here is a description of the RadGrid's logic when the control determines how to build its filter expressions:

1. If the datasource is known to support filtering natively (LinqDataSource, EntityDataSource and OpenAccessDataSource), the RadGrid builds the corresponding expressions for this datasource.

2. If the .NET framework version is 2.0 or EnableLinqExpressions="false" (property exists for RadGrid only in .NET 3.5+), RadGrid builds SQL-like expressions.

3. Otherwise, the control inspects the type of one data item from the datasource and according to this type, filter expressions are built in such a way, so that they can be passed on DynamicLinq extension methods. To be more particular, we differentiate between DataRowView, collection of value types, collection of any other types. Your case is the last one.


>> "And it also does seem like it can only support one filter, ie not combining 2 different filters at the same time, is this correct?"

I am not sure I understand the question. You can supply a more complex filter expression to the RadGrid control manually using the syntax from the help article you are referring to. It can't be shown in the control's UI (the filter row), however, but it can be applied - this is how RadFilter and RadGrid work together.

http://demos.telerik.com/aspnet-ajax/filter/examples/integration/defaultcs.aspx

Dimo
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 13 Jan 2011, 05:33 PM
This is kind of a cross post as both threads seem to deal with the same thing, and both should be interesting to anyone reading one of them (perhaps they should be merged). Anyway, here we go:

I am trying to understand if LINQ is actually generated or not with ObjectDataSource.

In this forum thread:

http://www.telerik.com/community/forums/aspnet-ajax/grid/filtering-with-linq-through-an-objectdatasource.aspx#1486944

you say that it is, but in the current thread, you say that it isn't or am I missing something?

We really need to access the LINQ expression of the RadGrid just as described in this thread by the origina poster, to send to to our Business Layer.

Please advice :)

0
Iana Tsolova
Telerik team
answered on 18 Jan 2011, 01:24 PM
Hi Patrik,

If you are binding your RadGrid through the NeedDataSource event, you can get the FilterExpression there. If you are using ObjectDataSource control to bind the grid, you should be able to get the FilterExpression in its Selecting event as my colleague Rosen illustrated in the project he attached in the other thread.

All the best,
Iana
the Telerik team
Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.
0
David Thompson
Top achievements
Rank 2
answered on 14 Jun 2011, 05:50 AM
If we use NeedDataSource, will the FilterExpression always be SQL-like? Is there any way to get a LINQ filter expression using NeedDataSource?
0
Iana Tsolova
Telerik team
answered on 14 Jun 2011, 08:27 AM
Hi David,

When RadGrid is bound though the NeedDataSource event, its FilterExpression uses Dynamic Linq, except you disable the Linq expressions (by setting the EnableLinqExpressions).

Greetings,
Iana
the Telerik team

Browse the vast support resources we have to jump start your development with RadControls for ASP.NET AJAX. See how to integrate our AJAX controls seamlessly in SharePoint 2007/2010 visiting our common SharePoint portal.

0
improwise
Top achievements
Rank 1
Iron
Iron
answered on 14 Jun 2011, 08:46 AM
What is really needed I believe is an option that forces the RadGrid to use LINQ or not for filterExpresion, as it's ability (inability to be honest) to determin this by itself doesn't really work. Since it can handle LINQ already, this should be a really small change to make and with a defaultvalue of false, it would not be a breaking change either. Telerik, please consider this.
0
Scott
Top achievements
Rank 1
answered on 16 Oct 2012, 04:19 PM
This seems to be an issue only with filtering on ObjectDataSource and more specifically with enterprise patterns such as business layer services and Entity Sql.  It surprises me there are still no elegant solutions to this issue.  I have been searching quite extensively for a cleaner solution and just haven't found one.  The existing solutions require some use of custom coding and that translates into me maintaining unnecessary code for a very long time.  I don't need any more technical debt, I need less.

I'm going to make some assumptions here but hopefully in the end this will all make sense and get some discussion going because we still need a better solution.  First some context, I started using the RadFilter control in a project recently and needed a way to manually build the expression tree into a query syntax my service layer would accept.  That turned out to be extremely easy using one of the query providers in the framework: RadFilterEntitySqlQueryProvider (see doc here).  The key idea I am getting at is the decoupling of RadFilter from having to determine my intent to use a certain query dialect.

When I began using the RadGrid's filter expression on a new project in the context of an ObjectDataSource.  I learned that RadGrid tries to determine my intent on a certain query dialect because of the "type" of data source I declared on the RadGrid.  That makes sense in most cases where the data source is "typed" for one query dialect, the LinqDataSource for example.  This seems like the opposite approach from the RadFilter, it couples itself closely with the data source and makes assumptions on intent.  Again that makes sense in most cases.  With ObjectDataSource, the query dialect needed could be anything and in the cases you see in this discussion thread, the business layer dictates that.

So this desire of mine to convert the filter expression into the right query dialect got me thinking about how RadFilter decouples itself from that responsibility.  I wanted to get at the RadGrid's internal filter expression tree, maybe through a well-published property like the RadFilter RootGroup property.  But as far as I can tell, there is no way to access the expression tree of a RadGrid filter.  Encapsulation of this implementation detail is probably a good thing so exposing that structure may not be the right solution.

However, if we can assume for a minute the RadGrid filter does hold a similar expression tree internally, why can't we as developers influence through some declared attribute the query dialect provider, thus removing that responsibility from the RadGrid?  Since this discussion is in the context of an ObjectDataSource it seems to make sense.

If you have made it this far and still understand what I am trying to say, I am grateful.  So here is the pitch.  I would like to see an attribute on the MasterTableView tag that will determine how the filter expression is written.  The following ASPX code snippet demonstrates the proposed attribute, FilterProvider.

<telerik:RadGrid ID="RadGrid1" AllowFilteringByColumn="true" DataSourceID="ObjectDataSource1" ... >
    <MasterTableView AllowFilteringByColumn="true" FilterProvider="RadFilterEntitySqlQueryProvider" ... >
        ...
    </MasterTableView>
</telerik:RadGrid>

If the ObjectDataSource1 is declared to use a business layer service expecting Entity Sql, the following ASPX code snippet should just work with the above RadGrid1 declaration without any additional code.

<asp:ObjectDataSource ID="ObjectDataSource1" ... >
    <SelectParameters>
        <asp:ControlParameter Name="filter" ControlID="RadGrid1" PropertyName="MasterTableView.FilterExpression" ... />
    </SelectParameters>
</asp:ObjectDataSource>

I hope this makes sense and someone still watches this thread because we need a better solution.

Sincerely,
Scott
0
Tsvetoslav
Telerik team
answered on 18 Oct 2012, 12:44 PM
Hi Scott,

Thanks for the extensive and insightful post. Indeed, you are absolutely correct in you comments but unfortunately, RadGrid does not have such a mechanism of constructing its filter expressions (and hence a property like the one suggested is not possible) and it is not a trivial task to refactor the grid's filtering along these lines. I have forwarded your idea to the development team of RadGrid and have received a notification that a research task on it will be provided for in the new quarter planning. What will be the outcome is still to be seen. Please, follow our roadmaps and share your ideas at http://ajaxportal.telerik.com

Regards, Tsvetoslav
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Scott
Top achievements
Rank 1
answered on 18 Oct 2012, 12:59 PM
Hi Tsvetoslav,

Thank you very much for the response and thank you for considering my idea.  I will follow the ASP.NET AJAX road map and blog feed--looking forward to seeing something in the future!

Sincerely,
Scott
0
Mick
Top achievements
Rank 1
answered on 25 Oct 2012, 02:31 AM
Here's my suggestion....

Firstly... when in doubt RadGrid should default to Entity SQL.

I think the basic problem is Telerik has assumed that people will always want to use designer DataSources, which is a completely false assumption.  Designer DataSources are good for demos and not much else.  

Most of Telerik's paying customers will want to construct their own business layers and do not want a tight coupling between the GUI and a database.  Data displayed in a grid might be a combination of a number of different sources, some of which may not even be in the database.

So following the KISS approach why not just provide properties for all the types of expressions...

ie.
Instead of just FilterExpression

EntitySQLFilterExpression
LinqSQLFilterExpression
SQLFilterExpression

etc...

And rather than providing a single property on the MasterTableView, provide these properties on the MasterTableView and on the columns as well (providing an expression for just the column).

This will give people wanting to do manual databinding the most amount of flexiblity.
0
Tsvetoslav
Telerik team
answered on 25 Oct 2012, 02:59 PM
Hello Patrik,

I see your point and would just add that those expressions should rather be providers exposing richer functionality - but the developers will be looking into that.

Concerning the designer datasources, RadGrid does not make such an assumption and we are aware that most of our customers use the NeedDataSource event - its purpose was to meet exactly what you are describing as the most frequent data layer requirement on the developer's side.


Greetings, Tsvetoslav
the Telerik team
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Tags
Grid
Asked by
improwise
Top achievements
Rank 1
Iron
Iron
Answers by
improwise
Top achievements
Rank 1
Iron
Iron
Dimo
Telerik team
Iana Tsolova
Telerik team
David Thompson
Top achievements
Rank 2
Scott
Top achievements
Rank 1
Tsvetoslav
Telerik team
Mick
Top achievements
Rank 1
Share this question
or