SQL Server full-text search: Support creating full-text indexes in migrations

[divega]

In EF Core 2.1 we have initial support for for full-text search via the FreeText predicate in LINQ, but this only works with databases that have already been indexed. EF Core and the SQL Server provider don't provide any way to configure the model so that migrations or EnsureCreated can generate the right SQL for defining the indexes.

[jakemclelland]

Does EF Core 3.1 support migrations to build full-text indexes and catalogs?

[bricelam]

Workaround

migrationBuilder.Sql(
    sql: "CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;",
    suppressTransaction: true);
migrationBuilder.Sql(
    sql: "CREATE FULLTEXT INDEX ON Posts(Content) KEY INDEX PK_Posts;",
    suppressTransaction: true);

[pantonis]

@bricelam Is this something that is supported in EF Core 3.1?

[bricelam]

The workaround should work. But there is an issue with Web Deploy not honoring suppressTransaction.

[hamidmayeli]

I assume this is not possible in EF Core 5.0 either, am I wrong?

[bricelam]

@HamidTheGeek It's possible (see SQL Server Full-Text Search and EF Core); it's just not as nice as it could be.

[mahesh-anjani]

@bricelam How to do full-text search on a property of type byte[] (varbinary(max) in SQL)? Can you please share or give link to a sample?

[bricelam]

🤷 A quick search says these docs might relevant...

[farhadnowzari]

Hi everyone, this looks relatively old but I have a quick question. What can be the consequences of adding the following sql to the migration

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON Posts(Content) KEY INDEX PK_Posts;

does these two lines are the same on all versions of mssql? because if we avoid using migrationBuilder methods, then it will ignore the version check for the given hard coded sql right?

[roji]

@farhadnowzari I don't know if these statements work across all versions - check out the SQL Server docs for that. However, note that when you use migrationBuilderm nethods, EF Core doesn't do any version checks of any sort; migration SQL is generated without even connecting to the database (this is how EF Core is able to produce migration SQL scripts). In that sense there's no difference between an EF Core-generated migrationBuilder line and a raw SQL line you add yourself.

[farhadnowzari]

@farhadnowzari I don't know if these statements work across all versions - check out the SQL Server docs for that. However, note that when you use migrationBuilderm nethods, EF Core doesn't do any version checks of any sort; migration SQL is generated without even connecting to the database (this is how EF Core is able to produce migration SQL scripts). In that sense there's no difference between an EF Core-generated migrationBuilder line and a raw SQL line you add yourself.

Exactly that's the point. The full text search feature should be integrated with migrationBuilder I think.

[roji]

@farhadnowzari sure, that's what this issue is tracking.