Filtering with linq through an ObjectDataSource

27 posts, 1 answers
  1. Marcus
    Marcus avatar
    2 posts
    Member since:
    Apr 2008

    Posted 04 Jan 2009 Link to this post

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

    Posted 05 Jan 2009 Link to this post

    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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Marcus
    Marcus avatar
    2 posts
    Member since:
    Apr 2008

    Posted 05 Jan 2009 Link to this post

    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


  5. Rosen
    Admin
    Rosen avatar
    3237 posts

    Posted 08 Jan 2009 Link to this post

    Hi Marcus,

    I'm afraid that I'm not sure what exactly you are trying to accomplish. As my colleague Vlad pointed out, when RadGrid's EnableLinqExpressions property is set to true, RadGrid will return the filter expression as a dynamic LINQ expression.
    It will be highly appreciated if you can send us more details about your scenario and implementation, thus we will be able to gather more details and provide you with more to-the-point answer/solution.

    Best regards,
    Rosen
    the Telerik team

    Check out Telerik Trainer, the state of the art learning tool for Telerik products.
  6. Henrik
    Henrik avatar
    40 posts
    Member since:
    Jan 2009

    Posted 10 Mar 2009 Link to this post

    Hi,
    I would like to know if something like this is possible:
    1 IList<Someobject>.Where(grid.MasterTableView.FilterExpression) 
    In other words, is it possible to use the FilterExpression property with LINQ when the grid has EnableLinqExpressions="true"?
  7. Rosen
    Admin
    Rosen avatar
    3237 posts

    Posted 11 Mar 2009 Link to this post

    Hello Henrik,

    Indeed you can use RadGrid's filter expression (if LinqExpressions are enabled) in such way, however you should import DynamicLinq library.

    Kind regards,
    Rosen
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  8. Answer
    Henrik
    Henrik avatar
    40 posts
    Member since:
    Jan 2009

    Posted 12 Mar 2009 Link to this post

    Hi Rosen,

    Thanks a lot for the answer. I hope I'll have time to try it out later today.
    You can mark the reply as answer for my part.

    Thanks again,

    /Henrik
  9. Bernhard
    Bernhard avatar
    8 posts
    Member since:
    Apr 2009

    Posted 16 Mar 2010 Link to this post

    Hello Henrik

    I have the same question / idea. Have you figured out how to do it with Dynamic Linq?

    Thanks & Regards
    Bernhard
  10. Rosen
    Admin
    Rosen avatar
    3237 posts

    Posted 17 Mar 2010 Link to this post

    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.
  11. Bernhard
    Bernhard avatar
    8 posts
    Member since:
    Apr 2009

    Posted 17 Mar 2010 Link to this post

    Hi Rosen,

    thanks for your example. Could you please explain me shortly: what are the steps to get the example running in visual studio 2008?

    Regards
    Bernhard
  12. Rosen
    Admin
    Rosen avatar
    3237 posts

    Posted 17 Mar 2010 Link to this post

    Hi Bernhard,

    You should just add reference to the Telerik.Web.UI .NET 3.5 assemblies and modify the LinqToSql context's connection string to point to a local instance of Northwind database.

    Kind regards,
    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.
  13. Patrik Johansson
    Patrik Johansson avatar
    270 posts
    Member since:
    May 2010

    Posted 13 Jan 2011 Link to this post

    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 :)

  14. Iana Tsolova
    Admin
    Iana Tsolova avatar
    3388 posts

    Posted 18 Jan 2011 Link to this post

    Hello Patrik,

    Could you please confirm you have tried the sample provided by my colleague Rosen?
    I assume it should work for you.

    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.
  15. Patrik Johansson
    Patrik Johansson avatar
    270 posts
    Member since:
    May 2010

    Posted 18 Jan 2011 Link to this post

    Well, I havn't as it was not complete and did not seem to include the necessary DLLs and Visual Studio projects (a bit short on time). We have however tried it in our own project, with Linq enabled for the RadGrid and running under .NET 4.0 and it produces "linq" that looks like this:

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

    As far as I know, this isn't valid LINQ, or am I wrong?


  16. Patrik Johansson
    Patrik Johansson avatar
    270 posts
    Member since:
    May 2010

    Posted 18 Jan 2011 Link to this post

    Futher investigation shows that the jury is still out regarding if it is valid LINQ or not, seems like it could be a valid Dynamic LINQ expression, but we get "'iif' cannot be resolved into a valid type or function" when we try to send it to where(). Will investigate further but would apprechiate suggestions and comments!
  17. Patrik Johansson
    Patrik Johansson avatar
    270 posts
    Member since:
    May 2010

    Posted 18 Jan 2011 Link to this post

    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)

  18. Patrik Johansson
    Patrik Johansson avatar
    270 posts
    Member since:
    May 2010

    Posted 18 Jan 2011 Link to this post

    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), "");

  19. Patrik Johansson
    Patrik Johansson avatar
    270 posts
    Member since:
    May 2010

    Posted 19 Jan 2011 Link to this post

    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?
  20. Iana Tsolova
    Admin
    Iana Tsolova avatar
    3388 posts

    Posted 19 Jan 2011 Link to this post

    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.
  21. Patrik Johansson
    Patrik Johansson avatar
    270 posts
    Member since:
    May 2010

    Posted 19 Jan 2011 Link to this post

    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).
  22. Iana Tsolova
    Admin
    Iana Tsolova avatar
    3388 posts

    Posted 24 Jan 2011 Link to this post

    Hi Patrik,

    I will try to make it clear. RadGrid builds Dynamic Linq expression but for Linq to SQL. If you need RadGrid to build Dynamic Linq expression for Linq to Entity, you ought to bind it to EntityDataSource or through the NeedDataSource. Otherwise, you will need to build the Linq-to-Entity Dynamic Linq expressions on your own as you already found.

    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.
  23. Patrik Johansson
    Patrik Johansson avatar
    270 posts
    Member since:
    May 2010

    Posted 25 Jan 2011 Link to this post

    Well, I can understand this as so far few developers are using Linq-to-entities, although it is probably increasing daily as this is now Microsofts prefered way of doing things, and what they push for. EntityDataSource is great for smaller rapid applications, but is not an option for a larger n-tier business applications.

  24. David Thompson
    David Thompson avatar
    29 posts
    Member since:
    Feb 2010

    Posted 14 Jun 2011 Link to this post

    When we use EnableLinqExpressions="True" and NeedsDataSource (rather than a DataSourceID), the FilterExpression is always a SQL-like expression rather than a LINQ expression. Does this work with NeedsDataSource? We're trying to set the filter expression in NeedsDataSource.
  25. Iana Tsolova
    Admin
    Iana Tsolova avatar
    3388 posts

    Posted 14 Jun 2011 Link to this post

    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

    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.

  26. steve
    steve avatar
    8 posts
    Member since:
    Jul 2012

    Posted 17 Sep 2012 Link to this post

    Hi Iana,

    in your example: is all the event handling in the code behind necessary. It seems to be working with just another ControlParameter of the ObjectDataSource's SelectMethod. In code:

    <asp:PresenterObjectDataSource ...some more attributes... >
        <SelectParameters>
            <asp:ControlParameter ControlID="RadGrid" PropertyName="MasterTableView.FilterExpression" Name="filter" />
        </SelectParameters>
    </asp:PresenterObjectDataSource>

    Is there any drawback of such approach that i am not aware of?

    Steves
  27. Allen
    Allen avatar
    50 posts
    Member since:
    Mar 2011

    Posted 22 Apr 2015 Link to this post

    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)

  28. Angel Petrov
    Admin
    Angel Petrov avatar
    1007 posts

    Posted 23 Apr 2015 Link to this post

    Hi,

    I have already provided an answer regarding this matter in the official support ticket you have opened. In order to avoid duplicate posts I suggest that we continue our conversation there. Once we are able to answer all your queries you can share your findings with the community thus helping other developers overwhelm such type of issues.

    Regards,
    Angel Petrov
    Telerik
     

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

     
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017