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
  • 34,719
  • 24
  • 185
  • 251
CodeNoob
  • 391
  • 1
  • 6
  • 24

2 Answers2

5

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

C#:

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

=> SQL

 WHERE c.Name LIKE 'Bloggs%'

C#:

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

=> SQL

WHERE c.Name LIKE '%Bloggs%'

C#:

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

=> SQL

WHERE c.Name LIKE '%Bloggs'

If applicable, StartsWith should be preferred over the other two, given that it has a better chance of using an index on the column. (%x% will generally result in an index or table scan).

StuartLC
  • 100,561
  • 17
  • 199
  • 269
  • 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 collation on the column in the DB as to whether the filter is case sensitive or not. – StuartLC Nov 04 '20 at 20:35
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,365
  • 23
  • 87
  • 156
  • 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