Searching for multiple strings using single database query with entity framework and LINQ

Question

Assuming I have a database table (aTable) with two columns

id : int

name: string

Requirements: I want to retrieve entries where aTable.name is like a list of strings (stringsToSearchFor).

What I am doing: Currently I am using the following approach

var result=new List<aTable>; 
foreach (var aString in stringsToSearchFor)
    {
        var r = Context.Set<aTable>()
                .Any(s => s.name.Contains(searchString))
                .ToList();
        res.AddRange(r);
    }
return result.Distinct();

To optimize it I tried to change the code by eliminating the foreach, to be:

return Context.Set<aTable>()
                    .Any(s => stringsToSearchFor.Contains(s.name))
                    .Distinct()
                    .ToList();

However, this didn't provide the same results as the previous statement. I believe the first statement is correct.

My question: How can I search for multiple strings without creating N database queries (like the 2nd approach)?

I usually use Context.Set<aTable>().AsEnumerable().Select(x => x.Field<string>("name")).Distinct().ToList();. — jdweng
– jdweng, Commented Aug 20, 2018 at 9:49
This selects all entries in column name from the table. But I want to select entries that match (LIKE sql statement) a list of strings (not a single string) — NuminousName
– NuminousName, Commented Aug 20, 2018 at 9:51
Then use Context.Set<aTable>().AsEnumerable().Select(x => x.Field<string>("name")).Distinct().Where(x => stringsToSearchFor.Contains(x)).ToList(); — jdweng
– jdweng, Commented Aug 20, 2018 at 9:54

V0ldek · Accepted Answer · 2020-07-13 20:01:44Z

3

Alternative solution: use the EF 6.2 Like:

.Where(x => stringsToSearchFor.Any(s => DbFunctions.Like(x.name, "%" + s + "%")))

Here's the documentation for DbFunctions.Like.

edited Jul 13, 2020 at 20:01

answered Aug 20, 2018 at 10:01

V0ldek

10.9k3 gold badges36 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ashilon Over a year ago

Awesome solution! The only that I needed to change was $"%{s}%" to string concatenation because this translates to string. Format which EF can't understand. Thanks!

V0ldek Over a year ago

@ashilon Didn't think of that! I updated the answer to string concatenation to avoid confusion.

Cosmin · Accepted Answer · 2018-08-20 09:56:10Z

0

Something like this should work:

string[] stringsToSearchFor = new string[] { "text1", "text2" };

using (MyDbContext model = new MyDbContext())
{
    var result = model.aTable
        .Where(r => stringsToSearchFor.Any(s => r.name.Contains(s)))
        .ToList();
}

answered Aug 20, 2018 at 9:56

Cosmin

2,3652 gold badges25 silver badges31 bronze badges

Collectives™ on Stack Overflow

Searching for multiple strings using single database query with entity framework and LINQ

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related