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

Filtering with linq through an ObjectDataSource

26 Answers 1018 Views

Grid

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

Marcus asked on 04 Jan 2009, 10:04 AM

Hello,

I am using a custom data object with a select-method that collects data from different tables, adding data from cached object collections etc. This is done with linq. It's working great with one exception: filtering data. When filtering data I cant get the filtering expression in a linq-syntax.

My radgrid has EnableLinqExpressions set to true, and my ObjectDataSource looks like below:

    
                <asp:ObjectDataSource ID="ObjectDataSource1" TypeName="MyNameSpace.DataAccessLayer.UserDataObject" 
        
                    SelectMethod="Select" SelectCountMethod="SelectCount" EnablePaging="True" runat="server" 
        
                    MaximumRowsParameterName="maximumRow" OldValuesParameterFormatString="original_{0}" 
        
                    OnSelecting="ObjectDataSource1_Selecting" OnSelected="ObjectDataSource1_Selected"> 
        
                    <SelectParameters> 
        
                        <asp:Parameter Name="shopID" Type="Int32" /> 
        
                        <asp:Parameter Name="filter" Type="String" /> 
        
                        <asp:Parameter Name="sort" Type="Object" /> 
        
                        <asp:Parameter Name="startRowIndex" Type="Int32" /> 
        
                        <asp:Parameter Name="maximumRow" Type="Int32" /> 
        
                    </SelectParameters> 
        
                </asp:ObjectDataSource>

I set the parameters in the selecting-event of the ObjectDataSource:

    
                protected void ObjectDataSource1_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) 
        
                { 
        
                    e.InputParameters["shopID"] = thisShop.ShopID; 
        
                    e.InputParameters["filter"] = RadGrid1.MasterTableView.FilterExpression; 
        
                    e.InputParameters["sort"] = RadGrid1.MasterTableView.SortExpressions.GetSortString(); 
        
                    e.InputParameters["startRowIndex"] = RadGrid1.CurrentPageIndex*RadGrid1.PageSize; 
        
                    e.InputParameters["maximumRow"] = RadGrid1.PageSize; 
        
                }

How can I get the filterexpression to be in a linq-friendly format so that I can use it in my linq-query?

26 Answers, 1 is accepted

answered on 05 Jan 2009, 09:53 AM

WebSite.zip

Hi Marcus,

Generally when you set EnableLinqExpressions the grid will generate LINQ expressions - I have attached small demo to illustrate you this.

Kind regards,
Vlad
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

answered on 05 Jan 2009, 12:48 PM

Thank you, but I realize now that I wasn't clear enough expressing my problem.

In my dataobject's selectmethod - I use a linqquery with lots of inner and outer joins, calculated columns etc.

From that query I want to add a where("<queryexpression goes here>"). Sort of like your toDynamicLinq but server side.

This is my approach trying to filter and sort:

    
            var x = result. 
        
                            Where(String.IsNullOrEmpty(filter) ? "1 == 1" : filter). 
        
                            OrderBy(String.IsNullOrEmpty(sort) ? "LastName ASC" : sort). 
        
                            Skip(startRowIndex). 
        
                            Take(maximumRow). 
        
                            CopyToDataTable(); 
        
            return x;

sorry for being unclear initially and hopefully you now understand my problem in more detail

//Marcus

Accepted

answered on 17 Mar 2010, 02:12 PM

radgridobjectdatasourcecustomlinqfiltering.zip

Hi Bernhard,

I have attached a small sample which demonstrates how to use RadGrid's filter/sort expressions to filter, sort and page through ObjectDataSource.

All the best,
Rosen
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.

answered on 13 Jan 2011, 05:30 PM

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/radgrid-filtering-with-dropdowns.aspx

you say that it isn't, but in the current thread, you say that it is infact 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 :)

answered on 19 Jan 2011, 02:25 AM

We have almost found a way to get this to work, which I feel we should share. After looking through the code for Dynamic LINQ, we noticed that there is not where() statement taking only one parameter, so something like this:

.Where(filterExpression);

Won't actually use Dynamic LINQ at all but probably one of the build in wheres in Entity Framework 4.

However, just changing to:

.Where(filterExpression, "");

Will instead trigger the call to Where() in DynamicLINQ (Dynamic.cs), which works.....almost.

