I've got a table with FirstName and LastName and a query that looks somewhat like this:
var TheQuery = (from c in MyDC.Contacts where (c.FirstName.Contains(TheSearchParameter) || c.LastName.Contains(TheSearchParameter)) select c.ColumnID).Distinct().ToList(); If a user searches for John or for Smith, there'll be records returned but if the user searches for John Smith, no records come back. How does Contains() work and what would I need to change in my query for it to work as expected?
Thanks.
13 Answers
Answers 1
Rather than start playing with combinations of and, or, StartsWith, EndsWith etc., let analyze the main issue:
Search query using
.Contains()for parameters that combine two columns
So the general answer is:
where Combine(table.Column1, table.Column2).Contains(TheSearchParameter) The question is though what is the Combine function, and there is no general answer to that.
In your particular case, looks like you want to search for a Name which is combination of FirstName and LastName columns.
Even that combination is not generally defined (different languages have different name rules), but assuming you have in mind the most common name combinator:
Name = "{FirstName} {LastName}" then the query is simply
var TheQuery = (from c in MyDC.Contacts where (c.FirstName + " " + c.LastName).Contains(TheSearchParameter) select c.ColumnID).Distinct().ToList(); For instance, if there is a Contact with FirstName: "John", LastName:"Smith", the above query will match "John", "Smith", "John Smith" but not "Smith John".
Answers 2
You might consider adding StartsWith method in your LINQ expression. StartsWith works similar to SQL where Column LIKE 'Something%'. You might add EndsWith also to make your search more wide
var TheQuery = (from c in MyDC.Contacts where (c.FirstName.Contains(TheSearchParameter) || c.FirstName.StartsWith(TheSearchParameter) || c.FirstName.EndsWith(TheSearchParameter) || c.LastName.StartsWith(TheSearchParameter) || c.LastName.EndsWith(TheSearchParameter) || c.LastName.Contains(TheSearchParameter)) select c.ColumnID).Distinct().ToList(); Answers 3
var TheQuery = (from c in MyDC.Contacts where ((c.FirstName.Contains(TheSearchParameter) && c.LastName.Contains(TheSearchParameter)) || (c.FirstName.Contains(TheSearchParameter) || c.LastName.Contains(TheSearchParameter))) select c.ColumnID).Distinct().ToList(); Using ( condition && condition ) || Will force the internals of the brackets to be a single expression while leaving the existing conditions in place.
Let me know if not. Hope this helps.
Answers 4
It is my current understanding that you want to search for words across columns. While I'm sure I do not yet fully understand the requirements I want to contribute the following idea. Hopefully, you'll be able to make it into a full solution:
var words = searchQuery.Split(' '); var query = (from c in MyDC.Contacts select c); //Force each word to occur. foreach (var w in words) query = query.Where(c => c.FN.Contains(w) || c.LN.Contains(w)); var TheQuery = (from c in query select c.ColumnID).Distinct().ToList(); This introduces the concept of searching for all words as opposed to searching for only one string.
If you want separate logic for first name and last name you could have two lists of words: One for FN and one for LN.
Answers 5
You can split the TheSearchParameter with the spaces and then you can look for the matching names. The following code includes your the query with modification. The output is ColumnID 1, which has a matching name.
internal class Program { private static void Main(string[] args) { List<Contact> contacts = new List<Contact>() { new Contact() { ColumnID=2, FirstName = "Peter", LastName="Jackson"}, new Contact() { ColumnID=1, FirstName = "Jhon", LastName="Smith"}, new Contact() { ColumnID=3, FirstName = "Venu", LastName="Prasad"}, new Contact() { ColumnID=4, FirstName = "Patrick", LastName="Jane"}, }; string TheSearchParameter = "Jhon Smith"; var result = (from c in contacts where (TheSearchParameter.Split(' ').Contains(c.FirstName) || TheSearchParameter.Split(' ').Contains(c.LastName)) select c.ColumnID).Distinct().ToList(); foreach (var item in result) { Console.WriteLine(item); } Console.ReadKey(); } public class Contact { public int ColumnID { get; set; } public string FirstName { get; set; } public string LastName { get; set; } } } Answers 6
Here you go (I prefer to use lambdas, but you don't have to):
var results = MyDC.Contacts .Where(c => string.Join(" ",c.FirstName,c.LastName).Contains(TheSearchParameter)) .Select(c => c.ColumnId) .Distinct() .ToList(); By combining FirstName and LastName into a single string, you save yourself several comparisons.
Edit: Updated the formatting since it was kind of hard to follow on a single line.
Answers 7
How does
Containswork
Assuming your FirstName and LastName are of string type, Contains will check if any of the substring of the string matches with your TheSearchParameter
Thus, c.FirstName.Contains("John") will match if the c.FirstName is any of the following:
John vbJohn John123 vbJohn123 It is case sensitive though, thus the following won't match:
john vbjohn john123 vbjohn123 what would I need to change in my query for it to work as expected
Assuming if you have the following name list:
John Bryant John Bard Smith Bard John Smith Ralla Smith Smith John Metajohn Rasmith And I assume the most natural "search" that you probably want, that is,
- the more items you add as the key word, the less items you should get ("John"
TheSearchParametershould return more results than "John Smith"). - The order doesn't matter.
- Exact matching required (since you deal with name)
Thus, by putting "John" you want to return:
John Bryant John Bard John Smith Smith John And by putting "Smith"
Smith Bard John Smith Ralla Smith Smith John While putting "John Smith"
John Smith Smith John What I suggest you to do is by making it case insensitive and checking it like this:
string TheSearchParameter = "John Smith"; TheSearchParameter = TheSearchParameter.ToLower(); //case insensitive string[] pars = TheSearchParameter.Split(new char[] {' '}, StringSplitOptions.RemoveEmptyEntries); //to get all the pars var TheQuery = (from c in MyDC.Contacts let l = new List<string>() {c.FirstName.ToLower(), c.LastName.ToLower()} where l.Except(pars).Count() <= l.Count - pars.Length select c.ColumnID).Distinct().ToList(); The key here is the
where l.Except(pars).Count() <= l.Count - pars.Length By which you control the number of matches that you want to consider as true matching. The more words you use in SearchParameter, the less amount of l.Except which you expected to have (because more words in the name have to match in the search parameters).
Answers 8
Good day, please consider my approach
both answers above will not return if searching for full name because they searched exclusively with FirstName and LastName
(ex: John.Contains(John Smith) = false
Smith.Contains(John Smith) = false
John.StartsWith(John Smith) = false
Smith.StartsWith(John Smith) = false
John.EndsWith(John Smith) = false
Smith.EndsWith(John Smith) = false
)
var TheQuery = (from c in x where ((TheSearchParameter.Contains(c.FirstName) && TheSearchParameter.Contains(c.LastName)) || ((c.FirstName.Contains(TheSearchParameter) || c.LastName.Contains(TheSearchParameter)))) select c.ColumnId).Distinct().ToList(); The First Part where the TheSearchParameter.Contains(c.First/LastName) will consider searching for TheSearchParameter with parts of the first AND last name
The Second Part Will Consider the searching for First OR Last Name with TheSearchParameter
if you search "John Smith"
and have John Smith, John Paul and Paul Smith records
The First part will return 1 result: John Smith The Second Part will return 0
if you search "John"
The First part will return 0 result The Second Part will return: John Smith and John Paul
if you search "Smith"
The First Part will return 0 The Second Part will return: John Smith and Paul Smith
Thank You!
Answers 9
As you said, Let's assume that the Collection contains a record with FirstName as John and LastName as Smith and There is another record with FirstName='Sam' and LastName as 'SmithX'. And The TheSearchParameter will contains the word to search.
Now Let's examine How your query will work up on different inputs.
Case 1 : TheSearchParameter= 'John' - Will give you the expected result since c.FirstName.Contains(TheSearchParameter) evaluates to true
Case 2 : TheSearchParameter= 'Smith' - Will give you the expected result since c.LastName.Contains(TheSearchParameter) evaluates to true
Case 3 : *TheSearchParameter= 'Jo'*Will give you the expected result since c.FirstName.Contains(TheSearchParameter) evaluates to true
Upto this everything is fine; Now
Case 4 : TheSearchParameter= 'John Smith' - Will give you Empty result set Since there is no FirstName or LastName that Contains John Smith But it is a combination of both.
What you can do to get the correct result in all these cases:
Since the TheSearchParameter may include firstName or LastName or Both, Perform the search operation in TheSearchParameter. So your Query May Looks like:
var TheQuery = (from c in MyDC.Contacts where (TheSearchParameter.Contains(c.FirstName) || TheSearchParameter.Contains(c.LastName)) select c.ColumnID).Distinct().ToList(); Now, It Gives the same result for Case 1-3 And Will Give Two Records("John Smith" and "Sam Smith" Even though the last name is Different. since TheSearchParameter.Contains(c.LastName) Is evaluates to true; Now We have to Sort-out this;
So the fact is that
If the input is Combination of FirstName and LastName We have To Confirm that The combination Should Start with FirstName and EndsWith LastName. So I Suggest you to Use such a Searching mechanism. The following snippet will Help you to do that;
var TheQuery = (from c in MyDC.Contacts where ((TheSearchParameter.Contains(c.FirstName) && TheSearchParameter.StartsWith(c.FirstName)) || (TheSearchParameter.Contains(c.LastName) && TheSearchParameter.EndsWith(c.FirstName))) select c.ColumnID).Distinct().ToList(); Answers 10
If we assume that the name is compose of first name and last name only, and you have no middle name in your data. User Full Name = First Name + Last Name. Maybe the follow code you can try:
var TheQuery = (from c in MyDC.Contacts where (c.FirstName + " " + c.LastName).Contains(TheSearchParameter) select c.ColumnID).Distinct().ToList();we check
FirstNameandLastNameinTheSearchParameter:var TheQuery = (from c in MyDC.Contacts where (c.FirstName.Contains(TheSearchParameter) || c.LastName.Contains(TheSearchParameter) || TheSearchParameter.StartsWith(c.FirstName) || TheSearchParameter.EndsWith(c.LastName) ) select c.ColumnID).Distinct().ToList();e.g.: Jhon Smith
"Jhon Smith".StartsWith("Jhon")or"Jhon Smith".EndsWith("Smith")is true when Jhon or Smith is inContacts.
Answers 11
string[] parametersArray = null; int parametersCount = 0; if(!string.IsNullOrEmpty(TheSearchParameter)) { parametersArray = TheSearchParameter.Split(new char[0],StringSplitOptions.RemoveEmptyEntries); parametersCount = parametersArray.Length; } var TheQuery = (from c in MyDC.Contacts where (parametersCount == 0 || (parametersCount > 0 && (parametersArray.Contains(c.FirstName) || parametersArray.Contains(c.LastName)))) select c.ColumnID).Distinct().ToList(); Answers 12
Have you tried this?
var TheQuery = (from c in MyDC.Contacts where (System.Threading.Thread.CurrentThread.CurrentCulture.CompareInfo.IndexOf(c.FirstName, TheSearchParameter, System.Globalization.CompareOptions.IgnoreCase) >= 0 || System.Threading.Thread.CurrentThread.CurrentCulture.CompareInfo.IndexOf(c.Lastname, TheSearchParameter, System.Globalization.CompareOptions.IgnoreCase) >= 0) select c.ColumnID).Distinct().ToList(); Answers 13
I do not quite understand why are you expecting your statement to work.
string x = "abc" ; string y = "abc def"; y.Contains(x); // returns true x.Contains(y); // returns false 
0 comments:
Post a Comment