Search for names in the database that matches whole parameters or any part of parameters

Question

C# or VB.NET are welcome.

I'm writing a query to select all records that has any part of parameter.

I have one table called Employees. Some people have name like this: John David Clark

If the parameter is

• "John"
• "John David"
• "David John"
• "John Clark"
• "Clark John"

I should be able to get result back as long as there's a match in the parameters.

If I use Function Contains (q.FirstName & " " & q.LastName).Contains(employeeName), I will not get any result back if employeeName is "John Clark" Function Contains looks only for next words from left to right. It doesn't match a single word at a time.

So that's why I used this in the Linq to SQL:

        Dim employeeName As String
        query = From q In db.Employees _
                Where (q.FirstName & " " & q.LastName).Split(CChar(" ")).Intersect(employeeName.Split(CChar(" "))).Any _
                Select q

I got the following error:

Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator

Is there another way that I can query for FirstName and LastName that has any part of parameter ?

Answer 1

This will find those employees that have both first name and last name match to the search string:

        var searchText = "John David Clark";
        var tokens = searchText.Split(null);

        var result = db.Employees
            .Where(arg => tokens.Contains(arg.FirstName) && tokens.Contains(arg.LastName))
            .Select(arg => new { arg.LastName, arg.FirstName })
            .ToList();

This will find those employees that have last or first name match to any name in the search string:

        var searchText = "John David Clark";
        var tokens = searchText.Split(null);

        var result = db.Employees
            .Where(arg => tokens.Contains(arg.FirstName) || tokens.Contains(arg.LastName))
            .Select(arg => new { arg.LastName, arg.FirstName })
            .ToList();