How to do a LIKE in Entity Framework CORE (not full .net)

Question

In the Full .Net EF LIKE's used to be SqlMethods.Like, eg:

from c in dc.Organization
where SqlMethods.Like(c.Boss, "%Jeremy%")

This doesn't work in EF Core and I get this error:

The name SqlMethods does not exist in the current context.

So how do you do a LIKE using Entity Framework CORE?

@Ivan that has the same answer however it's only got 21 views, while this has 27 ATM. Poor choice for a closure. Reason ppl like this leave: https://stackoverflow.com/users/5/jon-galloway — Jeremy Thompson, Nov 14 '17 at 08:24
Frankly I didn't look at such statistics. Duplicate is just (and still) duplicate :) — Ivan Stoev, Nov 14 '17 at 08:25
Better and more indexable than your duplicate, reverse it now please. The difference is `InStr` where as this is `CHARINDEX`, retagging SQL Server — Jeremy Thompson, Nov 14 '17 at 08:26
@Ivan, c'mon, check my profile I'm someone who is donating good answers — Jeremy Thompson, Nov 14 '17 at 08:30
I agree that this question is better formulated. But what to do with the other then? Mark it as duplicate of this? (I'll remove the closure, no problem) — Ivan Stoev, Nov 14 '17 at 08:32

score 35 · Accepted Answer · answered Nov 14 '17 at 04:22

35

The LIKE function has moved under EF.Functions in Core:

from c in dc.Organization
where EF.Functions.Like(c.Boss, "%Jeremy%")

answered Nov 14 '17 at 04:22

Jeremy Thompson

score 2 · Answer 2 · answered Feb 22 '22 at 12:24

2

Fluent version:

dc.Organization.Where(o => EF.Functions.Like(o.Boss, "%Jeremy%"));

answered Feb 22 '22 at 12:24

Shenron

score 1 · Answer 3 · edited Nov 14 '17 at 07:20

1

Instead of Like function you can use Contains

from c in dc.Organization
where c.Boss.Contains("Jeremy")

edited Nov 14 '17 at 07:20

Jeremy Thompson

answered Nov 14 '17 at 06:13

Kaval Patel

2

No, if you run say SQL profiler to capture the raw SQL query you'll see `Contains` uses SQLs `CHARINDEX` Functions. `EF.Functions.Like` maps correctly to SQL `LIKE` in the query. – Jeremy Thompson Nov 14 '17 at 06:15
but when a huge amount of data is there then use CHARINDEX it's work faster then like – Kaval Patel Nov 14 '17 at 06:25
It's not the same, say for example when Full-Text Search is enabled. – Jeremy Thompson Nov 14 '17 at 06:26
4

Also when the search term is null, CHARINDEX returns nothing, while LIKE returns everything. – Jeremy Thompson Nov 14 '17 at 06:27
yeh! that's true – Kaval Patel Nov 14 '17 at 06:28
Contains can be used _in this example_ but in general it is much less than Like. Even MS SQL pattern matching is much more, e.g. "_________2___" is a like expression but not a Contains expression. (Okay, in this simple case you can use charindex.) It's not a theoretical thing, in Scala (a pretty old ERP) all ledger dimensions are merge into a big string and one must make filter by positional values. – Peter Krassoi Oct 17 '19 at 13:30

3 Answers3