How to execute a full text search using entity framework 6

Question

I have the query:

var query = DataContext.Fotos.Where(x => x.Pesquisa.Contais("myTerm")

The SQL generated is:

SELECT 
...
FROM Fotos AS [Extent1]
WHERE [Extent1].[Pesquisa] LIKE N'%mytem%'

But I need to use:

SELECT 
...
FROM Fotos AS [Extent1]
WHERE CONTAINS ([Extent1].[Pesquisa], 'my term')

How to execute a full text search using entity framework 6?

Halutz

mikesdotnetting.com/article/298/… – Scurf

Answer 1

Seems that Entity Framework 6 does not support full text search, but there is a workaround with interceptors.

http://www.entityframework.info/Home/FullTextSearch

Update Link doesn't work so here is the original content:

Microsoft TSQL supports full-text query by means of predicates (CONTAINS and FREETEXT)

For example, you have table Notes

Create table Notes (
    Id int Identity not null,
    NoteText text 
)

CREATE FULLTEXT CATALOG [Notes Data]

When you search this table for records containing word 'John', you need to issue

SELECT TOP (10) 
* from gps.NOTES
WHERE contains(NoteText, '(john)') 

Unfortunately, Enity framework does not support full-text search predicates still. For EFv6, you can make a workaround using interception.

The idea is to wrap search text with some magic word during inside plain String.Contains code and use interceptor to unwrap it right before sql is executed in SqlCommand.

To start, lets create the interceptor class:

public class FtsInterceptor : IDbCommandInterceptor
{
    private const string FullTextPrefix = "-FTSPREFIX-";
    public static string Fts(string search)
    {
    return string.Format("({0}{1})", FullTextPrefix, search);
    }
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        RewriteFullTextQuery(command);
    }
    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        RewriteFullTextQuery(command);
    }
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }
    public static void RewriteFullTextQuery(DbCommand cmd)
    {
        string text = cmd.CommandText;
        for (int i = 0; i < cmd.Parameters.Count; i++)
        {
            DbParameter parameter = cmd.Parameters[i];
            if (parameter.DbType.In(DbType.String, DbType.AnsiString, DbType.StringFixedLength, DbType.AnsiStringFixedLength))
            {
                if (parameter.Value == DBNull.Value)
                    continue;
                var value = (string)parameter.Value;
                if (value.IndexOf(FullTextPrefix) >= 0)
                {
                    parameter.Size = 4096;
                    parameter.DbType = DbType.AnsiStringFixedLength;
                    value = value.Replace(FullTextPrefix, ""); // remove prefix we added n linq query
                    value = value.Substring(1, value.Length - 2); // remove %% escaping by linq translator from string.Contains to sql LIKE
                    parameter.Value = value;
                    cmd.CommandText = Regex.Replace(text,
                    string.Format(
                    @"\[(\w*)\].\[(\w*)\]\s*LIKE\s*@{0}\s?(?:ESCAPE
                    N?'~')",parameter.ParameterName),
                    string.Format(@"contains([$1].[$2], @{0})",parameter.ParameterName));
                    if (text == cmd.CommandText)
                        throw new Exception("FTS was not replaced on: " + text);
                    text = cmd.CommandText;
                }
            }
        }
    }
}

I used extension function In that can be defined like this:

static class LanguageExtensions
{
    public static bool In<T>(this T source, params T[] list)
    {
        return (list as IList<T>).Contains(source);
    }
}

Now lets compose a sample how to use it. We need entity class Note:

public class Note
{
    public int Id { get; set; }
    public string NoteText { get; set; }
}

Mapping configuration for it:

public class NoteMap : EntityTypeConfiguration<Note>
{
    public NoteMap()
    {
        // Primary Key
        HasKey(t => t.Id);
    }
}

And our DbContext ancestor:

public class MyContext : DbContext
{
    static MyContext()
    {
        DbInterception.Add(new FtsInterceptor());
    }
    public MyContext(string nameOrConnectionString) : base(nameOrConnectionString)
    {
    }
    public DbSet<Note> Notes { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new NoteMap());
    }
}

Now we ready to use it. Lets search for 'john':

class Program
{
    static void Main(string[] args)
    {
        var s = FtsInterceptor.Fts("john");
        using (var db = new MyContext("CONNSTRING"))
        {
            var q = db.Notes.Where(n => n.NoteText.Contains(s));
            var result = q.Take(10).ToList();
        }
    }
}

Heterophony

How can I make it work with Database First approach? – Brewton

Would also like to know how to get it to work with a Database First Approach – Wenwenceslaus

the link is broken is there an alternative link? – Sarnen

@user2279473 I updated the answer with the original content from webarchive – Heterophony

@MarianBan Thanks for you prompt response – Sarnen

For anyone using this, I had to change the search Regex to the following to make it work correctly with EF 6.4.4: [(\w*)].[(\w*)]\sLIKE\s*@(\w)\s(?:ESCAPE N?'~')" – Astrid

Answer 2

You can use raw SQL queries with EF. So, there is another easy workaround.

        using (DBContext context = new DBContext())
        {
            string query = string.Format("Select Id, Name, Description From Fotos Where CONTAINS(Pesquisa, '\"{0}\"')", textBoxStrToSearch.Text);
            var data = context.Database.SqlQuery<Fotos>(query).ToList();
            dataGridView1.DataSource = data;
        }

Input validation etc. is omitted. Edit: Code is modified according to OP's query.

Mathieu