How to check string contents within a LINQ database query

Question

In my LINQ query I want to check that a numeric string is followed by only non-numeric characters. In regex, it would look something like this: ^[0-9]+[^0-9]*$

However, I've discovered that LINQ can't translate regex.

I've also tried doing the following:

List<char> numbers = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '0'];
var queryableNumbers = numbers.AsQueryable();

from ...
where
...
   && row.content.ToUpper().StartsWith(searchTerm)
   && !queryableNumbers.Any(number => row.content.Substring(searchTerm.Length).Contains(number))
...

Which also doesn't work because LINQ can't translate it for whatever reason. The line !queryableNumbers.Any(number => row.content.Substring(searchTerm.Length).Contains(number)) is the problem.

I've done my best to test all the components in isolation (Contains(), Any(), and Substring()) and it seems that as soon as I put Contains() inside the Any(), it fails.

All I can think to do is to keep the first line of the where block, and move the logic of the second line out of the LINQ query and into an extra loop to filter my results. In other words:

1. Use LINQ to get data that satisfies ^[0-9]+
2. Filter via a for-loop or some other option to ensure ^[0-9]+[^0-9]*$ is satisfied

I'd like to avoid an extra loop if possible. At this point I'm stuck. Does anyone have any suggestions for other ways to approach this issue.

Answer 1

Only thing I can think of is to first limit query with LIKE operator and look only for string that start with number, then in C# code, when query is fetched, filer further the results:

// Get entities which have the field starting with digit.
// [0-9]%" means match one digit and rest is arbitrary.
var semiFiltered = databaseContext.Entities
    .Where(x => DbFunctions.Like(x.SemiNumericField, "[0-9]%")
    .ToArray();

// Here we use Regex to do the rest
var filtered = semiFiltered
    .Where(x => Regex.IsMatch(x.SemiNumericField, "^[0-9]+[^0-9]*$"))
    .ToArray();