LINQ全文搜索 [英] Full Text Search with LINQ
问题描述
您好，谢谢，
我正在尝试实现搜索功能，在该功能中可以将结果填充到Gridview中.我正在使用LINQ在C#中查询我的数据库.
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#.
我要解决的问题是，如果用户要使用多个搜索词搜索多个列/字段，那我该怎么办?因此，例如，如果我用这3个字段搜索一个表，并且有一些数据，例如:
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:
firstName | lastname | players#
- 迈克尔|约旦| 12
- 迈克尔|约旦| 24
- 迈克尔|约旦| 45
- DeAndre |约旦| 6
- Jerome |约旦| 44
- 乔丹|火花| 88
- 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:
如果我搜索迈克尔·乔丹，我将获得第1,2,3行.
If I search Michael Jordan than I should get row #s 1,2,3 back.
如果我搜索Jordan Jordan Sparks 88，则应该返回第6行.
If I search Jordan Sparks 88 than I should row # 6 back.
所以我的问题是我不知道搜索词在表中的什么位置，因此我必须搜索所有列/字段.在当前代码中，我需要遍历每个列/字段的名称，然后使用Contains()然后||. (或")，但仅适用于1个搜索字词.
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 || ("or"), but it only works for 1 search term.
是否有一种优雅，简便的方法来搜索和过滤整个linq表，以使列表最小化?从那里，我将结果添加到数据源，然后将其绑定.
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.
@Sphinxxx我正在使用典型的gridview.看起来像这样:
@Sphinxxx I am using 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
}
推荐答案
幸运的是，在Linq2SQL中创建多个AND(每个搜索词一个)很容易-只需向查询中添加.Where(..:
Luckily, creating multiple ANDs (one for each search term) is easy in Linq2SQL - just keep adding .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's 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();
}
这篇关于LINQ全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
