Linq to SQL, is there support for a mid-string % wildcard?

Question

Selecting from Linq to Sql, I want to have a string search using wildcards. So far I understand it is possible to do the following

%token = column.EndsWith(value)
token% = column.StartsWith(value)
%token% = column.Contains(value)

What I can't seem to find support for is

to%ken

Is that supported in any way?

Update - people have suggested SqlMethods.Like - however this doesn't seem to work in the usage I have, code below

if (!object.Children.Any() || 
    !object.Children.OrderByDescending(t => t.Version)
        .First().MetaDataPairs.Any(mdp => SqlMethods.Like(mdp.Value.ToLower(), stringmatch))) continue;

and error I get when trying this

Error was Method 'Boolean Like(System.String, System.String)' cannot be used on the client; it is only for translation to SQL.

See the not accepted but more upvoted answer from Ryan here, is this what you're looking for? http://stackoverflow.com/questions/1040380/wildcard-search-for-linq — Equalsk, Jan 18 '17 at 16:43
Check also this one out http://stackoverflow.com/questions/3000153/linq-to-sql-wildcards — Luis Lavieri, Jan 18 '17 at 16:44
@J.Pichardo Err, isn't that exactly what OP wants? It's in the title of the post and the first 5 words of the post. Am I missing something? — Equalsk, Jan 18 '17 at 16:51
Since this question is locked because it is duplicate I cannot answer here. But I've posted an answer here: http://stackoverflow.com/questions/1040380/wildcard-search-for-linq/42307642#42307642 — , Feb 17 '17 at 21:26

score 1 · Answer 1 · answered Jan 18 '17 at 16:45

1

In the namespace SqlMethods there is a like method, you could use it like:

Where SqlMethods.Like(entity.StringProperty, "tok%en");

This only works for Linq to Sql queries, if you want a more general approach I would recommend using Regex, something like:

.Where(entity => Regex.matches(entity.StringProperty, @"(?:tok)(\w*)(?:en)");

answered Jan 18 '17 at 16:45

J. Pichardo

3,077
21
37

Is there a way to do this with the other Linq syntax, eg I need it in the below structure but it doesn't seem to work in this usage. (mentioned above) – NZJames Jan 19 '17 at 10:13
Can you post an example? – J. Pichardo Jan 19 '17 at 14:01

score 1 · Answer 2 · edited Jan 18 '17 at 17:14

1

2 suggestions:

1) Using

SqlMethods.Like

2) Using Regex like

System.Text.RegularExpressions.Regex regEx = new System.Text.RegularExpressions.Regex("token");

edited Jan 18 '17 at 17:14

TheLethalCoder

6,668
6
34
69

answered Jan 18 '17 at 16:46

Ranadip Dutta

8,857
3
29
45

Regex is better in case the ORM is changed from LINQ to SQL to EF or NHibernate for instance. – Neo Nov 01 '19 at 11:28

Linq to SQL, is there support for a mid-string % wildcard?

2 Answers2