Noob_Coder Noob_Coder - 24 days ago 6
Vb.net Question

VB.Net LINQ SqlMethods.Like (and .Contains) fails

Noobie coder here. I am trying to learn more about LINQ ("more" meaning more than the nothing I know about it now).

I have a very simple program right now. This is just for testing/learning.

I have a Class call Person. It holds first name, last name and middle name (optional). I have a form-wide SortedDictionary called oPeople. oPeople is a SortedDictionary(Of Integer, Person). On Form_Load, I call a procedure that loads a list of 65 people. Right now this is hard-coded but eventually I'll be grabbing it from a database. But right now I hard code 65 first/last/one middle name along with an integer as the key to replicate what will be the primary key when it comes from the database.

Once the form is up, I have a Textbox (txtSearchForName). Right now I would like the user to be able to enter a search term and have the system look through the LastName for a full or partial match (case insensitive). Eventually I would like to be able to search for comparisons between FirstName, LastName and MiddleName (if there is one).

At this point all I want to do is loop through the results of the LINQ query and output them to the console window. So here is the code I have for just looping through a LINQ query with no conditions (so all names in the SortedDictionary). This part does work (or at least it appears to work). I have gone through the 65 entries and they do appear in the order that they are initially entered.

I'm going to include the collection of person's data so if it's needed you'll have it. Also I'm including the Class "Person."

Here's the class:

Public Class Person


Private _fnm As String = String.Empty
Public Property FirstName() As String

Get

Return _fnm

End Get

Set(ByVal value As String)

_fnm = value.Trim

End Set

End Property

Private _lnm As String = String.Empty
Public Property LastName() As String

Get

Return _lnm

End Get

Set(ByVal value As String)

_lnm = value.Trim

End Set

End Property

Private _mnm As String = String.Empty
Public Property MiddleName() As String

Get

Return _mnm

End Get

Set(ByVal value As String)

_mnm = value.Trim

End Set

End Property



Public Sub New()

End Sub

Public Sub New(ByVal firstName As String,
ByVal lastName As String,
Optional ByVal middleName As String = "")

_fnm = firstName
_lnm = lastName
_mnm = middleName

End Sub


End Class

Here is the list of people I'm adding (sorry for how long it is - but I wanted the data available so you'll see what I'm looking for with regards to the search):

Private Sub FillPeopleDictionary()

Try

If oPeople.Count > 0 Then oPeople.Clear()

Dim oNewPerson As Person = Nothing

oNewPerson = New Person("Scarlett", "Johansson")

oPeople.Add(1, oNewPerson)

oNewPerson = New Person("Amy", "Adams")

oPeople.Add(2, oNewPerson)

oNewPerson = New Person("Jessica", "Biel")

oPeople.Add(3, oNewPerson)

oNewPerson = New Person("Meghan", "Markle")

oPeople.Add(4, oNewPerson)

oNewPerson = New Person("Zoe", "Saldana")

oPeople.Add(5, oNewPerson)

oNewPerson = New Person("Kate", "Upton")

oPeople.Add(6, oNewPerson)

oNewPerson = New Person("Odette", "Annable")

oPeople.Add(7, oNewPerson)

oNewPerson = New Person("Abigail", "Spencer")

oPeople.Add(8, oNewPerson)

oNewPerson = New Person("Lea", "Michele")

oPeople.Add(9, oNewPerson)

oNewPerson = New Person("Kerry", "Washington")

oPeople.Add(10, oNewPerson)

oNewPerson = New Person("Keira", "Knightly")

oPeople.Add(11, oNewPerson)

oNewPerson = New Person("Nathalia", "Hasapole")

oPeople.Add(12, oNewPerson)

oNewPerson = New Person("Gal", "Gadot")

oPeople.Add(13, oNewPerson)

oNewPerson = New Person("Margo", "Robbie")

oPeople.Add(14, oNewPerson)

oNewPerson = New Person("Ashley", "Greene")

oPeople.Add(15, oNewPerson)

oNewPerson = New Person("Chyler", "Leigh")

oPeople.Add(16, oNewPerson)

oNewPerson = New Person("Emily", "Wickersham")

oPeople.Add(17, oNewPerson)

oNewPerson = New Person("Jennifer", "Morrison")

oPeople.Add(18, oNewPerson)

