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?

Answer 1

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");
        }
    }
}