Support to Full-Text Search

[ghost]

Updated by @divega:

Given that EF Core 2.1 will contain #11484 initial support for SQL Server full-text search, in the form of support for FreeText predicate contributed by @Djangoum, we have decided to break down the original issues in into a number of individual issues:

• SQLite: Full-text search support SQLite full-text search support #4823
• SQL Server: Consider supporting overloads of full-text search functions SQL Server full-text search: FreeText which takes list of columns as parameter #10462
• SQL Server: Consider supporting binary columns in full-text search functions SQL Server full-text search: Consider supporting binary columns in full-text search functions #11482
• SQL Server: Contains predicate SQL Server full-text search: Contains predicate #11486
• SQL Server: FreeTextTable and ContainsTable table-valued functions SQL Server full-text search: FreeTextTable and ContainsTable table-valued functions #11487
• SQL Server: Support creating full-text indexes in migrations SQL Server full-text search: Support creating full-text indexes in migrations #11488
• Support for external full-text search engines Support for external full-text search engines #11489

In order to help us prioritize any future investments in this area, please up-vote (using the 👍 reaction) your favorite features among the new issues.

---

Original issue:

There are any plans to make SQL Server Full-Text Search work with EF?

More info about Full-Text Search in SQL Server https://msdn.microsoft.com/en-us/library/ms142571.aspx
Also consider the capabilities in SQLite #4823

[rowanmiller]

We do want to look at this, dropping it on the backlog for the moment since we're working on core functionality for EF Core right now.

Note I also removed the reference to SqlFunctions from the title as this was a type from EF6.x and we'll likely do something different on the EF Core code base.

[d668]

any update?

[roji]

Posting link to the PostgreSQL full-text search capabilities for when design begins on this feature.

[jemiller0]

This is a feature I've been wanting for a long time. Really, all I need is the ability to do a .Contains() filter in an efficient way, which as far as I know can only be done using full-text search. The tables I'm working with have millions of rows, so, using a SQL LIKE is problematic.

[joacar]

When is this planned? Really keen to use this feature

[mikebridge]

Is there any workaround for this in the meantime? In EF6 it was possible to use a DbInterceptor or maybe even call ToTraceString() to generate SQL and eval the result.

[Trolldemorted]

@rowanmiller are there any plans on getting this out of the backlog in the near future? A dirty workaround would suffice, but being unable to do any efficient full-text search is really bad.

[ajcvickers]

@Trolldemorted @mikebridge I think you should be able to use FromSql to write a full-text query, but I haven't tried it. /cc @divega

[ErikEJ]

@Trolldemorted You can use the concepts here to get started, just use the CONTAINS or FREETEXT functions instead of LIKE.. https://github.com/rowanmiller/Demo-EFCore/blob/master/StartingSourceCode/FromSql/Program.cs

[mikebridge]

@ErikEJ @ajcvickers The problem is that I have some complex logic which constructs a query using linqkit's predicatebuilder. If I could extract the resulting SQL some way I could at least do some string manipulation on it and send it through FromSql, but I see no way to do this in EF Core. It was possible in EF6 with DBInterceptor or ToTraceString but these no longer exist.

[jemiller0]

After awhile one has to ask, is jumping through all these hoops, just to use LINQ really worth it? It is a constant fight trying to get the queries to work in an efficient manner. An example, is the way one-to-manys are now handled in EF Core, where it does separate queries for each of the tables rather than a big join. That works well for some things, but, other things it is terrible. For example, if you need to filter using an Any() on one of the one-to-manys. It uses N+1s. So, for some things, EF Core is better than EF 6, but, other things are worse. Personally, I think there should be a compatibility mode that lets you revert to the old behavior. Otherwise, you are forced into using a mishmash of technologies in a given application to do everything you need to do. Recently, I've switched to using Dapper for some things. I wanted to use EF Core for everything, but, I'm coming to the conclusion that that may never happen. So, at the moment, I'm using EF 6 for some things, EF Core for others, Dapper for other things, and I've had to even do things like generate my own database schema since EF Core doesn't even preserve column order. I've also had to write custom code for doing any kind of bulk operations. I found that even for doing something as simply as querying all results from a table, Dapper was twice as fast. Not a big deal if you are only returning a page of data for a web page, but, if you are doing any kind of processing of large result sets, EF isn't really appropriate even for pulling the data.

[mikebridge]

... another EF6 workaround that no longer works was to use ToString() on an IQueryable.

[mikebridge]

Has anyone tried extending IQueryable like this?

[mikebridge]

This method of generating the query via reflection fails for me, but I think it has something to do with the fact that I'm producing it with LinqKit.

[Trolldemorted]

@ajcvickers @ErikEJ thanks, i will have a look!

do you also have an advice on how to make ef core's migrations cope with an fts3/4/5 table peacefully?

[ErikEJ]

Think of migrations as a "proposal" for schema changes, so you are always free to modify them, including having Raw SQL in them via the Sql() method.

[Trolldemorted]

With "peacefully" i meant it will not introduce problems in future migrations - how does ef core determine the difference between the last migration and the status quo when it generates a new migration, and may that cause problems?

If we could get some official sound on when this comes out of the backlog (even if it takes several months to be implemented) i'd rather not meddle with it myself, but rather wait for an official release.

I am pretty sure this feature is requested by many, and if i had known this before i might have chosen EF 6.x instead.

[joopscheer]

Any updates on this topic?

[eltiare]

Would love to have first class support for this in the near future. Bumping for my vote. :)

[divega]

We have a couple of new SQL Server FullText functions in 2.1. Clearing up milestone to decide what to do with this issue. We could resolve as fixed but split for remaining functionality.

[eltiare]

I'm assuming that maintainers of other drivers (like the Postgres people) will have to add something similar?

[IvanJosipovic]

Postgres people are already on it, see npgsql/efcore.pg#1

[ajcvickers]

+1 for fixed. @divega What is remaining?

[divega]

@ajcvickers the PR from @Djangoum added basic of overloads of FreeText, which is the simplest predicate function for SQL Server's full-text-search feature. Other things that could be added:

• SQL Sever Full-text-search support:

  • FreeTextTable TVF
  • Contains Predicate
  • ContainsTable TVF
  • Support for creating full-text indexes (declared through fluent API and/or attributes)
  • Better sugar for specifying searching in specific properties (e.g. lambda instead of column name, with support for column to property mapping with different names)

• Ouside of SQL Server support: we can have a separate issue about plugging in an external full-text engine like Lucene or Azure Search (@anpete had a prototype for the latter).

In fact I am a bit concerned by the shape of the API we have right now... @smitpatel I know you reviewed and did some cleanup after the PR. Do you know if the propertyName argument is really treated as a propertyName or is it actually a column name? E.g. what happens if the names don't match?

[smitpatel]

We can certainly improve the name of parameter. The parameter passed is actually the string property from CLR model. That string property would get translated to mapped column in database so there is no issue about name mismatch.

[divega]

@smitpatel ok, so if I understand correctly, the parameter name seems fine. What improvement did you have in mind?

[divega]

Discussed the current state of FreeText() with @smitpatel. Here are some notes:

• We need to disallow any string expression that is not a reference to a property
• Rename first parameter to property or propertyReference (it isn't actually the name)
• Figure out how to support property lists (@smitpatel had already filed SQL Server full-text search: FreeText which takes list of columns as parameter #10462 for this)
• Figure our if we want to support binary columns (we can create a separate issue for this)

[ajcvickers]

@divega to break into new issues and close general issue.

[divega]

Updated original post with links to the new issues. Closing as duplicate of everything else.