linq query for searching lists

Question

I have a List property of type string. I would like to search my database to make sure that the list matches the results. For example:

List<string>strings = new List<string>({"test1","test2","etc"});

I would now like to do a search in my database using linq to check if under certain condition that these strings exist.

_databaseContext.Names.Where(x=>x.name == strings && specialID == 1);

@Amy there's a problem in his where clause although I don't know if that is his problem. — evanmcdonnal, Jun 15 '13 at 01:07
Check out http://stackoverflow.com/questions/4172195/mysql-like-multiple-values (also it is for MySQL) to see what kind of query you may need (OR'ed conditions). — Alexei Levenkov, Jun 15 '13 at 01:28

YK1 · Accepted Answer · 2013-06-15T02:29:47.287

_databaseContext.Names.Where(x => strings.Contains(x.name) && specialID == 1);

If your list of strings is unique, use can use HashSet<> instead of List<>. I think it will give you better performance.

HashSet<string> hs = new HashSet<string> { "test1", "test2", "etc" };

_databaseContext.Names.Where(x => hs.Contains(x.name) && specialID == 1);

EDIT: +1 to Alexei Levenkov for pointing out- Exists will cause problems, should use Contains instead. Also, verified the same query generated for List as well as HashSet so in case you want to query on server, is does not matter whether you use Listor HashSet.

Generated query will be some thing like.

Select [name], [other columns]
From Names
Where [name] IN (N'test1', N'test2', N'etc')

However, assuming specialID is local variable, it will generate a dynamic query similar to above but also pass its value to SQL Server. This query is executed using sp_executesql.

+0: I'm not sure if this will not force query to be executed mostly on client due to function call in the predicate. — Alexei Levenkov, Jun 15 '13 at 01:27

linq query for searching lists

1 Answers1