Full Text Search in EF Core 2.1?

Question

I am looking at using Full Text Search but not 100% clear on how to get it up with EF Core 2.1.

It seems that EF Core 2.1 might have implemented partial support for Full Text Search but I am not finding any tutorials on how actually use it.

My understanding is that I will have to add an Full Text index to one of my columns.

So if I have this table

public class Company {
    public string Name {get; set;}
}

public class CompanyConfig : IEntityTypeConfiguration<Company>
{
  public void Configure(EntityTypeBuilder<Company> builder)
        {
            builder.HasKey(x => x.Id);
            builder.Property(x => x.Name).HasMaxLength(100).IsRequired();
        }

}

How would I add full text index to my Name property?

Answer 1

You need to add them manually for now using the SQL function in migrations.

Creation of full text indexes is not yet built, as of EF Core 2.1. See this issue tracker for more detail https://github.com/aspnet/EntityFrameworkCore/issues/11488 .

In summary;

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.

An example C# Linq query for FreeText, extracted from the tests on https://github.com/aspnet/EntityFrameworkCore/commit/2a6ccad8821f9360ae753bce41d63811185b8912;

using (var context = CreateContext())
{
    var result = await context
        .Employees
        .Where(c => EF.Functions.FreeText(c.Title, "Representative"))
        .ToListAsync(); 

        Assert.Equal(result.First().EmployeeID, 1u);

        Assert.Equal(
            @"SELECT [c].[EmployeeID], [c].[City], [c].[Country], [c].[FirstName], [c].[ReportsTo], [c].[Title] FROM [Employees] AS [c] WHERE FREETEXT([c].[Title], N'Representative')",
                    Sql,
                    ignoreLineEndingDifferences: true,
                    ignoreWhiteSpaceDifferences: true);
}