public void Query_Replace()
{
using (BlogEntities db = new BlogEntities())
{
string queryResult = "";
FtsSearch.Log = value => queryResult = value;
List <Article> result = FtsSearch.Execute(() => _GetQuery(db).ToList());
Assert.Equal(@"SELECT TOP (10)
[Project1].[Id] AS [Id],
[Project1].[Date] AS [Date],
[Project1].[Text] AS [Text],
[Project1].[Active] AS [Active]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Date] AS [Date],
[Extent1].[Text] AS [Text],
[Extent1].[Active] AS [Active],
[Extent2].[Rank] AS [Rank]
FROM [dbo].[Article] AS [Extent1]
INNER JOIN CONTAINSTABLE([dbo].[Article],(*),'text') AS [Extent2] ON [Extent1].[Id] = [Extent2].[Key]
WHERE ([Extent1].[Active] = 1) AND (1=1)
) AS [Project1]
ORDER BY [Project1].[Rank] DESC", queryResult);
}
}
public void Query_Execute()
{
using (BlogEntities db = new BlogEntities())
{
List <Article> result = FtsSearch.Execute(() => _GetQuery(db).ToList());
Assert.Single(result);
}
}
public void Query_ExecuteAsync()
{
using (BlogEntities db = new BlogEntities())
{
Task.Run(async() =>
{
List <Article> result = await FtsSearch.ExecuteAsync(async() => await _GetQuery(db).ToListAsync());
Assert.Single(result);
}).Wait();
}
}
public void Query_FREETEXT()
{
using (BlogEntities db = new BlogEntities())
{
string res = FtsSearch.Query(
dbContext: db,
ftsEnum: FtsEnum.FREETEXT,
tableQuery: typeof(News),
tableFts: typeof(FTS_Int),
search: "test or text");
Assert.Equal("(-FTSFULLTEXTTABLE-FREETEXT-;[dbo].[News];*;[dbo].[FTS_Int];test or text)", res);
}
}
public void Query()
{
using (BlogEntities db = new BlogEntities())
{
string res = FtsSearch.Query(
dbContext: db,
ftsEnum: FtsEnum.CONTAINS,
tableQuery: typeof(Article),
tableFts: typeof(FTS_Int),
search: "text");
Assert.Equal("(-FTSFULLTEXTTABLE-CONTAINS-;[dbo].[Article];*;[dbo].[FTS_Int];text)", res);
}
}
public void Query_Fields()
{
using (BlogEntities db = new BlogEntities())
{
string res = FtsSearch.Query(
dbContext: db,
ftsEnum: FtsEnum.CONTAINS,
tableQuery: typeof(News),
tableFts: typeof(FTS_Int),
search: "text",
fields: new string[] { nameof(News.Text), nameof(News.Tegs) });
Assert.Equal("(-FTSFULLTEXTTABLE-CONTAINS-;[dbo].[News];Text,Tegs;[dbo].[FTS_Int];text)", res);
}
}
private IQueryable <Article> _GetQuery(BlogEntities db)
{
string queryText = FtsSearch.Query(
dbContext: db,
ftsEnum: FtsEnum.CONTAINS,
tableQuery: typeof(Article),
tableFts: typeof(FTS_Int),
search: "text");
var queryFts = db.FTS_Int.Where(n => n.Query.Contains(queryText));
return(db.Article
.Where(n => n.Active)
.Join(queryFts, article => article.Id, fts => fts.Key, (article, fts) => new
{
article,
fts.Rank,
})
.OrderByDescending(n => n.Rank)
.Take(10)
.Select(n => n.article));
}
static void Main(string[] args)
{
using (BlogEntities db = new BlogEntities())
{
FtsSearch.Log = value => Console.WriteLine(value);
{
string queryText = FtsSearch.Query(
dbContext: db,
ftsEnum: FtsEnum.CONTAINS,
tableQuery: typeof(Article),
tableFts: typeof(FTS_Int),
search: "text");
var queryFts = db.FTS_Int.Where(n => n.Query.Contains(queryText));
var query = db.Article
.Where(n => n.Active)
.Join(queryFts, article => article.Id, fts => fts.Key, (article, fts) => new
{
article,
fts.Rank,
})
.OrderByDescending(n => n.Rank)
.Take(10)
.Select(n => n.article);
Console.WriteLine(query.ToString());
var result = FtsSearch.Execute(() => query.ToList());
}
{
string queryNewsText = FtsSearch.Query(
dbContext: db,
ftsEnum: FtsEnum.CONTAINS,
tableQuery: typeof(News),
tableFts: typeof(FTS_Int),
search: "Новое AND исследование");
var queryNewsSearch = db.FTS_Int.Where(n => n.Query.Contains(queryNewsText));
var query = db.News
.Join(queryNewsSearch, news => news.Id, fts => fts.Key, (news, fts) => new
{
news.Id,
news.Text,
RubricId = news.Rubric.Id,
fts.Key,
fts.Rank,
})
.OrderByDescending(n => n.Rank)
.Take(5);
Console.WriteLine(query.ToString());
var result = FtsSearch.Execute(() => query.ToList());
//var result = await FtsSearch.ExecuteAsync(async () => await query.ToListAsync());
foreach (var item in result)
{
var elem = item;
Console.WriteLine(elem.Id + " " + (elem.Text.Length > 20 ? elem.Text.Substring(0, 20) : elem.Text).Replace('\r', ' ').Replace('\n', ' '));
}
Console.ReadKey();
}
}
}
