How do I configure Full Text Search for my database

Question

Hi all;

I've read this (really helpful), this, & this. I think I understand how to select the varchar columns in my primary class. So for (Entity Framework - code first):

public class Event
 	 {
  	public int Id { get; private set; }
 	public string Subject { get; set; }
 	public string? ElevatorPitch { get; set; }
 	public RichText? Description { get; set; }
 	public Interest? Interest { get; set; }
 	public List<Tag>? Tags { get; set; }
 }
  public class RichText
 {
 	public string Text { get; private set; }
 } 
 public class Interest
 {
 	public string Name { get; set; } 
	public string Description { get; set; }
 }
  public class Tag
 {
 	public string Name { get; set; }
 	public string Description { get; set; }
 }

So how do I write the SQL to create the FULLTEXT CATALOG and INDEX including the SEARCH PROPERTY LIST to Tags and the 1:1 links to Description and Interest?

And even more basic, what else do I need to do to have this all set up right against my database? (I can't try this on my own at present as I can't connect to SQL Server.) But from what I've read to date, it's not clear to me exactly what needs to be done to enable Full Text Search on a DB, set AUTO indexing, set the language, use the stop words, etc.

And is there a web page somewhere that gives a clear description of the search grammar and how to use it in a query?

Including anything that lets me set how it calculates the WEIGHT and RANK. For example, in my case, a word appearing in Interest should increase the WEIGHT/RANK a lot. And appearing in Tags should increase the WEIGHT/RANK a fair amount.

Any guidance on the above much appreciated. Hopefully as I get further into this, then this will all start to make sense.

thanks - dave

Answer 1

First step is to make sure that you select the Full-text component when you install SQL Server. If you did not, you can run Setup to add it to your instance.

Creating the fulltext catalog is quite simple:

CREATE FULLTEXT CATALOG fulltext WITH ACCENT_SENSITIVITY = ON AS DEFAULT

Creating a fulltext is index is not that tricky either. Here is an example:

CREATE FULLTEXT INDEX ON Lines(linetext LANGUAGE 0x0) KEY INDEX u_Lines

Although here are a few more options, and the topic for CREATE FULLTEXT INDEX may be worth giving a look:

I have not worked much with property lists, so I can't say much there, but the topic for CREATE SEARCH PROPERTY LIST can be a start.

To query a fulltext index you can use the CONTAINS and FREETEXT predicates as well as the CONTAINSTABLE and FREETEXTABLE functions. If you are only searching for a word, you can do this:

SELECT O.schema_, O.name, O.type, L.linenum, L.linetext
FROM   Objects O
JOIN   Lines L ON O.object_id = L.object_id
WHERE  CONTAINS(L.linetext, 'tablethree')
ORDER  BY O.name, L.linenum

I'm afraid that I don't really see the connection to your C# columns, but you've tagged the query SQL Server only, so...

Answer 2

To use full-text search in EF Core, maybe you should refer to here: https://www.bricelam.net/2020/08/08/mssql-freetext-and-efcore.html