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.
You can use the SqlMethods.Like method for this purpose.
from p in Objects
where SqlMethods.Like(p.Title, "Britney% Spears")
select p
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'");
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.
