title: Full Text Search with LINQ
body: <p>I am trying to implement a search function where the results could be populated to a Gridview. I am using  LINQ to query my db in c#.</p>
  <p>The problem I am trying to figure out is, if the user want to search through multiple columns/fields with more than one search term, then how do I do that? So instance if I search a table with these 3 fields and I had some data like:
  &gt;     firstName | lastname | players#
  &gt;&gt;1. Michael | Jordan | 12
  &gt;&gt;2. Michael | Jordan | 24
  &gt;&gt;3. Michael | Jordan | 45
  &gt;&gt;4. DeAndre| Jordan | 6
  &gt;&gt;5. Jerome| Jordan | 44
  &gt;&gt;6. Jordan| Sparks| 88</p>
  <p>Now if I search for Jordan I get everything:</p>
  <p>If I search Michael Jordan than I should get row #s 1,2,3 back.</p>
  <p>If I search Jordan Sparks 88 than I should row # 6 back.</p>
  <p>So my problem is I don't know where in the table the search term may be so I must search all columns/fields. In my current code, I have something where I go through each column/field name and use the Contains() then || (&quot;or&quot;), but it only works for 1 search term.</p>
  <p>Is there an elegant and easy way to search and filter through a whole linq table so that the list is minimized? From there I will add that result to a datasource and then bind it.</p>
  <p>@Sphinxxx I am using a typical gridview. It looks like this:</p>
  <pre><code>    &lt;asp:GridView ID=&quot;GridView&quot; runat=&quot;server&quot; AllowSorting=&quot;True&quot; PagerStyle-Mode=&quot;NumericPages&quot;
                                  AutoGenerateColumns=&quot;false&quot; Width=&quot;100%&quot; CssClass=&quot;gridView&quot; OnPageIndexChanging=&quot;GridView_PageIndexChanging&quot;
                                  AllowPaging=&quot;True&quot;  DataKeyNames=&quot;idPlayersList&quot; OnRowCommand=&quot;GridView_RowCommand&quot;
                                  OnRowEditing=&quot;GridView_RowEditing&quot; OnRowCancelingEdit=&quot;GridView_CancelEditRow&quot;
                                  OnRowUpdating=&quot;GridView_UpdateRow&quot; OnRowDataBound=&quot;GridView_RowDataBound&quot;&gt;
                                  &lt;RowStyle CssClass=&quot;rowStyle&quot;&gt;&lt;/RowStyle&gt;
                                      &lt;asp:BoundField DataField=&quot;FirstName&quot; HeaderText=&quot;First Name&quot; SortExpression=&quot;FirstName&quot; /&gt;
                                      &lt;asp:BoundField DataField=&quot;LastName&quot; HeaderText=&quot;Last Name&quot; SortExpression=&quot;LastName&quot; /&gt;
  &lt;asp:BoundField DataField=&quot;PlayersNumber&quot; HeaderText=&quot;Players Number&quot; SortExpression=&quot;PlayersNumber&quot; /&gt;
  &lt;asp:TemplateField HeaderText=&quot;Team&quot; SortExpression=&quot;Team&quot;&gt;
                                      &lt;EditItemTemplate&gt;
                                          &lt;asp:DropDownList ID=&quot;ddlTeam&quot; runat=&quot;server&quot; CssClass=&quot;dropdown&quot; AutoPostBack=&quot;True&quot;
                                              AppendDataBoundItems=&quot;true&quot; DataTextField=&quot;TeamName&quot; DataValueField=&quot;idTeam&quot;&gt;
                                          &lt;/asp:DropDownList&gt;
                                      &lt;/EditItemTemplate&gt;
                                      &lt;ItemTemplate&gt;
                                          &lt;asp:Label ID=&quot;lblTeam&quot; runat=&quot;server&quot; Text='&lt;%# Bind(&quot;TeamName&quot;) %&gt;'&gt;&lt;/asp:Label&gt;
                                      &lt;/ItemTemplate&gt;
                                  &lt;/asp:TemplateField&gt;
  </code></pre>
  <p>My current search function looks something like this:</p>
  <pre><code>/**
  * This method is for button search functionality 
  * 
  */
  protected void btnSearch_Click(object sender, EventArgs e)
  {
  
      // Call to Entity Model Framework
      DBModel.DBEntities context = new DBModel.DBEntities();
      
      string[] searchTerms = txtSearch.Text.Trim().Split('&amp;');
  
  //Prepare to build a &quot;players&quot; query:
  IQueryable&lt;DBModel.playersList&gt; playersListQuery = context.playersLists;
  
  foreach (var term in searchTerms)
  {
      //Refine our query, one search term at a time:
      playersListQuery = playersListQuery.Where(p =&gt; p.isDeleted == false &amp;&amp; (p.FirstName.Contains(term.Trim()) ||
                                             p.LastName.Contains(term.Trim()) ||
                                             p.PlayersNumber.Contains(term.Trim()) ||
                                             p.Team.Name.Contains(term.Trim())));
  }
  
  //Now we have the complete query. Get the results from the database:
  var filteredplayersList = playersListQuery.Select(s =&gt; new
                                             {
  
                                                 idPlayersList = s.idPlayersList,
                                                 FirstName = s.FirstName,
                                                 LastName = s.LastName,
                                                 PlayersNumber  = s.PlayersNumber,
                                                 TeamName  = s.Team.Name
                                              }).ToList();
  
  
  GridView.DataSource = filteredplayersList; //Connecting query to the datasource Gridview
  GridView.DataBind();  //Binding Gridview
  }
  </code></pre>