Full-Text Search with Entity Framework Core

.net-core c# entity-framework-core

Question

What is the best way to use full-text search with EFCore

For now I have two approaches

Approach #1

 var entities = this.DbContext.Example
            .FromSql("fullText_Proc {0}, {1}", searchTermParameter, topParameter);

        return entities.AsNoTracking().ToList();

Here I'm forced to create a proc because FromSql ignores the WHERE clause.

Approach #1

Create a command and do the mapping manually

using (var command = this.DbContext.Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = "SELECT ... WHERE CONTAINS("Name", @p1)";
        command.CommandType = CommandType.Text;
        var parameter = new SqlParameter("@p1",...);

        this.DbContext.Database.OpenConnection();

        using (var result = command.ExecuteReader())
        {
            while (result.Read())
            {
               .... // Map entity
            }
        }
    }
1
7
7/23/2017 6:36:51 AM

Popular Answer

WHERE clause is working with FromSql, actually. I spent enough time to realize problem was passing parameter.

This is how I have solved the problem.

  public IList<Example> QueryPhrase(string phrase)
    {
        phrase = $"FORMSOF(FREETEXT, \"{phrase}\")";

        var query = _dataContext.Example
            .FromSql(@"SELECT [Id]
                      ,[Sentence]                          
                    FROM [dbo].[Example]
                    WHERE CONTAINS(Sentence, @p0)", phrase)
            .AsNoTracking();

        return query.ToList();
    }
3
8/16/2017 12:09:37 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow