What's the Linq equivalent of SQL's LIKE with wildcards?

Data and Databases
1

I’m trying to convert an SQL query to Linq. The query uses LIKE with % wildcards to search a hierarchy column. Here’s what I have so far:

var result = from o in Organizations
             join oh in OrganizationsHierarchy on o.Id equals oh.OrganizationsId
             where oh.Hierarchy.Contains("/12/")
             select new { o.Id, Organization = o.Name };

Is this the right way to do it? I’m not sure if Contains() works the same as LIKE with % wildcards. The hierarchy values look like ‘/1/3/12/’ and I need to find all entries that have ‘/12/’ anywhere in the string.

Any help would be great. I’m new to Linq and not sure about the best way to handle this kind of string matching.

2

The approach you’re taking with Contains() is on the right track, but it might not capture all the nuances of SQL’s LIKE with wildcards. For a more precise equivalent, you could consider using regular expressions. Here’s an alternative:

var result = from o in Organizations
             join oh in OrganizationsHierarchy on o.Id equals oh.OrganizationsId
             where System.Text.RegularExpressions.Regex.IsMatch(oh.Hierarchy, @"/12/")
             select new { o.Id, Organization = o.Name };

This uses Regex.IsMatch() to find ‘/12/’ anywhere in the string, which more closely mimics the behavior of LIKE with % wildcards. It’s worth noting that while this approach is more flexible, it may have performance implications for large datasets. In such cases, you might want to explore indexing strategies or consider if a full-text search solution would be more appropriate for your needs.

3

hey there! have u thought about using EF Core 2.0+ and its like() method? it’s pretty neat for this kinda stuff. something like:

where EF.Functions.Like(oh.Hierarchy, ‘%/12/%’)

might be worth checkin out. what do u think? how big is ur dataset anyway?

4

have u tried using string.contains()? it works pretty well for this kinda thing. just make sure ur string comparison is case-insensitive if that matters. like this:

where oh.Hierarchy.IndexOf(“/12/”, StringComparison.OrdinalIgnoreCase) >= 0

this should do the trick for finding ‘/12/’ anywhere in ur hierarchy string