Search for names in the database that matches whole parameters or any part of parameters
C# or VB.NET are welcome.
I'm writing a query to select all records th开发者_如何学Cat 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 ?
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();
精彩评论