Linq to SQL "not like" operator

Question

I have a simple SQL statement.

Select distinct value from tablename where value not like '%TEST%'

How do I write this in Linq to SQL syntax.

I tried the below statement but it doesnt seem to work.

var p = (from c in tablename where !(c.value.ToUpper().Contains("%TEST%")) 
        select c.Value).Distinct().ToList()

Answer 1

The problem is the "%" - you're looking for things which literally don't contain "%TEST%" which would probably be everything. I think you mean:

var p = (from c in tablename
         where !c.Value.ToUpper().Contains("TEST")
         select c.Value).Distinct().ToList()

Answer 2

If you were stuck with a pattern for sql to match, you could use SqlMethods.Like

string pattern = "%TEST%";
  ...
from c in tablename
where !SqlMethods.Like(c.Value, pattern)
select c