Use SQL LIKE operator in C# LINQ

Question

I am building up a query in C#. For integer and string fields, case is quite simple. For date fields, I am using following query:

list.Where("myDateColumn >= DateTime(2017,1,20)");

How I can perform following SQL LIKE query in LINQ?

select * from table where myTextColumn LIKE '%abc%';

@monstertjie_za: I want to append like filter in same date filter. — Behzad Qureshi, Jan 20 '17 at 07:01
Do you want to perform a LIKE against a date? Why not just select where the date is equal to a certain date, and maybe ignore the time part? — monstertjie_za, Jan 20 '17 at 07:03
Example folks: list.Where("myDateColumn >= DateTime(2017,1,20) AND myTextColumn LIKE '%abc%' "); — Behzad Qureshi, Jan 20 '17 at 07:05
list.Where(x => x.myDateColumn >= date && x.myTextColumn.Contains('abc')); — monstertjie_za, Jan 20 '17 at 07:05
The Contains is what you are looking for. It checks to see if the object you are doing the contains against, contains the text you specify, which is essentially the same as what the LIKE does. — monstertjie_za, Jan 20 '17 at 07:21
So again there is no way I can specify like query in string? — Behzad Qureshi, Jan 20 '17 at 07:23
No, C# is not SQL. Like @un-lucky has mentioned, you can mix this up with StartWith if you only want to find the words starting with your specified texts, EndsWith only to find words that ends with your text, or Contains for the text anywhere in the word. Do you even know what the Wildcard '%' purpose is in the SQL LIKE clause? Placement of the Wildcard makes a difference, and C# has the methods to back this up — monstertjie_za, Jan 20 '17 at 07:24
@monstertjie_za Thanks for your time. Simple answer is there is no way to use LIKE while specifying condition in string. — Behzad Qureshi, Jan 20 '17 at 09:47

sujith karivelil · Accepted Answer · 2017-01-20T07:18:58.553

39

There are lots of possibilities for Like in Linq:

For LIKE '%abc%';

list.Where(x => x.myTextColumn.Contains('abc'));

For LIKE 'abc%';

list.Where(x => x.myTextColumn.StartWith('abc'));

For LIKE '%abc';

list.Where(x => x.myTextColumn.EndsWith('abc'));

Updates : If you need to add Date comparison as well means you can do like the following:

DateTime date2Compare = new DateTime(2017, 1, 20);
list.Where(x => myDateColumn >= date2Compare && x.myTextColumn.Contains('abc'));

edited Jan 20 '17 at 07:18

answered Jan 20 '17 at 07:02

sujith karivelil

28,671
6
55
88

I want to append like filter in same date filter. – Behzad Qureshi Jan 20 '17 at 07:03
@BehzadQureshi: What you are expected the `Like` to do with Date fields? what is the datatype of that column in Database? whats your actual requirement? – sujith karivelil Jan 20 '17 at 07:04
Kindly read comments in question. You will get the idea of actual problem – Behzad Qureshi Jan 20 '17 at 07:11
@BehzadQureshi: See the updates in my answer: forgive me if I misunderstand the scenario that you ware specified – sujith karivelil Jan 20 '17 at 07:19
Missing: So there is no way I can specify like query in string? – Behzad Qureshi Jan 20 '17 at 07:21
Could you please make a try on what i suggested. and let me know how the output different from your expected output – sujith karivelil Jan 20 '17 at 07:24
That is a work around and it is already working. Well, thanks. – Behzad Qureshi Jan 20 '17 at 07:39

score 7 · Answer 2 · answered Jan 20 '17 at 07:34

Placement of the Wildcard '%' in a LIKE clause makes a difference, and C# has the methods to back this up. Below is what the placements of the Wildcard means.

LIKE '%abc'

Meaning: Find any word ending with 'abc'.

C# equivalent: EndsWith

LIKE 'abc%'

Meaning: Find any word starting with 'abc', and you don't care about the text after.

C# equivalent: StartWith

LIKE '%abc%'

Meaning: Find any word that contains 'abc', and you don't care where in the word it appears.

C# equivalent: Contains

Satpal · Answer 3 · 2017-01-20T07:04:45.897

4

You can use Contains with myTextColumn field

var date = new DateTime(2017,1,20);
list.Where(x => x.myDateColumn >= date  && x.myTextColumn.Contains('abc'));

edited Jan 20 '17 at 07:04

answered Jan 20 '17 at 06:59

Satpal

132,252
13
159
168

score 2 · Answer 4 · answered Jan 20 '17 at 07:09

Let's try solving the problem in general case. Suppose we're given something like

select ...
 where ... MyField like '%abc%'

we can try convert like expression into corresponding regular one:

Like | Description                       |Regular
-------------------------------------------------
   _ | any character  (one and only one) | .
   % | any characters (zero or more)     | .*

Implementation

// If you want to implement both "*" and "?"
private static String LikeToRegular(String value) {
  return "^" + Regex.Escape(value).Replace("_", ".").Replace("%", ".*") + "$"; 
}

usage:

var result list
  .Where(x => Regex.IsMatch(x.myTextColumn, LikeToRegular("%abc%")));

you may want to convert the data into string before matching:

 var result list
  .Where(x => Regex.IsMatch(x.myDate.ToString(), LikeToRegular("%abc%")));

score 2 · Answer 5 · answered Aug 06 '22 at 15:32

While other answers (including the accepted one) are 80% correct, there are some caveats.

T-SQL allows the Like statement to have wildcards not just at the start and end of a string, but also in the middle. And % is not the only special char in the syntax, see docs for details.

.NET Core has EF.Functions.Like() method that is literally translated into LIKE statement. See the below example.

//assuming there is a db context with a table of Foos
await using var dbContext = new SqlContext();

//assuming table of Foos has Ipsum, Iptum, Ipuum
var result = dbContext.Foos.Where(x
    => EF.Functions.Like(x.Bar, "Ip_um"));

Caveats:

This only works with a server-side evaluation. An attempt to use this feature in client-side evaluation will throw NotSupportedException.
There is a noticeable difference in SQL translation between EF.Functions.Like and Contains or StartsWith, which may impact performance.

If you are certain you are using server-side evaluation and performance is important, use EF.Functions.Like.

Use SQL LIKE operator in C# LINQ

5 Answers5