oNewPerson = New Person("Penélope", "Cruz")

oPeople.Add(19, oNewPerson)

oNewPerson = New Person("Jennifer", "Esposito")

oPeople.Add(20, oNewPerson)

oNewPerson = New Person("Kaley", "Cuoco")

oPeople.Add(21, oNewPerson)

oNewPerson = New Person("Kate", "Mara")

oPeople.Add(22, oNewPerson)

oNewPerson = New Person("Diane", "Kruger")

oPeople.Add(23, oNewPerson)

oNewPerson = New Person("Rachel", "Bilson")

oPeople.Add(24, oNewPerson)

oNewPerson = New Person("Emily", "Blunt")

oPeople.Add(25, oNewPerson)

oNewPerson = New Person("Rosario", "Dawson")

oPeople.Add(26, oNewPerson)

oNewPerson = New Person("Shannon", "Sossamon")

oPeople.Add(27, oNewPerson)

oNewPerson = New Person("Yvonne", "Strahovski")

oPeople.Add(28, oNewPerson)

oNewPerson = New Person("Zhang", "Zilin")

oPeople.Add(29, oNewPerson)

oNewPerson = New Person("Sibel", "Kekilli")

oPeople.Add(30, oNewPerson)

oNewPerson = New Person("Ali", "Larter")

oPeople.Add(31, oNewPerson)

oNewPerson = New Person("Wei", "Tang")

oPeople.Add(32, oNewPerson)

oNewPerson = New Person("Claire", "Forlani")

oPeople.Add(33, oNewPerson)

oNewPerson = New Person("Gabrille", "Union")

oPeople.Add(34, oNewPerson)

oNewPerson = New Person("Mary", "Winstead", "Elizabeth")

oPeople.Add(35, oNewPerson)

oNewPerson = New Person("Teresa", "Palmer")

oPeople.Add(36, oNewPerson)

oNewPerson = New Person("Amy", "Acker")

oPeople.Add(37, oNewPerson)

oNewPerson = New Person("Liu", "Yifei")

oPeople.Add(38, oNewPerson)

oNewPerson = New Person("Sarah", "Shahi")

oPeople.Add(39, oNewPerson)

oNewPerson = New Person("Marian", "Rivera")

oPeople.Add(40, oNewPerson)

oNewPerson = New Person("Amy", "Smart")

oPeople.Add(41, oNewPerson)

oNewPerson = New Person("Jennifer", "Connelly")

oPeople.Add(42, oNewPerson)

oNewPerson = New Person("Chloe", "Bennet")

oPeople.Add(43, oNewPerson)

oNewPerson = New Person("Kylie", "Bunbury")

oPeople.Add(44, oNewPerson)

oNewPerson = New Person("Paula", "Patton")

oPeople.Add(45, oNewPerson)

oNewPerson = New Person("Jessica", "Alba")

oPeople.Add(46, oNewPerson)

oNewPerson = New Person("Dia", "Mirza")

oPeople.Add(47, oNewPerson)

oNewPerson = New Person("Kate", "Hudson")

oPeople.Add(48, oNewPerson)

oNewPerson = New Person("Roselyn", "Sanchez")

oPeople.Add(49, oNewPerson)

oNewPerson = New Person("Thandie", "Newton")

oPeople.Add(50, oNewPerson)

oNewPerson = New Person("Aubrey", "Plaza")

oPeople.Add(51, oNewPerson)

oNewPerson = New Person("Salma", "Hayak")

oPeople.Add(52, oNewPerson)

oNewPerson = New Person("Milana", "Vayntrub")

oPeople.Add(53, oNewPerson)

oNewPerson = New Person("Eva", "Mendez")

oPeople.Add(54, oNewPerson)

oNewPerson = New Person("Rashida", "Jones")

oPeople.Add(55, oNewPerson)

oNewPerson = New Person("Melissa", "Benoist")

oPeople.Add(56, oNewPerson)

oNewPerson = New Person("Olivia", "Munn")

oPeople.Add(57, oNewPerson)

oNewPerson = New Person("Camilla", "Belle")

oPeople.Add(58, oNewPerson)

oNewPerson = New Person("Emmy", "Rossum")

oPeople.Add(59, oNewPerson)

oNewPerson = New Person("Dakota", "Fanning")

oPeople.Add(60, oNewPerson)

oNewPerson = New Person("Morena", "Baccarin")

oPeople.Add(61, oNewPerson)

oNewPerson = New Person("Brooklyn", "Decker")

oPeople.Add(62, oNewPerson)

oNewPerson = New Person("Elisha", "Cuthbert")

oPeople.Add(63, oNewPerson)

oNewPerson = New Person("Maggie", "Lawson")

oPeople.Add(64, oNewPerson)

oNewPerson = New Person("Emily", "VanCamp")

oPeople.Add(65, oNewPerson)

Catch ex As Exception

MessageBox.Show(ex.Message, "Error [FillPeopleDictionary]", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub


Here is the code for the button click event that will search the oPeople collection:

Private Sub btnSearchForName_Click(sender As Object, e As EventArgs) Handles btnSearchForName.Click

Try

If String.IsNullOrEmpty(txtSearchForName.Text.Trim) Then

MessageBox.Show("hey dummy, i need something to search for... ",
"no search string",
MessageBoxButtons.OK,
MessageBoxIcon.Information)

txtSearchForName.Focus()

Return

Else

Dim sSearchTerm As String = txtSearchForName.Text.Trim.ToLower

Dim queryResults = From person In oPeople
'Where SqlMethods.Like(person.Value.LastName.ToLower, "%" & sSearchTerm & "%")
'Where person.Value.LastName.ToLower.Contains("%" & sSearchTerm & "%")

Console.WriteLine("search term: " & sSearchTerm &
Environment.NewLine & Environment.NewLine &
"queryResults.Count: " & queryResults.Count.ToString &
Environment.NewLine)

For Each result In queryResults

If Not String.IsNullOrEmpty(result.Value.MiddleName) Then

Console.WriteLine(result.Key.ToString.PadLeft(2, "0") & ": " & result.Value.FirstName & " " & result.Value.MiddleName & " " & result.Value.LastName)

Else

Console.WriteLine(result.Key.ToString.PadLeft(2, "0") & ": " & result.Value.FirstName & " " & result.Value.LastName)

End If

Next

End If

Catch ex As Exception

MessageBox.Show(ex.Message, "Error [btnSearchForName]", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub


The button code as it is right there works fine. There are no conditions on the LINQ query so it loops through and correctly lists all of the persons in the oPeople collection. There are two Where clauses commented out below the initial queryResults statement. Those are the two ways I was trying to add a Where clause. One approach was to use .Contains and the other was to use .Like.

Neither works.

I included the list of Persons so that I could explain what I am looking at attempting. Given that list, if the user were to type "mar", I would hope to get back a list of 6 people (case insensitive):

Meghan Markle

Margo Robbie

Kate Mara

Mary Elizabeth Winstead

Marian Rivera

Amy Smart

Now of course that is searching on FirstName and LastName. Right now I am just trying to get LastName to work. With only the LastName the list would only be:

Meghan Markle

Kate Mara

Amy Smart

Can anyone see what I am doing wrong here? Or should I scrap the idea of using LINQ with a SortedDictionary?

Again, I apologize for such a long post. I was not sure what information would be needed/useful and would also hopefully make it easier for you to spot where I am screwing up. So I opted for more information...

Thank you in advance for any help/suggestions you can offer.

Answer

Change your Person class to include a PersonId and pass that through like oNewPerson = New Person(1, "Scarlett", "Johansson").

Change the oPeople to be a List(Of Person) so when adding it would look like this oPeople.Add(oNewPerson).

Your LINQ statement would then look like this:

Dim queryResults = From person In oPeople
                   Where person.FirstName.ToLower Like "*" & sSearchTerm & "*" Or
                         person.LastName.ToLower Like "*" & sSearchTerm & "*"

You would also want to change the rest as no longer using a dictionary:

For Each result In queryResults

    If Not String.IsNullOrEmpty(result.MiddleName) Then

        Console.WriteLine(result.PersonId.ToString.PadLeft(2, CChar("0")) & ": " & result.FirstName & " " & result.MiddleName & " " & result.LastName)

    Else

        Console.WriteLine(result.PersonId.ToString.PadLeft(2, CChar("0")) & ": " & result.FirstName & " " & result.LastName)

    End If

Next

Hope this helps.