using linq to find if a text field contains any string in a list

Question

im running this in asp.net core v3.1

my question is similar to this question:
How to use Linq to check if a list of strings contains any string in a list

with the specific question relating to the first answer such that

filterTags = ["abc", "cd", "efg"]
var results = db.People
                .Where(p => filterTags.Any(tag => p.Tags.Contains(tag)));

so basically saying
give me results from the db of all People
who's Tags field contains any of the filterTags
where Tags = a big text field populated by a bunch of space-delimited tags

This seems straightforward (esp since this has been written before)
but i get an error back

System.InvalidOperationException: The LINQ expression 'DbSet .Where(p => __filterTags_0 .Any(tag => p.Tags.Contains(tag)))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()

does anyone know what this means or what im doing wrong?

did you check this ? https://stackoverflow.com/questions/10667675/linq-where-list-contains-any-in-list — TAHA SULTAN TEMURI, Dec 08 '20 at 06:00
@TAHASULTANTEMURI - intersect is used for two lists while im specifically looking for anything in one list to show up somewhere in a long string — toy, Dec 08 '20 at 06:06
I m refering to the next one , you should var People= _db.People.Where(p => p.Tagse.Split(',').ToList().Any(x => filterTags.Contains(x)); — TAHA SULTAN TEMURI, Dec 08 '20 at 06:08
Do this code : `List filterTags = new List { "abc", "cd", "efg" }; var results = db.People.ToList().Where(p => filterTags.Any(tag => p.Tags.Contains(tag))).ToList()` — Yinqiu, Dec 08 '20 at 06:15
Yinqiu: your solution would transfer all thousands of People from the Database to your local process before throwing away most of them. — Harald Coppoolse, Dec 08 '20 at 07:24

score 2 · Accepted Answer · answered Dec 08 '20 at 14:05

This is not possible with pure EF LINQ. You have to create helper which transforms your search list in Expression Tree.

public static class QueryExtensions
{
    private static MethodInfo _containsMethodInfo = typeof(string).GetMethod("Contains")!;

    public static IQueryable<T> FilterUsingContains<T>(this IQueryable<T> query, Expression<Func<T, string>> prop, IList<string> items)
    {
        if (items.Count == 0)
            return query.Where(e => 1 == 2);

        var param = prop.Parameters[0];

        var predicate = items.Select(i =>
                (Expression)Expression.Call(prop.Body, _containsMethodInfo, Expression.Constant(i, typeof(string))))
            .Aggregate(Expression.OrElse);

        var lambda = Expression.Lambda<Func<T, bool>>(predicate, param);

        return query.Where(lambda);
    }
}

Then you can use this extension in your queries

filterTags = ["abc", "cd", "efg"]
var results = db.People
    .Where(p => p.Tags.AsQueryable().FilterUsingContains(t => t, filterTags).Any());

Great! Just a little correction in _constainsMethodInfo (.Net Core 5.0): `private static MethodInfo containsMethod = typeof (string).GetMethod("Contains", new Type[]{typeof (string)});` — Eduardo Moralles, Mar 11 '22 at 17:56

Majid Shahabfar · Answer 2 · 2020-12-09T09:35:19.457

-1

Here is a workaround for you:

using System.Linq; 

string[] filterTags = {"abc", "cd", "efg"};
var results = db.People.Where(p => filterTags.Contains(p.Tags)).ToList();

edited Dec 09 '20 at 09:35

answered Dec 08 '20 at 10:54

Majid Shahabfar

4,010
2
28
36

Your code wouldn't compile. `p.Tags` is clearly a `List`. You're treating it like it is a `string`. – Enigmativity Dec 08 '20 at 11:04
1

@Enigmativity You made a mistake. Just take a precise look at the question. It says "Tags = a big text field populated by a bunch of space-delimited tags", so Tags is a string. – Majid Shahabfar Dec 08 '20 at 12:24
1

There's no overload of `IEnumerable.Contains` that takes a lambda. You code still does not compile. – Enigmativity Dec 09 '20 at 06:50
@Enigmativity Again I should say that you don't read the question exactly. The question has mentioned that the use of Linq must be considered in the workaround. So using Linq the string array can apply the Contains method. – Majid Shahabfar Dec 09 '20 at 08:42
Perhaps you should test your code - the `IEnumerable.Contains` method does not have an overload that takes a lambda. Your code would not compile. – Enigmativity Dec 09 '20 at 08:52
@Enigmativity here is the compilation: https://dotnetfiddle.net/Fjhnhe – Majid Shahabfar Dec 09 '20 at 09:05
The code in your answer contains a lambda. The code in your fiddle does not. `filterTags.Contains(tag => p.Tags)` versus `filterTags.Contains(tags)`. – Enigmativity Dec 09 '20 at 09:14
At least it now compiles. The question now is, do you think a "a big text field populated by a bunch of space-delimited tags" will ever match ***any one of the tags*** in `filterTags`. Let's assume that `Tags` is `"abc cd efg"` then your code does ***NOT*** match. – Enigmativity Dec 09 '20 at 10:08

using linq to find if a text field contains any string in a list

2 Answers2

Linked