Full text index search

[andyfurniss4]

Does Pomelo EF Core provide a way of utilising a full text index using the MATCH...AGAINST syntax?

I can see there's a ForMySqlIsFullText() extension on the index builder but I can't find an explanation of how to actually query using this index.

[mguinness]

I don't believe there's much work occurring on FTS in this provider, but there's a lot going on upstream. The typical workaround is to use FromSql to query the index.

If you're looking for something like EF.Functions.FreeText that was implemented in SQL Server then someone would need to port that over to this provider as a contribution.

[AliA74]

@mguinness , how should we specify full text index on the property of the model ?

[mguinness]

@AliA74 Not possible ATM, see dotnet/efcore#11488. You'll need to use Custom SQL.

[yilinjuang]

Is MATCH...AGAINST supported now?

[lauxjpn]

@frankyjuang Good timing! @neonVoice is just getting PR #1056 ready to be merged. Should be part of 3.2.0.

[yilinjuang]

Nice! But too bad. I planed to use it on Chinese data. Just realised the support of Chinese fts is pretty poor.

[lauxjpn]

@frankyjuang Yeah, I am not proficient in Chinese, but in Japanese, which (naturally) has similar word boundary problems. I suspect that the boolean mode should work, but I haven't given it a try.

Also take a look at 12.9.8 ngram Full-Text Parser, that is specifically directed at Chinese and Japanese support.

[ahnseunghae]

@lauxjpn
Is it possible to create fulltext index with ngram parser using IsFullText()?

[lauxjpn]

@ahnseunghae Do you mean support for the WITH PARSER ngram clause? We currently don't but it is easy to implement if it should be needed. So if this is something you need, just open an issue for it.

As a quick workaround, you could use a command interceptor and just modify the statement, or run a manual ALTER TABLE CHANGE COLUMN statement in your migrations.

[zoran-petrovic-87]

@frankyjuang Good timing! @neonVoice is just getting PR #1056 ready to be merged. Should be part of 3.2.0.

Is this now available? I see that is merged and according to integration tests I should be able to use it as
_db.Products.Where(p => p.Name.MatchInBooleanMode("Product*"))
but MatchInBooleanMode method is not found.

[mguinness]

Try _db.Products.Where(p => EF.Functions.Match(p.Name, "Product*", MySqlMatchSearchMode.Boolean)) instead.

[zoran-petrovic-87]

Try _db.Products.Where(p => EF.Functions.Match(p.Name, "Product*", MySqlMatchSearchMode.Boolean)) instead.

Thank you, works great!