Implement Full Text Search Instead of relying on Entity Framework

[chaim1221]

Full Text Search allows for a better overall search experience; faster searches, better support for ES & EN specific characteristics, (stem'ing, etc.), reduced reliance of EF...

However, support for FTS in EFCore is not yet ideal. As of EFCore 2.2, searches in the format of

CONTAINS(*, 'some text') 

don't work as expected. The LINQ code necessary to use FTS in EFCore 2.2 is verbose and annoying. Best wait for EFCore 3 and more FTS support.

[jcii]

Useful TSQL: Is FTS installed?

  select SERVERPROPERTY('IsFullTextInstalled')

setting up a FTS index

USE casa_db;
GO
CREATE FULLTEXT CATALOG MacheteActivitiesFTCat;

CREATE FULLTEXT INDEX ON dbo.Activities
(
    teacher Language 1033,
    notes   Language 1033,
    nameEN  Language 1033,
    typeEN  Language 1033
)
KEY INDEX PK_Activities ON MacheteActivitiesFTCat
WITH CHANGE_TRACKING AUTO
GO

select * from Activities where contains(*, 'LnI');

drop fulltext index on dbo.Activities;

https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/search/choose-a-language-when-creating-a-full-text-index?view=sql-server-2017

[jcii]

Installation URLS https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-2017&pivots=cs1-bash https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-change-repo?view=sql-server-2017&pivots=ld2-ubuntu https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-full-text-search?view=sql-server-2017