Searching a set of data with multiple terms using Linq

Question

I'm in the process of moving from ADO.NET to Linq. The application is a directory search program to look people up. The users are allowed to type the search criteria into a single textbox. They can separate each term with a space, or wrap a phrase in quotes such as "park place" to indicate that it is one term.

Behind the scenes the data comes from a XML file that has about 90,000 records in it and is about 65 megs. I load the data into a DataTable and then use the .Select method with a SQL query to perform the searches. The query I pass is built from the search terms the user passed. I split the string from the textbox into an array using a regular expression that will split everything into a separate element that has a space in it. However if there are quotes around a phrase, that becomes it's own element in the array. I then end up with a single dimension array with x number of elements, which I iterate over to build a long query.

I then build the search expression below:

      query = query & _
    "((userid LIKE '" & tempstr & "%') OR " & _
    "(nickname LIKE '" & tempstr & "%') OR " & _
    "(lastname LIKE '" & tempstr & "%') OR " & _
    "(firstname LIKE '" & tempstr & "%') OR " & _
    "(department LIKE '" & tempstr & "%') OR " & _
    "(telephoneNumber LIKE '" & tempstr & "%') OR " & _
    "(email LIKE '" & tempstr & "%') OR " & _
    "(Office LIKE '" & tempstr & "%'))"

Each term will have a set of the above query. If there is more than one term, I put an AND in between, and build another query like above with the next term. I'm not sure how to do this in Linq. So far, I've got the XML file loading correctly. I'm able to search it with specific criteria, but I'm not sure how to best implement the search over multiple terms.

'this works but far too simple to get the job done
    Dim results = From c In m_DataSet...<Users> _
    Where c.<userid>.Value = "XXXX" _
    Select c    

The above code also doesn't use the LIKE operator either. So partial matches don't work. It looks like what I'd want to use is the .Startswith but that appears to be only in Linq2SQL. Any guidance would be appreciated. I'm new to Linq, so I might be missing a simple way to do this.

The XML file looks like so:

<?xml version="1.0" standalone="yes"?>
<theusers>

<Users>
<userid>person1</userid>
<nickname></nickname>
<lastname></lastname>
<firstname></firstname>
<department></department>
<telephoneNumber></telephoneNumber>
<email></email>
</Users>

<Users>
<userid>person2</userid>
<nickname></nickname>
<lastname></lastname>
<firstname></firstname>
<department></department>
<telephoneNumber></telephoneNumber>
<email></email>
</Users>

######## UPDATE ######## Below is the full working solution in VB thanks to our kind answerer.

---

Here is the query you would run:

Dim query = From d In m_DataSet.Descendants("Users") _
                Where d.ChildrenBeginWith(rezsplit) _
                Select d     

---

Here is the extension method:

Public Module SearchEngine
<System.Runtime.CompilerServices.Extension()> _
Public Function ChildrenBeginWith(ByVal parent As XElement, _ 
  ByVal ParamArray     searchTerms As String()) As Boolean
  Dim ret As Boolean = False
      Dim children = parent.Elements().ToList()
      For Each searchTerm In searchTerms
          ret = children.Any(Function(x) x.Value.StartsWith(searchTerm))
          If Not ret Then
              Exit For
          End If
      Next
      Return ret
  End Function
End Module

Answer 1

If you just want something that works with linq-to-xml you can load your xml into an XDocument and execute the following query. It will include any child node values that begin with the specified text.

Dim doc = XDocument.Parse("this is where your xml string goes")
Dim query = From d In doc.Descendants("Users") _
            Where d.Elements().Any(Function(x As XElement) x.Value.StartsWith(tempStr)) _
            Select d
For Each A In query
  //Do Something
Next

Edit: Sorry I am not a VB guy, so the following sample is c# (i originally wrote my first answer in c# but it was pretty easy for me to convert to vb). I don't know of a native way to cleanly do what you want, so the easiest way might be a helper method or an extension method like the following:

New Query:

var query = from d in doc.Descendants("Users")
            where d.ChildrenBeginWith(tempStr, tempStr2)
            select d;

Extension Method:

public static class Extension
{
    public static bool ChildrenBeginWith(this XElement parent, params string[] searchTerms)
    {
        bool ret = false;
        var children = parent.Elements().ToList();
        foreach (var searchTerm in searchTerms)
        {
            ret = children.Any(x => x.Value.StartsWith(searchTerm));
            if (!ret)
                break;
        }
        return ret;
    }
}