Is there a way to have multiple likes generated dynamically from an array of string values in EF?

user_1972970 0
Viewed 939

I've set up a search textbox where the search will grab every word individually and search through a field using Contains.

Is there a way to search an array of string through Contains?

//Keep in mind that the array would be generated dynamically through textbox
string[] searchWords = { "hello", "world", "today" };

var articles = _swmDbContext.Articles
                        .Include(c => c.Category)
                        .Where(a => a.Title.Contains(searchWords));

searchWords obiviously does not work but trying to show what I want to achieve. searchWords[0] works because it is just one word.

I also tried below as suggested in other links but now the WHERE clause does not show up in query when i run debugger or profiler:

`var articles = _swmDbContext.Articles
                 .Include(c => c.Category)
                 .Where(a => searchWords.Any(w => a.Title.Contains(w)));

`

asp.net-core ef-core-2.1 c#
1 Answers

It seems like Entity Framework Core does not translate .Any and .All with .Contains in the above query to SQL statements. Instead it loads all otherwise matching data and does the search in memory.

If you want to find Articles which contain all search words in the Title you could dynamically add .Where conditions (I had a test database with Persons and a Comment field):

var query = (IQueryable<Person>)dbContext.Persons
    .Include(p => p.TaxIdentificationNumber);

foreach (var searchWord in searchWords)
{
    query = query.Where(p => p.Comment.Contains(searchWord));
}

var persons = query.ToList();

But if you want to find articles which contain any of the search words then you would need an OR in the .Where clause.

Written manually it would look like this:

.Where(p => p.Comment.Contains(searchWords[0]) || p.Comment.Contains(searchWords[1]))

But you can build the expression dynamically:

Expression<Func<Person, bool>> e1 = p => p.Comment.Contains(searchWords[0]);
Expression<Func<Person, bool>> e2 = p => p.Comment.Contains(searchWords[1]);
Expression<Func<Person, bool>> e3 = p => p.Comment.Contains(searchWords[2]);
var orExpression1 = Expression.OrElse(e1.Body, Expression.Invoke(e2, e1.Parameters[0]));
var orExpression2 = Expression.OrElse(orExpression1, Expression.Invoke(e3, e1.Parameters[0]));
var finalExpression = Expression.Lambda<Func<Person, bool>>(orExpression2, e1.Parameters);

and use it like this:

var persons = dbContext.Persons.Where(finalExpression).ToList();

as a function:

Expression<Func<Person, bool>> BuildOrSearchExpression(string[] searchWords)
{
    // searchWords must not be null or empty

    var expressions = searchWords.Select(s => (Expression<Func<Person, bool>>)(p => p.Comment.Contains(s))).ToList();

    if (expressions.Count == 1) return expressions[0];

    var orExpression = expressions.Skip(2).Aggregate(
        Expression.OrElse(expressions[0].Body, Expression.Invoke(expressions[1], expressions[0].Parameters[0])),
        (x, y) => Expression.OrElse(x, Expression.Invoke(y, expressions[0].Parameters[0])));

    return Expression.Lambda<Func<Person, bool>>(orExpression, expressions[0].Parameters);
}

and use it

var persons = dbContext.Persons
    .Include(p => p.TaxIdentificationNumber)
    .Where(BuildOrSearchExpression(searchWords))
    .ToList();

If you exchange the .OrElse with .AndAlso all search words must be found like with multiple .where clauses.

When I did some research I also stumbled upon the PredicatedBuilder http://www.albahari.com/nutshell/predicatebuilder.aspx and this SearchExtension https://stackoverflow.com/a/31682364/5550687. But I have not tried them and I don't know if they work with EF Core.

user_55506870
Related