LINQ to Entities using the SQL LIKE operator

Question

I have this:

query = query.Where(s => s.ShowTypeDescription == showTypeDescription);

several times for different variables in order to build dynamic SQL.

How would I go about transforming the above to say:

query = query.Where(s => s.ShowTypeDescription **LIKE** showTypeDescription);

?

search is your friend - http://stackoverflow.com/questions/2493703/linq-to-entity-using-a-sql-like-operator?rq=1 — EkoostikMartin, Jun 13 '13 at 21:53
that doesn't help me. I know about "Contains" but how would I implement that into the code I have? — JJ., Jun 13 '13 at 21:53
If you knew about `Contains()` you wouldn't be asking this question... — EkoostikMartin, Jun 13 '13 at 21:58

score 13 · Answer 1 · answered Jun 13 '13 at 21:53

13

query = query.Where(s => s.ShowTypeDescription.Contains(showTypeDescription));

   Contains() is translated LIKE '%term%'
   StartsWith() = LIKE 'term%'
   EndsWith()   = LIKE '%term'

answered Jun 13 '13 at 21:53

Nick

4,192
1
19
30

p.s.w.g · Accepted Answer · 2016-08-09T14:00:22.967

5

If all you want is to find a substring within another string, the best way to do this is with the Contains method:

query = query.Where(s => s.ShowTypeDescription.Contains(showTypeDescription));

Because the String.Contains method translates to:

CHARINDEX(ShowTypeDescription, @showTypeDescription) > 0

Which is roughly equivalent to:

ShowTypeDescription LIKE '%' + @showTypeDescription + '%'

Update: In Linq-to-SQL, you can use the SqlMethods.Like method:

query = query.Where(s => SqlMethods.Like(s.ShowTypeDescription, showTypeDescription));

This will directly translate to the SQL LIKE operator. Note, however, this won't work outside of Linq-to-SQL queries. Trying to call this method in other contexts will throw an exception.

edited Aug 09 '16 at 14:00

answered Jun 13 '13 at 21:53

p.s.w.g

146,324
30
291
331

any idea if i can make my own expression? i would pass a string – CMS Aug 09 '16 at 09:01
im not sure why but if i call do it in EF6 i get an error converting store expression Boolean like(System.String,System.String) – CMS Aug 09 '16 at 18:50
1

I guess EF uses Linq-to-Entity, while this `SqlMethods` only work with Linq-to-SQL. `StartsWith` works for me. – Hp93 May 12 '20 at 07:26

LINQ to Entities using the SQL LIKE operator

2 Answers2