locked
Filter one datasource column with multiple values RRS feed

  • Question

  • User-1425666379 posted

    I've done some poking around, but didn't see an answer on this.

    I have a GridDataSource that I want to filter when the page loads. But I want to filter a column by multiple values, like using a "contains" or an "in".

    The column is an integer value. I tried this: 

    protected void GridDataSource_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        if (someCondition)
        {
            using (MyDBContext db = new MyDBContext ())
            {
                e.Result = db.Employees.Where(emp => _currentUser.ShiftIds.Contains(emp.ShiftID));        }
            }
    }
    But that doesn't work. When I look at e.Result, it shows filtered data, like I want it, but when the page renders it shows all the data, not the filtered data. 
     I wanted to leverage the current code that I have in page_init that already filters another column on a single value, but I'm not sure how I'd do that either.
     
    GridDataSource.WhereParameters.Add(new Parameter("it.LocationId", TypeCode.Int32, _locationId));
    
     
    Tuesday, March 10, 2009 8:44 PM

Answers

  • User-330204900 posted

    What you need to look at first of all is the ForeignKey_MultiSelect.ascx filter. And I recommend downloading the source code.

    This is sort of the thing you need r multi select: 

    var dynamicParameters = new AndExpressionParameter() { Name = "DynamicParameters" };
    var iep = new InExpressionParameter()
        {
            Name = "Customer.CustomerID",//Column, ForeignKeyTable PK
            ValueType = TypeCode.String,
            ValueList = "ALFKI,ANATR,ANTON,AROUT"
        };
    dynamicParameters.Parameters.Add(iep);
    GridDataSource.WherePredicateParameters.Add(dynamicParameters);
    GridDataSource.WherePredicateParameters.SetDirty();
     and I think just the name would be required for a straigh text filter.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 12, 2009 4:49 AM

All replies

  • User-330204900 posted

    Have a look at this article of mine Limit Data Displayed By User you need to use the GridDataSource.WhereParameters to filter you data of you may want to look at the Dynamic Data Filtering (by Josh Heyes) project on codeplex and my article here Dynamic Data Filtering – Installation which implements some extra where capabilities most of which are also available from Dyanmic Data Preview 2 also on codeplex.

    Wednesday, March 11, 2009 9:47 AM
  • User323304077 posted

    Duplicated posting... Ooops!

    Wednesday, March 11, 2009 1:33 PM
  • User323304077 posted

    Steve - I was thinking that this would do the trick for something I'm doing as well, but as it turns out, the WhereParameters only appears to allow you to specify a single value like username = 'my name'...  How would you go about doing this for making sure that username is within a list of usernames?  If it were a straight LINQ query, that wouldn't be a big deal because you can use contains.  But how do you specify a contains type of parameter directly to the data source?

    Wednesday, March 11, 2009 1:34 PM
  • User660823006 posted

    Yes, this is a limitation of the existing LinqDataSource. One solution you have today is to override the OnSelecting event where you can actually use REAL LINQ to shape the query. One of the changes we have in our Dynamic Data Preview release on codeplex (www.codeplex.com/aspnet) is the new QueryExtender which adds the exact capability to LinqDataSource and in the future EntityDataSource. This will be in .NET 4.0.

     

    Wednesday, March 11, 2009 1:42 PM
  • User-1425666379 posted

    Understood. But then, what is "InExpressionParameter" and what are the Attributes I see for setting filtermode == multiselect and such? It looks like capabilities are there from an API perspective.

    Ok, well we tried using OnSelecting to implement Linq, but as I noted in my original post, the results, although they showed as filtered in the Result object while in debug, did not render to the page filtered.

     Then we tried OnSelected, but the e.Result is readOnly there.

    Wednesday, March 11, 2009 1:55 PM
  • User-330204900 posted

    Steve - I was thinking that this would do the trick for something I'm doing as well, but as it turns out, the WhereParameters only appears to allow you to specify a single value like username = 'my name'...  How would you go about doing this for making sure that username is within a list of usernames?  If it were a straight LINQ query, that wouldn't be a big deal because you can use contains.  But how do you specify a contains type of parameter directly to the data source?

    Hi RussSolberg, you should have a look at Dynamic Data Filtering (by Josh Heyes) project on codeplex and my article here Dynamic Data Filtering – Installation this offers many of the features that are in the Dyanmic Data Preview 2 also on codeplex. In particular there is a featue that allows you to simulate the WHERE IN in T-SQL.

    Wednesday, March 11, 2009 1:59 PM
  • User323304077 posted

     Just wanted to throw the code sample out there for the GridDataSource_Selecting event.  e.Result is null before it is set with the LINQ expression. 

     

            protected void GridDataSource_Selecting(object sender, LinqDataSourceSelectEventArgs e)
            {
                List<int> myShifts = new List<int>();
                myShifts.Add(1);
                myShifts.Add(2);
    
                using (WorkforceDataContext cntx = new WorkforceDataContext())
                {
                    e.Result = from emp in cntx.Employees 
                            where myShifts.OfType<int>().Contains(emp.ShiftID)
                            select emp;
                }
            }
      
    Wednesday, March 11, 2009 2:01 PM
  • User-1425666379 posted

    I see your blog on that, but although it mentions  

    [Filter(FilterMode=FilterControlMode.MultilSelect)]
    

     I don't know what to do with it after that. I'm already using Josh's coolness, but he doesn't really go into how to do this either.

    Wednesday, March 11, 2009 2:51 PM
  • User-330204900 posted

    I'll try an post a sample tomorrow.

    Wednesday, March 11, 2009 3:02 PM
  • User-1425666379 posted

    That would be nice if you get a chance to do that. Thanks Steve.

    Wednesday, March 11, 2009 3:25 PM
  • User-330204900 posted

    What you need to look at first of all is the ForeignKey_MultiSelect.ascx filter. And I recommend downloading the source code.

    This is sort of the thing you need r multi select: 

    var dynamicParameters = new AndExpressionParameter() { Name = "DynamicParameters" };
    var iep = new InExpressionParameter()
        {
            Name = "Customer.CustomerID",//Column, ForeignKeyTable PK
            ValueType = TypeCode.String,
            ValueList = "ALFKI,ANATR,ANTON,AROUT"
        };
    dynamicParameters.Parameters.Add(iep);
    GridDataSource.WherePredicateParameters.Add(dynamicParameters);
    GridDataSource.WherePredicateParameters.SetDirty();
     and I think just the name would be required for a straigh text filter.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 12, 2009 4:49 AM
  • User-1425666379 posted

    So is ForeignKey_MultiSelect from Josh's Dynamic Data Filtering project?

    Thursday, March 12, 2009 1:10 PM
  • User-1425666379 posted

    Nice. I tried using that at one point but was confused by there being no constructor on the objects.

    That worked. Thanks for your help Steve!

    Thursday, March 12, 2009 3:02 PM
  • User-330204900 posted

    your welcome. [:D]

    Thursday, March 12, 2009 7:16 PM