How to Efficiently Search for Multiple Keywords in a Database Field Using Lambda/LINQ?

Lambda/LINQ multi-keyword database field search

This article introduces how to use Lambda/LINQ expressions to efficiently search for multiple keywords in database fields (such as Comments fields) at the same time. The traditional Contains method can only search for one keyword at a time, while the method provided in this article can process multiple keywords at the same time.

Using Lambda expressions, you can write queries like this:

<code class="language-csharp">var newList = MainList.Where(m => m.Comments.Contains(purposes));</code>

However, if you need to search for multiple keywords, more advanced techniques are required. For this purpose, we have developed an extension method:

<code class="language-csharp">public static IQueryable<T> FilterByItems<T, TItem>(this IQueryable<T> query, IEnumerable<TItem> items,
    Expression<Func<T, TItem, bool>> filterPattern, bool isOr)</code>

This extension method receives the main list MainList, the keyword list items, a Lambda expression filterPattern (used to check whether the keyword exists in the Comments field), and a Boolean value isOr (specifies the use of OR or AND operators to match keywords).

For example:

<code class="language-csharp">var newList = MainList
    .FilterByItems(keywords, (m, k) => m.Comments.Contains(k), true)
    .ToList();</code>

This query will return a list of records whose Comments field contains any keyword in the keywords list.

Implementation of FilterByItems extension method

The

FilterByItems extension method leverages the ExpressionReplacer class to recursively modify an expression to replace specific parts with the desired value.

The following is the implementation of the ExpressionReplacer class:

<code class="language-csharp">public static class QueryableExtensions
{
    public static IQueryable<T> FilterByItems<T, TItem>(this IQueryable<T> query, IEnumerable<TItem> items,
        Expression<Func<T, TItem, bool>> filterPattern, bool isOr)
    {
        Expression predicate = null;
        foreach (var item in items)
        {
            var itemExpr = Expression.Constant(item);
            var itemCondition = ExpressionReplacer.Replace(filterPattern.Body, filterPattern.Parameters[1], itemExpr);
            if (predicate == null)
                predicate = itemCondition;
            else
            {
                predicate = Expression.MakeBinary(isOr ? ExpressionType.OrElse : ExpressionType.AndAlso, predicate,
                    itemCondition);
            }
        }

        predicate ??= Expression.Constant(false);
        var filterLambda = Expression.Lambda<Func<T, bool>>(predicate, filterPattern.Parameters[0]);

        return query.Where(filterLambda);
    }

    // ... (ExpressionReplacer class remains the same) ...
}</code>

This improved description explains more clearly what the code does and how to use it, and explains key sections in greater detail.

The above is the detailed content of How to Efficiently Search for Multiple Keywords in a Database Field Using Lambda/LINQ?. For more information, please follow other related articles on the PHP Chinese website!