LINQ2SQL LIKE Command for Phrase

Question

Hi all,

I am trying to retrieve a list of string where it contains "Britney Spears", and this is what I use

from p in Objects
where p.Title.Contains("Britney Spears")
select p

That works fine, but if I want to select title which is "Britney Jean Spears", "Britney 'Sexy' Spears" it doesn't work, so my question is how do I insert a wildcard '%' in between Britney Spears while conducting a LINQ2SQL search? Thanks.

Regards, Andy.

Answer 1

do exactly as you would with straight up SQL

you can supply special characters with the like clause such as ".", "%" and "[]"

if that is not good enough, then you can use regex, but be careful since regex will be executed "client"-side, i.e. it will fetch the whole thing and then try to narrow results down in memory. You can somewhat optimize zerkms' method by doing

from p in Objects
where p.Title.Contains("Britney") && p.Title.Contains("Spears") && regex.Match(p.Title).Success
select p

I haven't tested it, but it should run first two conditions on the SQL server side, and then narrow down using regex.

Answer 2

Just use StartsWith() and EndsWith() to simulate the pattern. AFAIK, LINQ-to-SQL doesn't support wildcards.

from p in Objects
where p.Title.StartsWith("Britney") && c.Title.EndsWith("Spears")
select p;

Otherwise you could execute a query directly if you have a more complex pattern.

db.ExecuteQuery<MyObject>("SELECT * "
                        + "FROM Objects "
                        + "WHERE Title LIKE 'Britney%Spears'");

Answer 3

You can use the SqlMethods.Like method for this purpose.

from p in Objects
where SqlMethods.Like(p.Title, "Britney% Spears")
select p