Is there a way to use the LIKE operator from an entity framework query?

Question

OK, I want to use the LIKE keyword from an Entity Framework query for a rather unorthodox reason - I want to match strings more precisely than when using the equals operator.

Because the equals operator automatically pads the string to be matched with spaces such that col = 'foo ' will actually return a row where col equals 'foo' OR 'foo ', I want to force trailing whitespaces to be taken into account, and the LIKE operator actually does that.

I know that you can coerce Entity Framework into using the LIKE operator using .StartsWith, .EndsWith, and .Contains in a query. However, as might be expected, this causes EF to prefix, suffix, and surround the queried text with wildcard % characters. Is there a way I can actually get Entity Framework to directly use the LIKE operator in SQL to match a string in a query of mine, without adding wildcard characters? Ideally it would look like this:

string usernameToMatch = "admin ";
if (context.Users.Where(usr => usr.Username.Like(usernameToMatch)).Any()) {
    // An account with username 'admin ' ACTUALLY exists
}
else {
    // An account with username 'admin' may exist, but 'admin ' doesn't
}

I can't find a way to do this directly; right now, the best I can think of is this hack:

context.Users.Where(usr =>
    usr.Username.StartsWith(usernameToMatch) &&
    usr.Username.EndsWith(usernameToMatch) &&
    usr.Username == usernameToMatch
)

Is there a better way? By the way I don't want to use PATINDEX because it looks like a SQL Server-specific thing, not portable between databases.

"Because the equals operator automatically pads the string to be matched with spaces": I am *pretty* sure that this isn't the case. — Daniel Hilgarth, Feb 15 '13 at 10:41
@DanielHilgarth Yes it is. See http://stackoverflow.com/questions/5854769/linq-automatically-trims-my-string and http://support.microsoft.com/kb/316626 — Jez, Feb 15 '13 at 10:54
maybe dublicate of [this](http://stackoverflow.com/questions/1033007/like-operator-in-entity-framework) — Jens Kloster, Feb 15 '13 at 11:02
@JensKloster The top answer there says to use the non-standard `PATINDEX`. — Jez, Feb 15 '13 at 11:12
@Jez yeah - but look at the buttom - Jon Skeet has a (in my opinion) a better anwser, than the one marked as accepcted. — Jens Kloster, Feb 15 '13 at 11:23
@JensKloster Not really, he just recommends `.Contains` which I've mentioned in my question isn't acceptable. — Jez, Feb 15 '13 at 11:29
`usr.Username.StartsWith(usernameToMatch)` would be hilariously insecure. I'd just create a user called `admin2` cause all kinds of hell. You really really shouldn't allow rough matches on authentication information. — PhonicUK, Feb 15 '13 at 13:06
@PhonicUK That's why I combine it with `.EndsWith` and the equality operator. — Jez, Feb 15 '13 at 16:45

PhonicUK · Answer 1 · 2013-02-15T13:07:24.737

0

There isn't a way to get EF to use LIKE in its query, However you could write a stored procedure that finds users using LIKE with an input parameter and use EF to hit your stored procedure.

Your particular situation however seems to be more of a data integrity issue though. You shouldn't be allowing users to register usernames that start or end with a space (username.Trim()) for pretty much this reason. Once you do that then this particular issue goes away entirely.

Also, allowing 'rough' matches on authentication details is beyond insecure. Don't do it.

edited Feb 15 '13 at 13:07

answered Feb 15 '13 at 10:40

PhonicUK

13,486
4
43
62

Are you sure there is no way? LINQ 2 SQL had helpers for that that did translate to SQL. EF must have a mechanism for this. – usr Feb 15 '13 at 11:21
There's the `SqlFunctions` methods that you can use which work for LINQ to Entities as well - but there isn't a `LIKE` method in there. See http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.aspx – PhonicUK Feb 15 '13 at 12:10
I see that there is a way using ESQL but not LINQ to EF. LINQ support in EF is just a disgrace. L2S was much better 3 years ago. – usr Feb 15 '13 at 12:59
The LINQ support is fine. C# doesn't have a 'LIKE' method (you use Regexes) and SQL doesn't support Regex's - so there isn't always a neat way of making this sit nicely in the middle. Depending on the number of users you could just use `ToList()` so that you can use any method. But for logins you should *not* be doing rough matches. – PhonicUK Feb 15 '13 at 13:05
It could work just like with L2S. EF LINQ does not support all kinds of stuff (DateTime.Date anyone?). – usr Feb 15 '13 at 13:18
You use `SqlFunctions.DatePart` instead. – PhonicUK Feb 15 '13 at 13:32
@PhonicUK What part of "I want to match strings more precisely than when using the equals operator" don't you understand? I do not talk in my question about using 'rough' matches anywhere - I specifically talk about using a combination of matches to achieve an *exact* match, so I think you should remove that warning at the end as it implies I talked about using rough matches. – Jez Feb 16 '13 at 17:49

score 0 · Accepted Answer · answered Mar 19 '13 at 10:51

Well there doesn't seem to be a way to get EF to use the LIKE operator without padding it at the beginning or end with wildcard characters, as I mentioned in my question, so I ended up using this combination which, while a bit ugly, has the same effect as a LIKE without any wildcards:

context.Users.Where(usr =>
    usr.Username.StartsWith(usernameToMatch) &&
    usr.Username.EndsWith(usernameToMatch) &&
    usr.Username == usernameToMatch
)

So, if the value is LIKE '[usernameToMatch]%' and it's LIKE '%[usernameToMatch]' and it = '[usernameToMatch]' then it matches exactly.

Is there a way to use the LIKE operator from an entity framework query?

2 Answers2