Translating this query in LINQ ? (list of like)

I would like to translate this query in LINQ ... it is very easy to construct if we do it in pure SQL but in dynamically created LINQ query (building a search query based on user input) it's a whole new story.

SELECT * FROM MyTable 
WHERE 1=1
  AND Column2 IN (1,2,3)
  AND ( Col开发者_JS百科umn1 LIKE '%a%' OR Column1 LIKE '%b%' )

Now to try to construct this we tried it this way :

if(myOjb.Column2Collection != null)
    query = query.where(f => f.Column2.Contains(myOjb.Column2Collection));

if(myObj.Column1Collection != null)
{
    // tough part here ?
    //query = query.Where(); ...
}

So what would be the best aproach to this normally ?

Note that I am aware of the SqlMethod.Like, tho I can't figure a way to implement it here ...

---

To get the Like.. Or Like.. etc, I think you have to write your own extension to build the expression tree for you. I wanted to do this a while ago and ended up finding a different article here on StackOverflow: Linq to Sql any keyword search query

From there, I think you would write this:

string[] terms = new string[] {"a", "b"}
query = query.LikeAny(table => table.Column1, terms)

BTW, you can also change the code on the linked page to do AND rather than OR by changing

var body = conditions.Aggregate((acc, c) => Expression.Or(acc, c));

to

var body = conditions.Aggregate((acc, c) => Expression.And(acc, c));

which was what I wanted at the time, calling it LikeAll

---

Try something like this:

var colums2 = { 1, 2, 3 };
var result = (from o in myOjb
              where columns2.Any(co2 => co2 == o.Column2)
              && Column1.Contains(column2valueA)
              || Column1.Contains(column2valueB)
              select o);

Hope can help

---

var sc = StringComparison.OrdinalIgnoreCase;
var col2Values = new int[] { 1, 2, 3 };    

var query = from item in myObj
            where col2Values.Contains(item.Column2)
            && (item.Column1.IndexOf("a", sc) >= 0
                || item.Column1.IndexOf("b", sc) >= 0)
            select item;

I haven't tested this yet, but it builds just fine. Original version if the above fails:

var col2Values = new int[] { 1, 2, 3 };

var query = from item in myObj
            let col1 = item.Column1.ToLower()
            where col2Values.Contains(item.Column2)
            && (col1.Contains("a") || col1.Contains("b"))
            select item;

I actually prefer the second version, even if it is slightly slower because of the ToLower(). To my eye, it's easier to read. YMMV.

---

Try this.

---

from o in myObj
where
new[] { 1, 2, 3 }.Contains(o.Column2) &&
new[] { "a", "b" }.Any(s => o.Column1.IndexOf(s, StringComparison.Ordinal) != -1)
select o;

---

or use new Hashset<T> if you care about lookup performance. Array brings only O(n).

---

Ordinal means unicode comparison byte-per-byte without culture-specific issues, the most quick.

OrdinalIgnoreCase means the same but case-sensitively