Passing a full-text search parameter using Dapper.net

Question

Passing a full-text search parameter using Dapper.net

dapper full-text-search sql-server

Question

Consider this simple query which use full text searching on the Keywords field:

DECLARE @searchTerm VARCHAR(500) = 'painted'
SELECT * FROM StockCatalogueItems
WHERE (CONTAINS(KeyWords, @searchTerm))

This works as expected, but I need to do the same using a Dapper.net parameterised query. When using stored procedures, I create the full text parameter like this: "\"painted*\""

But using the same approach this doesn't work using dapper. No results are returned. This is the line in the query where I use the parameter:

AND (CONTAINS(KeyWords, @search))

and it's passed to the query like so:

return _context.Database.Connection.Query<StockProfileMatrix>(basequery, new
{
    search = searchTerm
}

I can only assume that dapper is sanitising the string somehow, removing quotes perhaps?

Any ideas?

markpsmith

Popular Answer

This works for me. However the tech stack am working on is .net core RTM and "Dapper": "1.50.0-rc3",

_dbConnection.QueryAsync<Guid>(@"select br.Id from Brand br where CONTAINS(br.Text,@Name)",new {Name = $"\"*{name}*\""}))

Jaya

View more on Stack Overflow

markpsmith · Accepted Answer · 8/17/2018 4:24:57 PM

For completeness, I'll answer the question. The query syntax is correct, but the way in which the full-text parameter is created was obviously not. I created an extension method that formats the parameter:

 public static string ToFullText(this string str)
 {
      string searchTerm = null;
      if (!string.IsNullOrEmpty(str))
      {
          string[] keywords = str.Trim().Split(null);
          foreach (var keyword in keywords)
          {
             searchTerm += string.Format("\"{0}*\" AND ", keyword);
           }
           if (searchTerm != null)
              searchTerm = searchTerm.Substring(0, searchTerm.LastIndexOf(" AND "));
        }
        return searchTerm;
  }

Then I call the method when I pass the parameter in to the dapper query:

_context.Database.Connection.Query<dynamic>(query, new 
{ 
    search = filter.SearchTerm.ToFullText()
});

Passing a full-text search parameter using Dapper.net

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator