Noob_Coder Noob_Coder - 20 days ago 4
Vb.net Question

LINQ SqlMethods.Like (and .Contains) fails

I have a class called

Person
which has the properties
FirstName
,
LastName
and
MiddleName
and I have a form-wide SortedDictionary(Of Integer, Person) called
oPeople
.

On
Form_Load
, I call a method that loads a list of 65 people. Right now this is hard-coded but eventually I'll be grabbing it from a database.

Once the form is loaded, I have a
TextBox
called
txtSearchForName
for the user to enter a search term and have the system look through
oPeople
filtering on
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.

Here's the
Person
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


This is the method I'm using to add people. I'm adding 65 people but have cut the code down:

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)

Catch ex As Exception

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

End Try

End Sub


This is my LINQ statement followed by the output to console which is called when the user clicks a button:

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


The LINQ statement works as it stands, with no conditions, so it loops through and correctly lists all of the people 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 filter. One approach was to use
.Contains
and the other was to use
.Like
however neither works.

If the user was to type "mar", I would hope to get back a list of 6 people from the list of 65(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?

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.