linq match word with boundaries

Question

say i have a nvarchar field in my database that looks like this

1, "abc abccc dabc"
2, "abccc dabc"
3, "abccc abc dabc"

i need a select LINQ query that would match the word "abc" with boundaries not part of a string

in this case only row 1 and 3 would match

Answer 1

Maybe a regular expression like this (nb - not compiled or tested):

var matches = from a in yourCollection
         where Regex.Match(a.field, ".*\sabc\s.*")
         select a;

Answer 2

from row in table.AsEnumerable()
where row.Foo.Split(new char[] {' ', '\t'}, StringSplitOptions.None)
    .Contains("abc")
select row

It's important to include the call to AsEnumerable, which means the query is executed on the client-side, else (I'm pretty sure) the Where clause won't get converted into SQL succesfully.

Answer 3

datacontext.Table.Where(
         e => Regex.Match(e.field, @"(.*?[\s\t]|^)abc([\s\t].*?|$)")
);

or

datacontext.Table.Where(
         e => e.Split(' ', '\t').Contains("abc");
);

Answer 4

For efficiency, you want to do as much of the filtering as possible on the server, and then the rest of the filtering on the client. You can't use Regex on the server (SQL Server doesn't support it) so the solution is to first use a LIKE-type search (by calling .Contains) then use Regex on the client to further refine the results:

db.MyTable
  .Where (t => t.MyField.Contains ("abc"))
  .AsEnumerable()    // Executes locally from this point on
  .Where (t => Regex.IsMatch (t.MyField, @"\babc\b"))

This ensures that you retrieve only the rows from SQL Server than contain the letters 'abc' (regardless of whether they're a word-boundary match or not) and use Regex on the client-side to further restrict the result set so that only matches that are on word boundaries are included.