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

c# entity-framework entity-framework-6 linq linq-to-sql

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)?

1
0
8/20/2018 9:37:58 AM

Popular Answer

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.

0
8/20/2018 10:01:26 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow