search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.66k stars 3.16k forks source link

Support to Full-Text Search #1590

Closed ghost closed 1 year ago

ghost commented 9 years ago

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:

In order to help us prioritize any future investments in this area, please up-vote (using the :+1: 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 https://github.com/aspnet/EntityFramework/issues/4823

rowanmiller commented 9 years ago

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 commented 7 years ago

any update?

roji commented 7 years ago

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

jemiller0 commented 7 years ago

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 commented 7 years ago

When is this planned? Really keen to use this feature

mikebridge commented 7 years ago

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 commented 7 years ago

@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 commented 7 years ago

@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 commented 7 years ago

@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 commented 7 years ago

@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 commented 7 years ago

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 commented 7 years ago

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

mikebridge commented 7 years ago

Has anyone tried extending IQueryable like this?

mikebridge commented 7 years ago

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 commented 7 years ago

@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 commented 7 years ago

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 commented 7 years ago

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 commented 7 years ago

Any updates on this topic?

eltiare commented 6 years ago

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

divega commented 6 years ago

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 commented 6 years ago

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

IvanJosipovic commented 6 years ago

Postgres people are already on it, see https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/1

ajcvickers commented 6 years ago

+1 for fixed. @divega What is remaining?

divega commented 6 years ago

@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:

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 commented 6 years ago

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 commented 6 years ago

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

divega commented 6 years ago

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

ajcvickers commented 6 years ago

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

divega commented 6 years ago

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