0

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 ( Column1 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 ...

Erick
  • 5,969
  • 10
  • 42
  • 61
  • What is the purpose of `WHERE 1=1`? Won't that just mean `True` and won't make a difference? – Marcus Feb 04 '11 at 14:49
  • 6
    @G_M - normally this is done when constructing dynamic SQL so you don't need to keep checking for an existing where clause. – Oded Feb 04 '11 at 14:50
  • Is your question how to handle an OR dynamically? – JohnOpincar Feb 04 '11 at 15:28
  • I think your question is just a duplicate of this one: http://stackoverflow.com/questions/4647130/building-up-a-query-expression-dynamically-or – JohnOpincar Feb 04 '11 at 15:33

5 Answers5

1

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

Community
  • 1
  • 1
James Carl Lind
  • 11
  • 2
0

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

Roberto Conte Rosito
  • 2,080
  • 12
  • 22
0
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.

Justin Morgan - On strike
  • 30,035
  • 12
  • 80
  • 104
  • Never use `ToLower()`, use `StringComparison` instead – abatishchev Feb 04 '11 at 15:13
  • @abatishchev: `StringComparison` was what I was trying to remember. I'll edit. – Justin Morgan - On strike Feb 04 '11 at 15:17
  • Wouldn't work on a dynamically created query, and it does not work with the % operator SqlMethods.Like() is the way to work with it, but I can't see a way to make it work here. – Erick Feb 04 '11 at 15:20
  • @Erick: What exactly wouldn't work? You mean the `string.Contains` version or the `string.IndexOf` version? I believe `string.Contains` works in .NET 4.0, but I can't remember whether I used it in LINQtoObjects or LINQtoEntities, so I don't know if it can get converted into SQL. – Justin Morgan - On strike Feb 04 '11 at 15:36
0
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

abatishchev
  • 98,240
  • 88
  • 296
  • 433
0

Try this.

Community
  • 1
  • 1
JohnOpincar
  • 5,620
  • 3
  • 35
  • 38