The almost comes from the fact that the LINQ generated by RadGrid isn´t fully compatible with Linq-to-entities. It will generate a filterExpression like:

"(iif(TestColumn== null, \"\", TestColumn).ToString().ToUpper().Contains(\"te\".ToUpper()))"

which will cause this:

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression

Simply removing the ".ToString()" part form the filterExpression will make everything work, so the obvious question is, must it really be in there to begin with in the Linq generated by RadGrid?

(Please see here for more information etc. http://www.drowningintechnicaldebt.com/RoyAshbrook/archive/2010/06/04/some-ef-weirdness-linq-to-entities-does-not-recognize-the.aspx)

answered on 19 Jan 2011, 02:42 AM

Of course, as the ToString() doesnt seem to be needed (at least not in this case), it wouldn't be to hard to create a work around to just drop all ".ToString()" from the generate filterExpressions, but that doesnt seem like the nicest way to deal with it :)

Anyway, if someone wants to use it anyway, here is a full working .Where() that has been somewhat verified to work with complex expressions as well (ANDs etc.)

.Where(String.IsNullOrEmpty(filterExpression) ? "true" : filterExpression.Replace(".ToString()", String.Empty), "");

answered on 19 Jan 2011, 11:17 AM

One thing we havn't been able to figure out in all this, if our conclusions are true, that you need to specify a .Where(string, string) in order to actually use the Dynamic LINQ, then the example code provided in this thread by Telerik wouldn't use Dynamic LINQ at all, as it only sends in one parameter:

                IQueryable<Customer> customers = db.Customers;
                if (!string.IsNullOrEmpty(filterExpressions))
                    customers = customers.Where(filterExpressions);

We havn't actually tried to run this example code as it is a bit to far from what we want to do, but it would still be nice to get some input on this, as I suspect we might have missed something imporant here?

answered on 19 Jan 2011, 01:00 PM

Hello Patrik,

The filter expression you posted is indeed a Dynamic Linq expression. Note that RadGrid uses the approach illustrated here to build its Linq expressions.
Furthermore, in the sample in this thread, you can see that passing the grid filter expression as a single parameter of the Where() method works as expected. Therefore I assume there is something different in your case causing the mentioned issues and errors. To find them out, I would suggest that you send us a runnable sample of your scenario in the support ticket you have opened so I can forward it for further research.

Regards,
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.

answered on 19 Jan 2011, 01:19 PM

Thank you for your quick response.

What I was thinking, did you run the sample also with complex filters, not just something like "(MyID == 3)", which could probably work in a non Dynamic LINQ as well? Actually, the only reason we ask, is because there is not Where() in the System.Linq.Dynamic that takes only one parameter what we can see:

        public static IQueryable<T> Where<T>(this IQueryable<T> source, string predicate, params object[] values) {
            return (IQueryable<T>)Where((IQueryable)source, predicate, values);
        }

        public static IQueryable Where(this IQueryable source, string predicate, params object[] values) {
            if (source == null) throw new ArgumentNullException("source");
            if (predicate == null) throw new ArgumentNullException("predicate");
            LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, typeof(bool), predicate, values);
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Where",
                    new Type[] { source.ElementType },
                    source.Expression, Expression.Quote(lambda)));
        }

As mentioned before, we have gotten this to work now, so this is more in the spirit of helping others who may face the same problems as we do/did.

Also, it would be nice if you could look into the possibility of not including ToString() when not necessary, as this does not work with Linq-to-entities (which of course isn't your fault but Microsofts).

answered on 14 Jun 2011, 08:27 AM

RadGridFilterExpression.zip

Hi David,

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

I prepared a simple web site where you can easily see how it works. Give it a try and let me know what differs in your case and if I missed something out.

Best wishes,
Iana
the Telerik team

answered on 22 Apr 2015, 10:37 PM

I am trying to update some old code that uses the approach outlined here. I have successfully updated the Telerik controls etc. but now the old code is obsolete. Is there any sort of guideline to show how this should NOW be done? Thank you.

Here are the first few lines of the old approach. The IQueryable<T> is not defined anymore.

using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Reflection.Emit;
using System.Threading;
public static class DynamicQueryable
{
public static IQueryable<T> Where<T>(this IQueryable<T> source, string predicate, params object[] values)