Use LIKE "%{%}%" in Entity Framework with Linq?

Question

I try to do a request to my database and I want to use something like the "LIKE" operator in SQL to resolve this pattern: %{%}% Using the "SQL-friendly" syntax. Currently my request looks like:

var routelist = (from dbHost in db.Hosts
                             where dbHost.Host == host
                             join dbRoute in db.Route on dbHost.HostsId equals dbRoute.HostId
                             select dbRoute).ToList();

If someone know how to do, can he tells it to me ? Thanks

EDIT :

I want to do something like it :

var routelist = (from dbHost in db.Hosts
                             where dbHost.Host == host
                             join dbRoute in db.Route on dbHost.HostsId equals dbRoute.HostId
                             where dbRoute.alias like "%{%}%"
                             select dbRoute).ToList();

Are you searching for wildcards in linq? In that case this might help you: http://stackoverflow.com/questions/1040380/wildcard-search-for-linq — Mickey, Nov 04 '16 at 11:00
@Mickey I think this is what I look for but do this way is optimized ? Do it only filter the request result or do it only pick the corrects fields in the database ? — Victor Castro, Nov 04 '16 at 11:06

marcartigas · Answer 1 · 2017-11-28T22:29:14.393

3

Entity Framework Core 2.0 has now available EF.Functions property that includes EF.Functions.Like().

https://blogs.msdn.microsoft.com/dotnet/2017/08/14/announcing-entity-framework-core-2-0/

Example:

var customers =
    from c in context.Customers
    where EF.Functions.Like(c.Name, "a%");
    select c;

edited Nov 28 '17 at 22:29

answered Nov 28 '17 at 18:48

marcartigas

61
2

score 2 · Accepted Answer · answered Nov 04 '16 at 11:10

When I need to perform such LIKE queries with entity framework (and this happens very rarely, because such like queries usually cannot use any index and so perform full table scan and are quite slow on big tables), I use PATINDEX, like this:

var routelist = (from dbHost in db.Hosts
                         where dbHost.Host == host
                         join dbRoute in db.Route on dbHost.HostsId equals dbRoute.HostId
                         where SqlFunctions.PatIndex("%{%}%",dbRoute.alias) > 0
                         select dbRoute).ToList();

PATINDEX function in sql server is like LIKE, but returns the position of the first match.

If you want to perform LIKE query in a form of "%something%", you can use Contains("something"). If it has the form of "%something" - use StartsWith("something"). If it has the form of "something%" - use EndsWith("something").

It seems to be exactly what I need but It also seems not to exist on __ASP.NET Core__ (Actually, System.Data.Object seems not to exists) — Victor Castro, Nov 04 '16 at 12:57
True, didn't notice it's about EF Core. Well such functions are not yet supported there (not sure how it can be used for any serious stuff in current state). You can try workaround using FromSql as described here: https://github.com/aspnet/EntityFramework/issues/3325 — Evk, Nov 04 '16 at 13:02

Use LIKE "%{%}%" in Entity Framework with Linq?

2 Answers2