1

Possible Duplicate:
How to do SQL Like % in Linq?

Im using mvc 3 and entity framework. How do i do a like search with linq or a lambda expression. Please assisst

Cœur
  • 37,241
  • 25
  • 195
  • 267
CodeNoob
  • 411
  • 1
  • 7
  • 24

2 Answers2

6

Since the goal is for EF expressions to be parsed into SQL, where the LIKE predicate should be applied, there are at least 3 ways to do this, depending on where you want the % wildcard to be placed

Starts With the Phrase

C#:

.Where(customer => customer.Name.StartsWith("Bloggs"))

=> SQL

 WHERE c.Name LIKE 'Bloggs%'
Contains the Phrase

C#:

.Where(customer => customer.Name.Contains("Bloggs"))

=> SQL

WHERE c.Name LIKE '%Bloggs%'
Ends in the Phrase

C#:

.Where(customer => customer.Name.EndsWith("Bloggs"))

=> SQL

WHERE c.Name LIKE '%Bloggs'

Performance

When applicable, for performance reasons, StartsWith should be preferred over the other two, given that it has a better chance of using an index on the column.

LIKE %x% or LIKE %x will generally result in an index or table scan, unless an unusual index is created on the column.

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • I just used contains and it worked. Is it going to break if i do not include the startswith and ends with with code? – CodeNoob Mar 13 '12 at 06:52
  • Contains will work / cover all cases, but will generate SQL: LIKE '%Bloggs%' which usually results in Index Scans / Table Scans. If you know that your search starts with a word, then like 'Bloggs%' will be much quicker in SQL than '%Bloggs%' (providing that it is indexed correctly). AFAIK EndsWith (i.e. '%Bloggs') won't be very useful to SQL Indexes – StuartLC Mar 13 '12 at 08:13
  • in case of case insensitive search, we would need to manually apply either upper or lower case ourselves, as done in sql correct? – Muneeb Mirza Nov 04 '20 at 18:57
  • The Linq expression is converted to Sql, so the filter predicate will be applied in the database. Thus it depends on the CS / CI collation setting on the column in the DB as to whether the filter is case sensitive or not – StuartLC Sep 06 '22 at 17:46
1

it's duplicate to - LIKE operator in LINQ How to do SQL Like % in Linq?

Typically you use String.StartsWith/EndsWith/Contains. For example:

var Code = .Where(p => p.Code.Contains("BALTIMORE"))
var Code = .Where(p => p.Code.StartsWith("BALTIMORE"))
var Code = .Where(p => p.Code.EndsWith("BALTIMORE"))
Community
  • 1
  • 1
Dor Cohen
  • 16,769
  • 23
  • 93
  • 161
  • I just used contains and it worked. Is it going to break if i do not include the startswith and ends with with code? – CodeNoob Mar 13 '12 at 06:30