Like operator in Linq to DataTable?

Question

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();

Brill

Answer 1

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)

Celia

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 – Brill

@Brill - no need for regex there; just use where row.CompanyName.Contains(someName) – Celia

@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. – Brill

@Brill - can you show with a full example? Contains is (other than case) the same as %foo% – Celia

@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. – Brill

If I have ABC, ABC Associates, BAC, XYZ Associates as data. On seaching for A is should returns all but not BAC. – Brill

@Brill ah, I couldn't see the space; then where row.CompanyName.StartsWith(name) || row.CompanyName.Contains(" " + name) – Celia

Answer 2

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");

Shona

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. – – Brill