LINQ contains appends % and escapes %

I'm using LINQ to create dynamic sql, when I'm using contains I don't want it to prefix and suffix % and if I'm using % inside my string I don't want to escape it. It escapes the percentage signs added by me using ~ as prefix before % as escape sequence character

For instance:

开发者_运维百科

string str = '%test%.doc%' 
.Contains(str) // converts this into LIKE '%~%test~%.doc~%%'

Expected Conversion: LIKE '%test%.doc%%'

---

as questioner asked, I've made my comments an answer

See Using LINQ Contains vs. SqlMethods.Like and in general the SqlMethods.Like method which will enable you to do a custom LIKE with Linq-to-sql.

Simple example:

var res = from row in dc.Table
          where SqlMethods.Like(row.Column, "%A%A%")
          select row;

More examples with Contains,StartsWith and Like: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/10/16/linq-to-sql-like-operator.aspx

---

Contains is probably translating into the use of the LIKE operator in SQL. This operator takes % as a wildcard character. Contains("abc") maps to LIKE '%abc%'.

---

I use the following extensions to avoid that case (although in my specific case, I'm still using wildcards, but you could modify it for your own effect).

public static bool Like(this string value, string term)
    {
        Regex regex = new Regex(string.Format("^{0}$", term.Replace("*", ".*")), RegexOptions.IgnoreCase);
        return regex.IsMatch(value ?? string.Empty);
    }

    public static IEnumerable<string> Like(this IEnumerable<string> source, string expression)
    {
        return (from s in source where s.Like(expression) select s);
    }

---

Unfortunately, I can't think of an easy way to do this, but this might work:

var a = from t in Db.Tests
        let i1 = t.Name.IndexOf("test")
        let i2 = t.Name.IndexOf(".doc")
        where i1 != -1 && i2 != -1 && i1 < i2
        select t;

Here is the equivalent in method chains:

Db.Tests.Select(t => new {t, i1 = t.Name.IndexOf("test")}).Select(
                @t1 => new {@t1, i2 = @t1.t.Name.IndexOf(".doc")}).Where(
                    @t1 => @t1.@t1.i1 != -1 && @t1.i2 != -1 && @t1.@t1.i1 < @t1.i2).Select(@t1 => @t1.@t1.t);