Using LINQ to search multiple terms

Question

I want to be able to do a basic search for multiple words. In essence, using LINQ to do the equivalent of the SQL query

SELECT * 
FROM table 
WHERE column LIKE '%foo%' OR column LIKE '%bar%'

I found a few similar questions on here and they mostly provide the same solution, splitting the input and then using Any to search for all words. However I get an error when using this solution.

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator

I understand the error message to mean I cannot use Any - only Contains. But this doesn't make sense to me as the solutions on here are successfully using Any.

This is my code.

var termString = "foo bar";
var searchTerms = termString.Split(' ');
var itemsList = (from items in myDataContext.myDatabaseTable
                 where searchTerms.Any(term => items.ColumnName.Contains(term))
                 select items).ToList();

The "solutions" using `Any` never work against databases, only lists. And the original SQL is extremely slow as it has to scan the entire table for matches. `LIKE '%foo%'` can't benefit from indexing. Instead of trying to recreate this *slow* query in LINQ, you should add a [full-text-search index](https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver15) and [query the table](https://learn.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search?view=sql-server-ver15) using FTS predicates like CONTAINS, FREETEXT — Panagiotis Kanavos, Dec 03 '20 at 14:12
[FreeText](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.sqlserverdbfunctionsextensions.freetext?view=efcore-2.1) is available in EF Core since 2.1. — Panagiotis Kanavos, Dec 03 '20 at 14:14
@PanagiotisKanavos full-test search indexes are unfortunately really not very useful because they are term/word based, and will not find partial word matches and should not be considered a good alternative to infix `LIKE` searches. — NetMage, Dec 03 '20 at 20:31
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Dec 03 '20 at 20:32
@NetMage that's what makes them really useful, especially in this case. And *yes they will find partial matches* because they use stemming and can actually match multiple forms of a word. LIKE isn't an alternative and the performance on even a small data set will be awful. The OP is looking for words. No recipes are going to fix the lack of indexing — Panagiotis Kanavos, Dec 03 '20 at 21:09
@NetMage if you mean *fuzzy* matching, the viable alternative is an external service like Elastic. Which is why it's used even by Microsoft's TFS (now Azure DevOps) to index code files — Panagiotis Kanavos, Dec 03 '20 at 21:11
@PanagiotisKanavos Strongly disagree. Try to use a full-text index to find matches for %dex% and see what happens. Or in my case, a phonebook database searched for an office code or last four digits. — NetMage, Dec 03 '20 at 21:11
@NetMage I know what happens. A full table scan. Lots of locks and blocking. Bad idea. That's why we have Elastic — Panagiotis Kanavos, Dec 03 '20 at 21:13
@NetMage I also know what happens if you search for *words* like `foo` and `bar` in an FTS-indexed column. Instant results. — Panagiotis Kanavos, Dec 03 '20 at 21:14
@PanagiotisKanavos That is fine, but it doesn't help my users. — NetMage, Dec 03 '20 at 22:27
@NetMage and your users will be just fine with FTS when they want to search words. They won't be fine *at all* if the system crawls to a stop or deadlocks. At that point you''ll have to use the proper tool for the job. LIKE %% isn't viable - no more than storing dates as strings is. You can do it up to a point, then it collapses — Panagiotis Kanavos, Dec 04 '20 at 07:59
@NetMage actually, *you* don't wont delays, do you ? When you search SO you don't want to wait. You don't, because SO *doesn't* use LIKE %%, it [uses Elastic](https://meta.stackexchange.com/questions/160100/a-new-search-engine-for-stack-exchange). If *you* want fast search, why shouldn't your users? — Panagiotis Kanavos, Dec 04 '20 at 08:00
@PanagiotisKanavos You're making some big assumptions - not every database is the size of SO. Sometimes a post-it note is the right solution. Sometimes `LIKE` on a view joining all columns is the right solution. — NetMage, Dec 04 '20 at 16:44

score 0 · Answer 1 · answered Dec 03 '20 at 20:42

Using LINQKit, or your own simplified implementation of it, you can create predicates that expand into a series of individual tests as in SQL.

My full library has WhereAny, WhereAll and WhereSplitContains as well as the predicate bases for these. You need one of the WhereAnys:

// searchTerms - IEnumerable<TSearch> where one must match for a row
// testFne(row,searchTerm) - test one of searchTerms against a row
// r => searchTerms.All(s => testFne(r,s))
public static Expression<Func<T, bool>> AnyIs<T, TSearch>(this IEnumerable<TSearch> searchTerms, Expression<Func<T, TSearch, bool>> testFne) {
    var pred = PredicateBuilder.New<T>();
    foreach (var s in searchTerms)
        pred = pred.Or(r => testFne.Invoke(r, s));

    return pred;
}

// searchTerms - IEnumerable<TSearch> where one must match for a row
// testFne(row,searchTerm) - test one of searchTerms against a row
// dbq.Where(r => searchTerms.Any(s => testFne(r,s)))
public static IQueryable<T> WhereAny<T,TSearch>(this IQueryable<T> dbq, IEnumerable<TSearch> searchTerms, Expression<Func<T, TSearch, bool>> testFne) =>
    dbq.AsExpandable().Where(searchTerms.AnyIs(testFne));

Now your query becomes:

var itemsList = myDataContext.myDatabaseTable
                   .WhereAny(searchTerms, (item,term) => item.ColumnName.Contains(term))
                   .ToList();

Using LINQ to search multiple terms

1 Answers1