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?

Solution

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);
}

Comments

[DavidG]

I don't think you can get full text search using normal Linq style querying. You would need to do something like context.Entity.FromSql("SELECT * FROM TableName WHERE CONTAINS(ColumnName, @p0)", "your text")

[vivek nuna]

It can help you github.com/aspnet/EntityFrameworkCore/issues/1590

[chobo2]

@DavidG - So that would do a full text search, but I still would need to create the full text index correct? How would I do that with ef code first?

[chobo2]

@viveknuna - I saw that link but I am not clear on what the difference between Full Text Search and FreeText predicate is it did not help me. It also did not give an example of how to use this FreeText predicate.