A search query using .Contains () for parameters that combine two columns

Question

A search query using .Contains () for parameters that combine two columns

I have a table with FirstName and LastName and a query that looks something like this:

var TheQuery = (from c in MyDC.Contacts
                where (c.FirstName.Contains(TheSearchParameter)  ||
                       c.LastName.Contains(TheSearchParameter))
                select c.ColumnID).Distinct().ToList();

If the user searches for John or Smith, records will be returned, but if the user searches for John Smith, no records are returned. How does it work Contains()and what do I need to change in my request so that it works as expected?

+5

c # linq linq-to-sql

frenchie Mar 01 '16 at 0:35

source share

13 answers

Ivan Stoev · Answer 1 · 2016-03-13T19:01:14+0000

Rather than start playing with combinations and, or, StartsWith, EndsWithand so on, analyze the underlying problem:

.Contains() ,

, :

where Combine(table.Column1, table.Column2).Contains(TheSearchParameter)

, Combine, .

, , Name, FirstName LastName.

( ), , :

Name = "{FirstName} {LastName}"

var TheQuery = (from c in MyDC.Contacts
                where (c.FirstName + " " + c.LastName).Contains(TheSearchParameter)
                select c.ColumnID).Distinct().ToList();

, FirstName: "", LastName: "", "", "", " ", " ".

Shyju · Answer 2 · 2016-03-01T00:38:58+0000

, StartsWith LINQ. StartsWith SQL where Column LIKE 'Something%'. EndsWith, .

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();

Gabe · Answer 3 · 2016-03-01T00:43:28+0000

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();

( condition && condition ) || , .

, . , .

usr · Answer 4 · 2016-03-13T11:09:55+0000

, . , , . , :

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();

.

, : FN LN.

Venu prasad H S · Answer 5 · 2016-03-16T18:00:01+0000

, . - , . , 1. . :

    var TheQuery = (from c in MyDC.Contacts
                    where (TheSearchParameter.Split(' ').Any(s => c.FirstName.Contains(s)) ||
                    TheSearchParameter.Split(' ').Any(s => c.LastName.Contains(s)))
                    select c.ColumnID).Distinct().ToList();

var TheQuery = (from c in MyDC.Contacts
      where (c.FirstName + " " + c.LastName == TheSearchParameter)
      select c.ColumnID).Distinct().ToList();

Algemist · Answer 6 · 2016-03-17T14:54:32+0000

( , ):

var results = MyDC.Contacts
  .Where(c => string.Join(" ",c.FirstName,c.LastName).Contains(TheSearchParameter))
  .Select(c => c.ColumnId)
  .Distinct()
  .ToList();

FirstName LastName , .

. , .

Ian · Answer 7 · 2016-03-17T17:43:43+0000

Contains

, FirstName LastName string, Contains , - TheSearchParameter

, c.FirstName.Contains("John") , c.FirstName - :

John
vbJohn
John123
vbJohn123

, , , :

John
vbJohn
John123
vbJohn123

,

, :

John Bryant
John Bard
Smith Bard
John Smith
Ralla Smith
Smith John
Metajohn Rasmith

"", , , ,

, ( "" TheSearchParameter , " " ).
.
( )

, "", :

John Bryant
John Bard
John Smith
Smith John

, ""

Smith Bard
John Smith
Ralla Smith
Smith John

"John Smith"

John Smith
Smith John

, :

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();

where l.Except(pars).Count() <= l.Count - pars.Length

, . SearchParameter, l.Except, ( ).

Gabriel Llorico · Answer 8 · 2016-03-01T03:15:33+0000

,

, FirstName LastName

(.:

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();

, TheSearchParameter.Contains(c.First/LastName) TheSearchParameter

TheSearchParameter

" "

,

1 : 0

""

0 :

""

0 :

sujith karivelil · Answer 9 · 2016-03-15T05:42:42+0000

, , FirstName John LastName Smith, FirstName = 'Sam' LastName 'Smi'. TheSearchParameter .

, .

1: TheSearchParameter = 'John' - , c.FirstName.Contains(TheSearchParameter) true

2: TheSearchParameter = 'Smith' - , c.LastName.Contains(TheSearchParameter) true

3: * TheSearchParameter = 'Jo' * , c.FirstName.Contains(TheSearchParameter) true

;

4: TheSearchParameter = 'John Smith' - . FirstName LastName, John Smith, .

, :

TheSearchParameter firstName LastName Both, TheSearchParameter. , :

var TheQuery = (from c in MyDC.Contacts
                where (TheSearchParameter.Contains(c.FirstName)  ||
                       TheSearchParameter.Contains(c.LastName))
                select c.ColumnID).Distinct().ToList();

1-3 ( " " " ". , TheSearchParameter.Contains(c.LastName) , ;

, ,

FirstName LastName, , FirstName EndsWith LastName. . :

  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();

Mitchell Chu · Answer 10 · 2016-03-16T15:43:05+0000

, , . = . , :

var TheQuery = (from c in MyDC.Contacts
            where (c.FirstName + " " + c.LastName).Contains(TheSearchParameter)
            select c.ColumnID).Distinct().ToList();

FirstName LastName TheSearchParameter:

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();

: Jhon Smith "Jhon Smith".StartsWith("Jhon") "Jhon Smith".EndsWith("Smith") , Jhon Smith Contacts.

Sivanantham Padikkasu · Answer 11 · 2016-03-17T11:40:06+0000

        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();

Ehsan Majdabadi · Answer 12 · 2016-03-19T14:34:20+0000

?

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();

Shashwat Gupta · Answer 13 · 2016-03-01T01:33:13+0000

, , .

string x =  "abc" ;
string y = "abc def";

y.Contains(x); // returns true
x.Contains(y); // returns false

A search query using .Contains () for parameters that combine two columns

More articles: