搜索数据库 - ASP.NET MVC C＃ [英] Search Database - ASP.NET MVC C#
问题描述
我试图在我的ASP.NET MVC实现完整的搜索功能（C＃，LINQ到SQL）的网站。
该网站由大约3-4表有大约1-2列，我想搜索。
这是我迄今为止：
公开名单＆LT;＆SearchResult中GT;搜索（字符串关键字）
{
字符串[] =拆分Keywords.Split（新的char [] {''}，StringSplitOptions.RemoveEmptyEntries）;
清单＆LT;＆SearchResult中GT; RET =新的List＆LT;＆SearchResult中GT;（）;
的foreach（在分割字符串s）
{
IEnumerable的＆LT;＆博文GT;结果= db.BlogPosts.Where（X =＆GT; x.Text.Contains（多个）|| x.Title.Contains（S））; 的foreach（在结果的博文P）
{
如果（ret.Exists（X =＆GT; x.PostID == p.PostID））
继续; ret.Add（新信息搜索结果
{
PostTitle = p.Title，
BlogPostID = p.BlogPostID，
文字= p.Text
}）; }
}
返回RET;
}
正如你所看到的，我对关键字和在一个表（我想再说一遍对每个表）。
运行一个内部的foreach一个foreach这似乎inefficent，我想知道，如果那里有一个更好的方式来创建数据库的搜索方法。
另外，我能做些什么，以列在数据库中，使他们能够被搜索得更快？我读了一些有关他们的索引，是只是全文索引真/假现场我在SQL Management Studio中看到了什么？
另外，我能做些什么向列
的数据库，以便它们可以
搜索速度更快？我读的东西
关于索引他们，就是刚刚
全文索引真/假场
我在SQL Management Studio中看到了什么？是，使全文索引通常会大大有助于提高性能这个场景很长的路要走。但不幸的是它不会自动与LIKE操作人员的工作（这就是你的LINQ查询生成）。所以，你将不得不使用像FREETEXT，FREETEXTTABLE，含有或CONTAINSTABLE。
内置的全文搜索功能之一只是解释，原来的code将比全文搜索慢得多，因为它通常会导致表扫描。例如，如果你要搜索一个名为标题与LIKE'％ABC％'一个varchar字段，有没有选择，但对于SQL扫描每一个记录看它是否包含这些字符。
不过，内置的全文检索，实际上指数可以指定每列的文本在全文索引包括。而且它是一个大大加快你的查询索引。
不仅如此，但全文搜索提供了一些很酷的功能，LIKE操作符不能给你。它不像谷歌那样复杂，但它有搜索根词的替代版本的能力。但我最喜欢的功能之一是排名功能，它可以返回一个额外的值，以指示相关性，您就可以使用您的结果进行排序。要使用窥视 FREETEXTTABLE 或的 CONTAINSTABLE 功能。
一些更多的资源：
- 全文搜索（SQL Server）的
- 在SQL Server的临全文搜索2008
I'm attempting to implement complete search functionality in my ASP.NET MVC (C#, Linq-to-Sql) website.
The site consists of about 3-4 tables that have about 1-2 columns that I want to search.
This is what I have so far:
public List<SearchResult> Search(string Keywords) { string[] split = Keywords.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); List<SearchResult> ret = new List<SearchResult>(); foreach (string s in split) { IEnumerable<BlogPost> results = db.BlogPosts.Where(x => x.Text.Contains(s) || x.Title.Contains(s)); foreach (BlogPost p in results) { if (ret.Exists(x => x.PostID == p.PostID)) continue; ret.Add(new SearchResult { PostTitle= p.Title, BlogPostID = p.BlogPostID, Text=p.Text }); } } return ret; }As you can see, I have a foreach for the keywords and an inner foreach that runs over a table (I would repeat it for each table).
This seems inefficent and I wanted to know if theres a better way to create a search method for a database.
Also, what can I do to the columns in the database so that they can be searched faster? I read something about indexing them, is that just the "Full-text indexing" True/False field I see in SQL Management Studio?
解决方案Also, what can I do to the columns in the database so that they can be searched faster? I read something about indexing them, is that just the "Full-text indexing" True/False field I see in SQL Management Studio?
Yes, enabling full-text indexing will normally go a long way towards improving performance for this scenario. But unfortunately it doesn't work automatically with the LIKE operator (and that's what your LINQ query is generating). So you'll have to use one of the built-in full-text searching functions like FREETEXT, FREETEXTTABLE, CONTAINS, or CONTAINSTABLE.
Just to explain, your original code will be substantially slower than full-text searching as it will typically result in a table scan. For example, if you're searching a varchar field named title with LIKE '%ABC%' then there's no choice but for SQL to scan every single record to see if it contains those characters.
However, the built-in full-text searching will actually index the text of every column you specify to include in the full-text index. And it's that index that drastically speeds up your queries.
Not only that, but full-text searching provides some cool features that the LIKE operator can't give you. It's not as sophisticated as Google, but it has the ability to search for alternate versions of a root word. But one of my favorite features is the ranking functionality where it can return an extra value to indicate relevance which you can then use to sort your results. To use that look into the FREETEXTTABLE or CONTAINSTABLE functions.
Some more resources:
这篇关于搜索数据库 - ASP.NET MVC C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
