Linq where 'not like' or ! <> equivalent in

Question

I've got this working :

Dim locations = From row In allLocations.AsEnumerable()                  
Where row.Field(Of String)("PartNr") Is Nothing
Select row

It gets all the locations where there is no partnumber. But i would like to do something like this to get everything except some record(s):

Dim locations = From row In allLocations.AsEnumerable()                  
Where row.Field(Of String)("PartNr") Not Like "12345"
Select row

i maybe better clarify, i am not really using a number but a regular expression like : "G[A-Z]*" for example — Tom Paredis, Aug 13 '15 at 13:29
It works for the string parts I define, like G in the example but not for the regular expression. example/regex: G[A-Z]* — Tom Paredis, Aug 13 '15 at 13:37

score 2 · Answer 1 · answered Aug 13 '15 at 13:23

2

A simple string contains (ie. equivalent to SQL's like '%substring%') can be done with String.Contains:

Dim locations = From row In allLocations.AsEnumerable()                  
               Where not row.Field(Of String)("PartNr").Contains("12345")
               Select row

answered Aug 13 '15 at 13:23

Richard

106,783
21
203
265

Sorry i forgot to say it's not really just a string but a regular expression. i just replied it to the original post to so my apologies for forgetting to clarify this in the op. – Tom Paredis Aug 13 '15 at 13:32
@TomParedis You'll need to apply some Google-Fu to looking up what is possible, which will certainly not include anything close to what .NET regular expressions are capable of (read the docs for `LIKE`: it is very limited matching). To directly use the server's `LIKE` operation you'll need to drop down to SQL I expect. – Richard Aug 13 '15 at 13:38
Ok i'll try to find the solution. Thanks for helping – Tom Paredis Aug 13 '15 at 13:41
If it's in a `DataTable` you may be able to leverage that too by using [`DataTable.Select`](https://msdn.microsoft.com/en-us/library/det4aw50(v=vs.110).aspx). – DavidG Aug 13 '15 at 13:42
@TomParedis depending on the row counts involved you could get all from the database and apply the regex in .NET (ie. switch from `IQueryable(Of T)` to `IEnumerable(Of T)` where you can use regex. – Richard Aug 13 '15 at 13:45
the row counts go up to 500.000, how would i go about changing from IQueryable to IEnumerable? – Tom Paredis Aug 13 '15 at 13:51
@TomParedis Use the `AsEnumerable()` function. – Richard Aug 13 '15 at 14:19
@Richard Thanks for helping me, I found a solution today using regular expressions. I've posted my solution as an answer too so anyone looking to solve this issue will be able to find my solution – Tom Paredis Aug 14 '15 at 09:27

score 0 · Accepted Answer · answered Aug 14 '15 at 08:38

I've found a solution for what i was looking for:

Dim rgx As New Regex("^G[A-Z]")
Dim alleLocaties = From row In beginQuery.AsEnumerable()
                   Where (Not rgx.IsMatch(row.Field(Of String)("Location"))

In this example i get all the locations that do not start with G followed by a letter.

Linq where 'not like' or ! <> equivalent in

2 Answers2