16

I see that Entity Framework core 2.1 has a new feature to use FREETEXT, but I am not sure how to use it as there are no examples that I can find online.

https://github.com/aspnet/EntityFrameworkCore/issues/11484

Has anyone used it yet and could give me a quick example?

DavidG
  • 113,891
  • 12
  • 217
  • 223
chobo2
  • 83,322
  • 195
  • 530
  • 832
  • You already have an issue in [Full Text Search in EF Core 2.1](https://github.com/aspnet/EntityFrameworkCore/issues/12461) which you should've mentioned in your question. – Mark G Jun 26 '18 at 16:39

1 Answers1

27

First make sure you have the relevant packages installed Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer.

Then ensure you have the following import:

using Microsoft.EntityFrameworkCore;

Now you can use the FREETEXT SQL function like this:

var results = context.Foos
    .Where(f => EF.Functions.FreeText(f.ColumnName, "search text"));

Note: You can see how this works in the unit tests, for example.

To create the full text index, there is currently no support for doing this automatically in Entity Framework Core. Instead, you need to manually add the code to the migration. So, create a migration as you normally do, open it up and add lines similar to this:

Sql("CREATE FULLTEXT CATALOG ft AS DEFAULT", true);
Sql("CREATE FULLTEXT INDEX ON dbo.TableName(ColumnName) KEY INDEX UI_TableName_ColumnName WITH STOPLIST = SYSTEM", true);

Note the 2nd parameter in the call to Sql to suppress transactions. If you omit that you may get an error stating:

CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction

GuyB
  • 101
  • 7
DavidG
  • 113,891
  • 12
  • 217
  • 223
  • Do, I need to index the column name I want to use? – chobo2 Jun 26 '18 at 17:08
  • 1
    Yes, you will need to set up the full text index first. – DavidG Jun 26 '18 at 18:20
  • I'm getting `CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.` when applying the custom migration. Any Ideas? – Liero Sep 11 '18 at 13:32
  • 2
    @Liero You need to suppress transactions in the `Sql` call, just call the overload with a value of `true` for the 2nd parameter e.g. `Sql("CREATE....", true);` – DavidG Sep 11 '18 at 13:45
  • So, we're completely out of luck if we want to search more than one column? – Jonathan Wood Sep 05 '20 at 00:20
  • @JonathanWood What makes you say that? – DavidG Sep 05 '20 at 17:35
  • Because `EF.Functions.FreeText()` only accepts one column reference. My current websites that implement full-text search all index more than one column. – Jonathan Wood Sep 05 '20 at 17:50
  • @JonathanWood But you can use that function multiple times. – DavidG Sep 05 '20 at 17:56
  • I want one query that scans multiple columns. I don't think there's any way to chain multiple calls together but you'd have to include the same query with each one. I don't think that's possible. – Jonathan Wood Sep 05 '20 at 17:59
  • @JonathanWood It's the same as any query, just do `FreeText(...) || FreeText(..)` or whatever. – DavidG Sep 05 '20 at 20:09
  • @DavidG: Full-text search queries look like this: `CONTAINSTABLE([Table], *, @SearchTerm)`. The asterisk specifies that all full-text-indexed columns should be searched. So this is more optimized than an `OR` expression would seem to allow. It's possible there is an option I'm not aware of, but I've requested Microsoft [add support for more columns](https://github.com/dotnet/efcore/issues/22418). – Jonathan Wood Sep 06 '20 at 16:07
  • Any idea if FromText is also available for MySQL? – Nelson Sousa Feb 25 '21 at 01:14
  • @DavidG yes, I just found an extension method for SQL Server. – Nelson Sousa Feb 25 '21 at 21:24