Full Text Search with LINQ

Question

Hi and thanks in advance

I am trying to implement a search function in which the results can be populated in a gridview. I am using LINQ to query my db in C #.

The problem I'm trying to figure out is that if the user wants to search across multiple columns / fields with multiple search terms, then how can I do this? So, instance, if I look for a table with these three fields, and I had some data like:

firstName | lastname | players# 

• Michael | Jordan | 12
• Michael | Jordan | 24
• Michael | Jordan | 45
• DeAndre | Jordan | 6
• Jerome | Jordan | 44
• Jordan | Sparks | 88

Now, if I search for Jordan, I get everything:

If I'm looking for Michael Jordan, I should get line number 1,2,3 back.

If I'm looking for Jordan Sparks 88, then I have to translate No. 6 back.

So my problem is that I don’t know where the search term may exist in the table, so I have to search all the columns / fields. In my current code, I have something where I look at each column / field name and use Contains (), then || ("or"), but it only works for 1 search query.

Is there an elegant and easy way to search and filter through the entire linq table so that the list is minimized? From there I will add this result to the data source, and then bind it.

@Sphinxxx I use a typical gridview. It looks like this:

  <asp:GridView ID="GridView" runat="server" AllowSorting="True" PagerStyle-Mode="NumericPages" AutoGenerateColumns="false" Width="100%" CssClass="gridView" OnPageIndexChanging="GridView_PageIndexChanging" AllowPaging="True" DataKeyNames="idPlayersList" OnRowCommand="GridView_RowCommand" OnRowEditing="GridView_RowEditing" OnRowCancelingEdit="GridView_CancelEditRow" OnRowUpdating="GridView_UpdateRow" OnRowDataBound="GridView_RowDataBound"> <RowStyle CssClass="rowStyle"></RowStyle> <asp:BoundField DataField="FirstName" HeaderText="First Name" SortExpression="FirstName" /> <asp:BoundField DataField="LastName" HeaderText="Last Name" SortExpression="LastName" /> <asp:BoundField DataField="PlayersNumber" HeaderText="Players Number" SortExpression="PlayersNumber" /> <asp:TemplateField HeaderText="Team" SortExpression="Team"> <EditItemTemplate> <asp:DropDownList ID="ddlTeam" runat="server" CssClass="dropdown" AutoPostBack="True" AppendDataBoundItems="true" DataTextField="TeamName" DataValueField="idTeam"> </asp:DropDownList> </EditItemTemplate> <ItemTemplate> <asp:Label ID="lblTeam" runat="server" Text='<%# Bind("TeamName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> 

My current search function looks something like this:

 /** * 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('&'); //Prepare to build a "players" query: IQueryable<DBModel.playersList> playersListQuery = context.playersLists; foreach (var term in searchTerms) { //Refine our query, one search term at a time: playersListQuery = playersListQuery.Where(p => p.isDeleted == false && (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 => 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 } 

Answer 1

Fortunately, creating multiple AND elements (one for each search term) is easy in Linq2SQL - just add .Where(.. to your query:

 string[] searchTerms = "Jordan Sparks 88".Split(' '); using (var dc = new MyDataContext()) { //Using DataContext.Log is handy //if we want to look at Linq2SQL generated SQL: dc.Log = new System.IO.StringWriter(); //Prepare to build a "players" query: IQueryable<Player> playersQuery = dc.Players; //Refine our query, one search term at a time: foreach (var term in searchTerms) { //Create (and use) a local variable of the search term //to avoid the "outer variable trap": //http://stackoverflow.com/questions/3416758 //http://stackoverflow.com/questions/295593 var currentTerm = term.Trim(); playersQuery = playersQuery.Where(p => (p.isDeleted == false) && (p.FirstName.Contains(currentTerm) || p.LastName.Contains(currentTerm) || p.PlayersNumber.Contains(currentTerm) || p.Team.Name.Contains(currentTerm)) ); } //Now we have the complete query. Get the results from the database: var filteredPlayers = playersQuery.Select(p => new { p.idPlayer, p.FirstName, p.LastName, p.PlayersNumber, TeamName = p.Team.Name }) .ToArray(); //See if the generated SQL looked like it was supposed to: var sql = dc.Log.ToString(); }