none
condition on all item of a array in linq RRS feed

  • Question

  • hello

    i have a string array and a iqueryable<T> object

    now i want write a query that filter iqueryable object same:

    TSQL

    "where NameField like'%" + MyArray[0]  + "%' Or NameField like'%" + MyArray[1]  + "%' Or NameField like'%" + MyArray[2]  + "%' Or .....

    Now i write this in linq

    string[] arrFilter = {"Ya","Ba"};

    IQueryable < MyStruct > Query.Where(Row => arrFilter.Any(str => Row.Name.ToString().Contains(str)));

     

    this not work properly

    and raise:

    local sequence cannot be used in linq to sql implementation of query operators except the contains

    Thanks

    IFA

    Sunday, June 20, 2010 10:51 AM

Answers

  • I think your best bet is to use the technique described in this EF tip in Alex James' blog:
    http://blogs.msdn.com/b/alexj/archive/2009/03/26/tip-8-writing-where-in-style-queries-using-linq-to-entities.aspx

    (although that article is for EF, the workaround presented in it can be tweaked to generate the kind of query you're looking for here, and then used with L2S)

    After tweaking it to call the string.Contains method (equivalent to SQL LIKE '%something%') instead of string.Equals, the "or builder" becomes roughly:

    public static Expression<Func<TElement, bool>> BuildContainsOrExpression<TElement, TValue>(
     Expression<Func<TElement, TValue>> valueSelector,
     IEnumerable<TValue> values
     )
    {
     if (null == valueSelector)
     throw new ArgumentNullException("valueSelector");
    
     if (null == values)
     throw new ArgumentNullException("values");
    
     ParameterExpression p = valueSelector.Parameters.Single();
    
     if (!values.Any())
     return e => false;
    
     System.Reflection.MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) }); 
     var equals = values.Select(value =>
     (Expression)Expression.Call(
      valueSelector.Body,
      method,
      Expression.Constant(
       value,
       typeof(TValue)
      )
     )
     );
    
     var body = equals.Aggregate<Expression>(
      (accumulate, equal) => Expression.Or(accumulate, equal)
     );
    
     return Expression.Lambda<Func<TElement, bool>>(body, p);
    }

    ...and your query would then be something like:

    string[] arrfilter = new string[] {"Ya", "Ba"};
    IQueryable<Foo> someQuery = someQuery.Where(BuildContainsOrExpression<Foo, string>(r => r.Name, arrFilter));

    ...resulting in a "WHERE ([t0].[Name] LIKE @p0) OR ([t0].[Name] LIKE @p1) ...etc..." where clause...

     


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Monday, June 21, 2010 7:17 AM
    Answerer
  • Well if that was what he wanted then I still think it will be easier to use the contains twice or regex if a pattern is what he wanted to extract

    IQueryable <Foo>MyQuery;

    string[] arrfilter={"Ya", "Ba"};

    var myfilteredq= expr from Myquery where (from filter in arrfilter where filter.Contains(expr)).Any() select expr;

    Or something to that effect (I don't have VS with me so I can't check how exactly this will work but the idea is sound)

    Regards

    Tuesday, June 22, 2010 12:24 PM

All replies

  • I think your best bet is to use the technique described in this EF tip in Alex James' blog:
    http://blogs.msdn.com/b/alexj/archive/2009/03/26/tip-8-writing-where-in-style-queries-using-linq-to-entities.aspx

    (although that article is for EF, the workaround presented in it can be tweaked to generate the kind of query you're looking for here, and then used with L2S)

    After tweaking it to call the string.Contains method (equivalent to SQL LIKE '%something%') instead of string.Equals, the "or builder" becomes roughly:

    public static Expression<Func<TElement, bool>> BuildContainsOrExpression<TElement, TValue>(
     Expression<Func<TElement, TValue>> valueSelector,
     IEnumerable<TValue> values
     )
    {
     if (null == valueSelector)
     throw new ArgumentNullException("valueSelector");
    
     if (null == values)
     throw new ArgumentNullException("values");
    
     ParameterExpression p = valueSelector.Parameters.Single();
    
     if (!values.Any())
     return e => false;
    
     System.Reflection.MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) }); 
     var equals = values.Select(value =>
     (Expression)Expression.Call(
      valueSelector.Body,
      method,
      Expression.Constant(
       value,
       typeof(TValue)
      )
     )
     );
    
     var body = equals.Aggregate<Expression>(
      (accumulate, equal) => Expression.Or(accumulate, equal)
     );
    
     return Expression.Lambda<Func<TElement, bool>>(body, p);
    }

    ...and your query would then be something like:

    string[] arrfilter = new string[] {"Ya", "Ba"};
    IQueryable<Foo> someQuery = someQuery.Where(BuildContainsOrExpression<Foo, string>(r => r.Name, arrFilter));

    ...resulting in a "WHERE ([t0].[Name] LIKE @p0) OR ([t0].[Name] LIKE @p1) ...etc..." where clause...

     


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Monday, June 21, 2010 7:17 AM
    Answerer
  • Yes, that will work but for the simple query he has perhaps this is simpler...

    IQueryable <Foo>MyQuery;

    string[] arrfilter={"Ya", "Ba"};

    var myfilteredq= expr from Myquery where arrfilter.Contains(expr.Name) select expr;

    Regards

    Monday, June 21, 2010 6:58 PM
  • Yes, that will work but for the simple query he has perhaps this is simpler...

    IQueryable <Foo>MyQuery;

    string[] arrfilter={"Ya", "Ba"};

    var myfilteredq= expr from Myquery where arrfilter.Contains(expr.Name) select expr;

    Regards


    That will result in a "where Name in (@p0, @p1)" with @p0='ya' and @p1='ba' which is the best way to match the entire string in each array element.

    However, based on the T-SQL where clause provided in the question, my interpretation was that the thread starter want to do wildcard matching where the db field contain a portion of the strings in the array: "where Name like @p0 or name like @p1" with @p0='%ya%' and @p1='%ba%'. Or in other words, using both IEnumerable<T>.Contains and s.Contains together...

     


    Kristofer - Huagati Systems Co., Ltd.
    Cool tools for Linq-to-SQL and Entity Framework:
    huagati.com/dbmltools (add-in with new features for the L2S and EF designers in VS2008 and VS2010)
    huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)
    Monday, June 21, 2010 11:34 PM
    Answerer
  • Well if that was what he wanted then I still think it will be easier to use the contains twice or regex if a pattern is what he wanted to extract

    IQueryable <Foo>MyQuery;

    string[] arrfilter={"Ya", "Ba"};

    var myfilteredq= expr from Myquery where (from filter in arrfilter where filter.Contains(expr)).Any() select expr;

    Or something to that effect (I don't have VS with me so I can't check how exactly this will work but the idea is sound)

    Regards

    Tuesday, June 22, 2010 12:24 PM
  • var myfilteredq= expr from Myquery where (from filter in arrfilter where filter.Contains(expr)).Any() select expr;

     


    Yep, I agree.

    FYI: It would probably result in the same "NotSupportedException: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator." exception mentioned in the original question but that's a different story for a different thread. :)

    Wednesday, June 23, 2010 5:23 AM
    Answerer
  • FYI: It would probably result in the same "NotSupportedException: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator." exception mentioned in the original question but that's a different story for a different thread. :)


    Hmm, perhaps, I have not tryed, as another way then you simply could make then Enumerables, but that would be not great perfomancy-wise and not a linq solution.  As you said a great theme for discussion. :)

    PS: I'm having trouble posting this, if duplicated I'll delete one

    Wednesday, June 23, 2010 12:08 PM
  • Joe Albahari's predicate builder is perfect for these kind of problems.

    http://www.albahari.com/nutshell/predicatebuilder.aspx

    Wednesday, June 23, 2010 4:45 PM