How do you check if a string contains any strings from a list in Entity Framework?

Question

I am trying to search a database to see if a string contains elements of a list of search terms.

var searchTerms = new List<string> { "car", "232" };
var result = context.Data.Where(data => data.Name.Contains(searchTerms) ||
                                        data.Code.Contains(searchTerms));

This would work if searchTerms was a string but I've been trying to get it to work with a list of strings.

Essentially I need SQL which would say

SELECT * FROM Data
WHERE Name LIKE '%car%'
OR Name LIKE '%232%'
OR Code LIKE '%car%'
OR Code LIKE '%232%'

linq where list contains any in list seems to be the closes thing I could find to the situation.

Where(data => searchTerms.Contains(data.Name) || searchTerms.Contains(data.Code) only brings back exact matches to the search terms list.

I have tried searching for multiple keyword search in Entity Framework also and have exhausted that effort. Is there any way to achieve what I am aiming for?

Dejadeject

Answer 1

You can try using Any method, I'm not sure whether it's supported but it's worth trying:

var result = context.Data.Where(data => searchTerms.Any(x => data.Name.Contains(x)) ||
                                        searchTerms.Any(x => data.Code.Contains(x));

If this gives you NotSupportedException you can add AsEnumerable before Where to fetch all records and execute the query in memory rather than DB.

Nocuous

What if Data has 1 million records? – Porcine

@Porcine If DB has too many records and this solution impacts performance then create a DB Function and let the database does what the database does ;-). The above worked fine for me with < 5000 records – Edris

Answer 2

I'm late to the party but using the SearchExtensions nuget package you could do something like the following

var result = context.Data.Search(x => x.Name, x => x.Code).Containing(searchTerms);

This builds an expression tree so will still perform the query on the server (and not in memory) and will essentially run the SQL you desire above

Therine

Better than using Any which can generate too deeply nested query and lead to exception. – Zoes

@Therine does this work against EF Core 5? – Leguminous

Answer 3

Following is a fully functional example, of how to implement different kind of searches, using multiple keywords.

This example was especially directed at @Hamza Khanzada 's similar question regarding Pomelo.EntityFrameworkCore.MySql.

It does something similar to the library @NinjaNye named.

---

The simplest approach is used by EqualsQuery(), which just tests the database field against an exact match of a keyword (though casing doesn't matter). This is what @Mohsen Esmailpour suggested.

It generates SQL similar to the following:

SELECT `i`.`IceCreamId`, `i`.`Name`
FROM `IceCreams` AS `i`
WHERE LOWER(`i`.`Name`) IN ('cookie', 'berry')
ORDER BY `i`.`IceCreamId`

However, this is probably not sufficient for your case, because you are not looking for exact matches, but want to return rows with fields that just contain the keyword (and possibly other words as well).

---

A second approach is used by AndContainsQuery(), which is still very simple, but does something slightly different. It returns only results, that contain all the keyword (and possibly orther words as well).

It generates SQL similar to the following:

set @__keyword_0 = 'Cookie';
set @__keyword_1 = 'choco';

SELECT `i`.`IceCreamId`, `i`.`Name`
FROM `IceCreams` AS `i`
WHERE
    (LOCATE(LCASE(@__keyword_0), LCASE(`i`.`Name`)) > 0) AND
    (LOCATE(LCASE(@__keyword_1), LCASE(`i`.`Name`)) > 0)
ORDER BY `i`.`IceCreamId`;

This is not what you want, but I though it would be nice to show as well, because it is very simple and can be done without manually building expression trees.

---

Finally, the third approach is used by orContainsQuery(), and builds part of the expression tree manually. It constructs the body of the WHERE expression of multiple nested OR expressions. This is what you want.

It generates SQL similar to the following:

set @__keyword_0 = 'berry';
set @__keyword_1 = 'Cookie';

SELECT `i`.`IceCreamId`, `i`.`Name`
FROM `IceCreams` AS `i`
WHERE
    (LOCATE(LCASE(@__keyword_0), LCASE(`i`.`Name`)) > 0) OR
    (LOCATE(LCASE(@__keyword_1), LCASE(`i`.`Name`)) > 0)
ORDER BY `i`.`IceCreamId`;

---

Here is the fully functional console project:

using System;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Storage;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql(
                    "server=127.0.0.1;port=3306;user=root;password=;database=so60914868",
                    b => b.ServerVersion(new ServerVersion("8.0.20-mysql")))
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>(
                entity =>
                {
                    entity.HasData(
                        new IceCream {IceCreamId = 1, Name = "Vanilla"},
                        new IceCream {IceCreamId = 2, Name = "Berry"},
                        new IceCream {IceCreamId = 3, Name = "Strawberry"},
                        new IceCream {IceCreamId = 4, Name = "Berry & Fruit"},
                        new IceCream {IceCreamId = 5, Name = "cookie"},
                        new IceCream {IceCreamId = 6, Name = "Chocolate chip cookie"},
                        new IceCream {IceCreamId = 7, Name = "Choco-Cookie & Dough"});
                });
        }
    }

    internal class Program
    {
        private static void Main()
        {
            using (var context = new Context())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();
            }

            EqualsQuery();
            AndContainsQuery();
            OrContainsQuery();
        }

        private static void EqualsQuery()
        {
            //
            // This will find only matches that match the word exactly (though case-insensitive):
            //

            using var context = new Context();
            
            var keywords = new[] {"Cookie", "berry"}
                .Select(s => s.ToLower())
                .ToArray();

            var equalsResult = context.IceCreams
                .Where(i => keywords.Contains(i.Name.ToLower()))
                .OrderBy(i => i.IceCreamId)
                .ToList();

            Debug.Assert(equalsResult.Count == 2);
            Debug.Assert(
                equalsResult[0]
                    .Name == "Berry");
            Debug.Assert(
                equalsResult[1]
                    .Name == "cookie");
        }

        private static void AndContainsQuery()
        {
            //
            // This will find matches, that contain ALL keywords (and other words, case-insensitive):
            //

            using var context = new Context();
            
            var keywords = new[] {"Cookie", "choco"};

            var andContainsQuery = context.IceCreams.AsQueryable();

            foreach (var keyword in keywords)
            {
                andContainsQuery = andContainsQuery.Where(i => i.Name.Contains(keyword, StringComparison.CurrentCultureIgnoreCase));
            }

            var andContainsResult = andContainsQuery
                .OrderBy(i => i.IceCreamId)
                .ToList();

            Debug.Assert(andContainsResult.Count == 2);
            Debug.Assert(
                andContainsResult[0]
                    .Name == "Chocolate chip cookie");
            Debug.Assert(
                andContainsResult[1]
                    .Name == "Choco-Cookie & Dough");
        }

        private static void OrContainsQuery()
        {
            //
            // This will find matches, that contains at least one keyword (and other words, case-insensitive):
            //

            using var context = new Context();
            
            var keywords = new[] {"Cookie", "berry"};

            // The lambda parameter.
            var iceCreamParameter = Expression.Parameter(typeof(IceCream), "i");

            // Build the individual conditions to check against.
            var orConditions = keywords
                .Select(keyword => (Expression<Func<IceCream, bool>>) (i => i.Name.Contains(keyword, StringComparison.OrdinalIgnoreCase)))
                .Select(lambda => (Expression) Expression.Invoke(lambda, iceCreamParameter))
                .ToList();

            // Combine the individual conditions to an expression tree of nested ORs.
            var orExpressionTree = orConditions
                .Skip(1)
                .Aggregate(
                    orConditions.First(),
                    (current, expression) => Expression.OrElse(expression, current));
            
            // Build the final predicate (a lambda expression), so we can use it inside of `.Where()`.
            var predicateExpression = (Expression<Func<IceCream, bool>>)Expression.Lambda(
                orExpressionTree,
                iceCreamParameter);

            // Compose and execute the query.
            var orContainsResult = context.IceCreams
                .Where(predicateExpression)
                .OrderBy(i => i.IceCreamId)
                .ToList();

            Debug.Assert(orContainsResult.Count == 6);
            Debug.Assert(orContainsResult[0].Name == "Berry");
            Debug.Assert(orContainsResult[1].Name == "Strawberry");
            Debug.Assert(orContainsResult[2].Name == "Berry & Fruit");
            Debug.Assert(orContainsResult[3].Name == "cookie");
            Debug.Assert(orContainsResult[4].Name == "Chocolate chip cookie");
            Debug.Assert(orContainsResult[5].Name == "Choco-Cookie & Dough");
        }
    }
}

Bufford

Great! In ef core 5 instead of i.Name.Contains(keyword, StringComparison.CurrentCultureIgnoreCase) you have to use i.Name.Contains(keyword) in order to get IQuearyable otherwise it will be client evaluation. MS docs go.microsoft.com/fwlink/?linkid=2101038 – Buddhism

Answer 4

This request will be executed on the database side

var searchTerms = new List<string> { "car", "232" };
Expressions<Func<Data, bool>> expression = it => false;
foreach(var searchTerm in searchTerms)
{
    expression = expression.Or(it => it.Name.Contains(searchTerm));
    //you can implement your own 'Or' extensions method, 
    //or use third-party libraries, i.e. LinqKit
}
var result = context.Data.Where(expression);

you can also use the Specification pattern, for code purity

Probabilism

This is a nice solution! I'm trying to write it in generic form, is this possible? – Huan