0

Short Version

I want to search a string data type for all numbers between "1000000" and "1009999", and the LINQ query needs to be SQL optimised.

Longer Version

I see many posts for "Contains()", "StartsWith()" and "EndsWith()", but nothing for a number compare.

What I want is the equivalent of SQL like '100____', but it must be all numbers, thus like '100[0-9][0-9][0-9][0-9]'. (The data type is string and can contain non-numeric data.)

What is the LINQ equivalent, that is also SQL optimised, to validate that a string value is only numbers?

I tried .Where(p => p.Value >= "1000000" && p.Value < "1010000"), but comparison on strings are not accepted (it gives a compile error).

This must be compatible with .NET Core, so .NET Framework solutions like SqlMethods.Like() and SqlFunctions.IsNumeric() are out.

Peet Brits
  • 2,911
  • 1
  • 31
  • 47
  • [This](https://stackoverflow.com/a/7581559/3793448) answer might be useful – Izzy Aug 27 '21 at 08:28
  • 1
    if p.Value is of numeric type remove the double quotes surrounding the numbers of your sample code. If p.Value is of string type however, you must parse it to Int as well. – oRoiDev Aug 27 '21 at 08:28
  • 1
    I think it depends which database provider you use. For Npgsql EF Core, you can use something like this: https://stackoverflow.com/a/66214435/6593177 – KJanek Aug 27 '21 at 08:36
  • 2
    .NET Core has no LINQ to SQL implementation at all (as in, `System.Data.Linq`), which has been deprecated for a long time even on Framework, so it's not quite clear what your question is (unless your question just mentions "SQL" for reference and no SQL is actually involved because you're using LINQ to Objects). Probably the most common LINQ provider on Core that handles SQL is Entity Framework Core, and that does have `Like` operations (`EF.Functions.Like`). The answer is necessarily provider-specific, since there is no single language construct that canonically maps to SQL this way. – Jeroen Mostert Aug 27 '21 at 09:31
  • I updated the question with some clarification. I'm looking for a LINQ statement that will be SQL optimised, and the data type is string, so non-numeric data is valid. – Peet Brits Aug 27 '21 at 10:13
  • @PeetBrits there's no `LIKE` for numbers in SQL. LIKE only works on text. When you write `like '100____'` you force the server to cast the numeric field into a text first. That's bad, because it means the server can't use any indexes to speed up the query and has to scan the entire table. Instead of `LIKE` you should use `BETWEEN` in SQL as well. The query will be 10K times faster at least - that's how many extra rows have to be scanned with 4 "wildcard" digits – Panagiotis Kanavos Aug 27 '21 at 10:18
  • @PeetBrits `the data type is string, so non-numeric data is valid` in that case why talk about numbers at all? You should update the question to make it clear the field is not numeric. You're performing a string search. Entity Framework does have a `Like` operator, although the actual method is different between EF Old and EF Core. Simple string operations like `text.Field.StartsWith(...)` are converted to `LIKE '...%`. What you want may be able to use indexes because it's similar a range search with some extra restrictions – Panagiotis Kanavos Aug 27 '21 at 10:23
  • @PanagiotisKanavos yes, my title was clumsily named... but I tried to explain everything in the question. I want to search a string data type for all numbers between "1000000" and "1009999". – Peet Brits Aug 27 '21 at 10:32
  • 2
    @PeetBrits as Jeroen Mostert explained you can use your ORM's `Like` function. LINQ itself isn't an ORM, it's a language used on top of other providers, like in-memory objects and ORMs. Are you using NHibernate? Entity Framework? EntityFramework Core? Have you tried using [EF.Functions.Like](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbfunctionsextensions.like?view=efcore-5.0) ? – Panagiotis Kanavos Aug 27 '21 at 10:34
  • `EF.Functions.Like` did the trick, thanks everyone. – Peet Brits Aug 27 '21 at 10:49

2 Answers2

1

you can use the Like method in the EF.Functions class

.Where(x => EF.Functions.Like(p.Value, SearchQuery + "%"));

The % will match any value following the SearchQuery value

You can do stuff like:

.Where(x => EF.Functions.Like(p.Value, "%" + SearchQuery + "%"));

.Where(x => EF.Functions.Like(p.Value, "%" SearchQuery));

thanzeel
  • 431
  • 4
  • 12
0

This worked.

.Where(p => EF.Functions.Like(p.Value, "100[0-9][0-9][0-9][0-9]"))

Thanks to all the advice in the comments!

Peet Brits
  • 2,911
  • 1
  • 31
  • 47
  • is there any EF funcion for `Convert` (or `cast`) sql builtin function? with a sql query, I'd go with a `where cast (myFiels as int)...` – Gian Paolo Aug 30 '21 at 06:00
  • 1
    @GianPaolo I do not know of any, but even if there is, the SQL query will break, because there are valid values that are not numbers. – Peet Brits Aug 30 '21 at 11:03