How do you check if a string contains any strings from a list in Entity Framework?

c# entity-framework

Question

I am trying to search a database to see if a string contains elements of a list of search terms.

var searchTerms = new List<string> { "car", "232" };
var result = context.Data.Where(data => data.Name.Contains(searchTerms) ||
                                        data.Code.Contains(searchTerms));

This would work if searchTerms was a string but I've been trying to get it to work with a list of strings.

Essentially I need SQL which would say

SELECT * FROM Data
WHERE Name LIKE '%car%'
OR Name LIKE '%232%'
OR Code LIKE '%car%'
OR Code LIKE '%232%'

linq where list contains any in list seems to be the closes thing I could find to the situation.

Where(data => searchTerms.Contains(data.Name) || searchTerms.Contains(data.Code) only brings back exact matches to the search terms list.

I have tried searching for multiple keyword search in Entity Framework also and have exhausted that effort. Is there any way to achieve what I am aiming for?

1
8
5/23/2017 11:47:11 AM

Accepted Answer

You can try using Any method, I'm not sure whether it's supported but it's worth trying:

var result = context.Data.Where(data => searchTerms.Any(x => data.Name.Contains(x)) ||
                                        searchTerms.Any(x => data.Code.Contains(x));

If this gives you NotSupportedException you can add AsEnumerable before Where to fetch all records and execute the query in memory rather than DB.

13
12/4/2014 1:54:59 PM

Popular Answer

I'm late to the party but using the SearchExtensions nuget package you could do something like the following

var result = context.Data.Search(x => x.Name, x => x.Code).Containing(searchTerms);

This builds an expression tree so will still perform the query on the server (and not in memory) and will essentially run the SQL you desire above



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