Create a Fulltext Catalog with EF Core .NET Migrations for MSSQL

A full-text catalog in Microsoft SQL Server is a specialized storage that is used to store and manage full-text indexes for one or more tables in a database. A full-text index is a special type of index that is used to support fast, full-text searches of large amounts of text data. It enables users to search for words and phrases within a column of text data in a table, and returns rows that contain the search criteria. Full-text indexes can be created on columns of data that are stored in character data types, such as char, varchar, and text.

To use full-text search in a SQL Server database, a full-text catalog must be created and associated with one or more tables in the database. The full-text catalog stores the full-text index and any related metadata, such as stopwords and thesaurus files. It also provides the infrastructure for managing and maintaining the full-text index. The full-text catalog is created and managed using the Full-Text Engine, which is a component of SQL Server that is responsible for creating and maintaining full-text indexes.

Create a full-text catalog with T-SQL

All possible options are

CREATE FULLTEXT CATALOG catalog_name  
     [ON FILEGROUP filegroup ]  
     [IN PATH 'rootpath']  
     [WITH <catalog_option>]  
     [AS DEFAULT]  
     [AUTHORIZATION owner_name ]

<catalog_option>::=  
     ACCENT_SENSITIVITY = {ON|OFF}  

So a simple create command would be

CREATE FULLTEXT CATALOG [MyDemoCatalog] WITH ACCENT_SENSITIVITY = OFF

Create a full-text catalog with EF Core

To create a full-text catalog with EF Core, we need to create a migration. The migration will create the full-text catalog in the database. To create a migration, we need to use the dotnet ef migrations add command. The command will create a migration file in the Migrations folder of the project. The migration file will contain the SQL commands (in our case empty, because we dont have any changes - we only want that empty migration!) that will be executed when the migration is applied to the database.

public partial class AddFullTextCatalog : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            @"CREATE FULLTEXT CATALOG [MyDemoCatalog] WITH ACCENT_SENSITIVITY = OFF");
    }
    
    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"DROP FULLTEXT CATALOG [MyDemoCatalog]");
    }
}

You can now execute the migration (e.g. dotnet ef database update), and it will result in:

CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

To get the command running, you need to susspress transactions, which can be archived with:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(
        @"CREATE FULLTEXT CATALOG [MyDemoCatalog] WITH ACCENT_SENSITIVITY = OFF",
            suppressTransaction: true); // YOU NEED THIS!
}

Now, the catalog will be created with your EF Migration steps, no matter if you are using in-process migrations or the recommended idempotent scripts.

Why EF Core idempotent migrations scripts are recommended

EF Core idempotent migration scripts are recommended for a number of reasons:

• They can be safely run multiple times: Idempotent migration scripts can be run multiple times on a database without causing any harm or causing any unintended changes. This makes them very useful in scenarios where the database schema needs to be updated frequently, such as in a continuous deployment environment.
• They can be easily rollbacked: If a problem occurs while running an idempotent migration script, it can be easily rolled back by simply running the script again in the opposite direction. For example, if a script is used to add a column to a table, running the script again would remove the column.
• They are easier to maintain: Idempotent migration scripts are easier to maintain because they do not depend on the current state of the database. This means that they can be modified and tested without worrying about the impact on the database.
• They are more resilient: Idempotent migration scripts are more resilient to errors and failures because they can be rerun if necessary. This makes them a safer choice for updating the database schema in production environments.

In summary, idempotent migration scripts are recommended because they can be safely run multiple times, easily rollbacked, easier to maintain, and more resilient to errors and failures.