FullTextIndexAttribute support

[borisdj]

FullText Search is another sometimes often used feature so it would be useful to have [FullTextIndexAttribute] for it as well. At the moment I am setting it manually in migration with raw Sql (encapsulated in method):

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(FullTextSql.CreateIndex(nameof(Company.Name), nameof(Company)), true);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(FullTextSql.DropIndex(nameof(Company.Name)), true);
}

And these function are defined in following class: (Remark: FullTextSql.CreateIndex requires that CATALOG exist which requires STOPLIST, and they are created in the first migration. That requirement (previous manual creation of StopList and Catalog) can stay the same. I am not sure how this could be done automatically, since they only need to be created once and all FTIndexes can use them).

public static class FullTextSql
{
    public static string CreateIndex(string[] columns, string table,
        string shema = "dbo", string stopList = null)
    {
        string sql = $"CREATE FULLTEXT INDEX ON";
        sql += $" {shema}.[{table}]([{String.Join("], [", columns)}]) KEY INDEX PK_{table}";
        sql += (stopList != null ? $" WITH STOPLIST = {stopList};" : "");
        return sql;
    }
    public static string CreateIndex(string column, string table, string shema = "dbo", string stopList = null)
    {
        return CreateIndex(new string[] { column }, table, shema, stopList);
    }
    public static string DropIndex(string table, string shema = "dbo")
    {
        return $"DROP FULLTEXT INDEX ON {shema}.[{table}];";
    }

    public static string CreateCatalog() { return "CREATE FULLTEXT CATALOG FTC AS DEFAULT;"; }
    public static string CreateStoplist(string stoplist = "EmptyStopList") {
        return $"CREATE FULLTEXT STOPLIST {stoplist};";
    }
    public static string DropCatalog() { return "DROP FULLTEXT CATALOG FTC;"; }
    public static string DropStoplist(string stoplist = "EmptyStopList") {
        return $"DROP FULLTEXT STOPLIST {stoplist};"; }
    }

Another thing that should be taken into consideration when doing this is that these methods which are implementing FTIndex should be exposed so that they can be overridden depending on type of DB itself. Or they could be interfaces and have different implementation based on some enum parameter DbType (MsSQL, PostgreSQL, MySQL).

[isukces]

I've just started to implement this feature. First I have each time verify if catalog exists on each migration, because I'm unable to obtain information if catalog has been introduced in last or previous migration. It's not best approach but have no idea how to solve this.

if not exists (SELECT * FROM sys.fulltext_catalogs where name='my catalog name') CREATE FULLTEXT CATALOG [my catalog name];

Please check last commit and leave comment.

[borisdj]

I can't make FTS attribute work. When put just [FullTextIndex] with no parameters it gives error: Unable to find FullTextCatalogName for table Movies And if I use [FullTextIndex(FullTextCatalogName = "FTC")] I can create migration but Up and Down methods are empty.

[isukces]

The problem is that from EntityFramework's point of view there is nothing to migrate. I have no idea at this moment how to fix this.

[borisdj]

So currently this attribute is informative only? Meaning that after manually creating FTS index we put [FullTextIndex] on column as marker.

[isukces]

No. It willl affect each next migration, by adding some sql statements. Shaman can only fix migrations made by EF. It cannot force EF to create migration if EF doesn't want.

[borisdj]

Even when I make another change because of which migration is created, that migration still does not contains anything regarding FullText (FTIndex still being ignored), and if I run update-database index is not created also. Should something else be configured for this ? I see that code for this is in EfCore.Shaman.SqlServer class SqlServerFixerService, but I am not sure how to use FixMigrationUp method from here and not the one from EfCore.Shaman. I have tried with: this.FixOnModelCreating(modelBuilder, shamanOptions: ShamanOptions.Default.With(new SqlServerFullTextIndexService())); But it didn't help.

[borisdj]

Is this feature not implemented yet? I can see SqlServerFullTextIndexService in code here on github, but after adding nugget in project there is no reference to it.