5

I am using Linq to DataTable. How I can apply like operator in where clause. I want to do a search on data just as we have like operator in SQL.

I searched and tried the following code but got an error: Method 'Boolean Like(System.String, System.String)' cannot be used on the client; it is only for translation to SQL.

var details = from addresses in dt.AsEnumerable() 
    where SqlMethods.Like(prefixText, prefixText + "%") || SqlMethods.Like(prefixText, "%" + prefixText + "%")
    select (string) addresses["Details"];                      

return details.ToArray();
Lipis
  • 21,388
  • 20
  • 94
  • 121
haansi
  • 5,470
  • 21
  • 63
  • 91

2 Answers2

4

Your best bet may be to re-write it as a regex an use

where yourRegex.IsMatch(row.SomeValue)

Or if it is just starts-with queries:

where row.SomeValue.StartsWith(prefix)
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Thanks Marc, I am sorry I am not aware of regulare expressions. Can you please guide what will be regular expression for this sql where clasue "where companyname like @string+'%' or companyname like '% '+@string+'%' "; Thanks – haansi Dec 16 '10 at 17:48
  • 1
    @haansi - no need for regex there; just use where row.CompanyName.Contains(someName) – Marc Gravell Dec 16 '10 at 17:53
  • @Marc I tried it earlier but it is not returning correct results. Suyppose I have ABC, ABC And Company, BAC, ABC Associates values. Using it returns even BAC, if I pass it should return all but not BAC. please guide. – haansi Dec 16 '10 at 17:59
  • @haansi - can you show with a full example? Contains is (other than case) the same as %foo% – Marc Gravell Dec 16 '10 at 18:03
  • @Marc I had this in stored procedrue "where companyname like @string+'%' or companyname like '% '+@string+'%'" , here notable is second like. Here I have a space after %, "% ". If I search for A ir returns ABC adn XYZ Associates but it do not returns BAC. – haansi Dec 16 '10 at 18:08
  • If I have ABC, ABC Associates, BAC, XYZ Associates as data. On seaching for A is should returns all but not BAC. – haansi Dec 16 '10 at 18:10
  • @haansi ah, I couldn't see the space; then `where row.CompanyName.StartsWith(name) || row.CompanyName.Contains(" " + name)` – Marc Gravell Dec 16 '10 at 18:19
4
var details = from addresses in dt.AsEnumerable()
                where addresses.Field<string>("Details").StartsWith(prefixText)
                ||  addresses.Field<string>("Details").Contains(prefixText)
                select addresses.Field<string>("Details");
Tom Brothers
  • 5,929
  • 1
  • 20
  • 17
  • contains do not returns correct results. Suyppose I have ABC, ABC And Company, BAC, ABC Associates values. Using it returns even BAC, if I pass it should return all but not BAC. please guide. – – haansi Dec 16 '10 at 18:04