How to match a list of item to database in C#

Question

I have a list of string

List<string> list = new List<string>();
list.Add("john");
list.Add("David");
list.Add("Sam");

Now I want to check whether my column in database contains these list Items.

var v = db.employee.where(s => s.Content.Contains(list));

My question is how can I match all list items to database column in just one query without using any loop. The query must return result if single list item is matched with column. The query I mentioned above not working. Please help me to solve this.

*" I want to check whether my column in database contains these list Items"*. Is that really what you want or it's `the list contains your column value`? — Ivan Stoev, Mar 12 '16 at 10:05
Assuming your `s.Content` will have `john`, `David`, `sam`, or other things... I think you should do it the other way round... Nevertheless, it might be best if you could provide how `s.Content` look like... — Ian, Mar 12 '16 at 10:06
@Ian I guess that's the case. Either way it will be a duplicate, I'm stepping out. — Ivan Stoev, Mar 12 '16 at 10:12
Possible duplicate of [Linq to Entities - SQL "IN" clause](http://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause) — Ivan Stoev, Mar 12 '16 at 10:17

score 1 · Answer 1 · answered Mar 12 '16 at 11:30

This will only work with the assumption that your db is an Entity Framework DbContext and that s.Content is a string. If you're using some other ORM then it may not work.

List<string> list = new List<string>();
list.Add("john");
list.Add("David");
list.Add("Sam");

var v = db.employee.Where(s => list.Contains(s.Content)).ToList();

Ivan Gritsenko · Answer 2 · 2016-03-12T10:16:02.803

You can do this:

List<string> list = new List<string>();
list.Add("john");
list.Add("David");
list.Add("Sam");
var v = db.employee
    .ToList()
    // This filtering doesn't happen in your SQL server.
    .Where(s => list.Any(x => s.Content.Contains(x)));

You can try to omit .ToList() line but I'm afraid your ORM will not know how to convert the query to SQL.

If you want the whole query to be executed in SQL you should consider building the query this way:

var query = db.employee.AsQueryable();
query = list.Aggregate(query, 
    (query, name) => query.Where(empl => empl.Content.Contains(name)));

score 0 · Answer 3 · answered Mar 12 '16 at 11:27

0

what about:

 List<string> list = new List<string> {"john", "Warwick", "Sam"};

 var vresult = _db.employee.Where(x => list.Contains(x.Content)).ToList();

please indicate what you are expecting...? like how is it not working?

answered Mar 12 '16 at 11:27

Seabizkit

2,417
2
15
32

score 0 · Answer 4 · answered Mar 12 '16 at 14:53

If return is different than null, exists.

public List<Employee> CheckIfExists(List<string> nameList)
{
    if (nameList == null)
    {
        return null;
    }

    string inClause = "";

    foreach (var item in nameList)
    {
        inClause = string.Format("{0},{1}", inClause, item);
    }

    return db.employee.SqlQuery("SELECT * FROM employee WHERE employee.Name IN (@p0)", inClause).ToList(); 
}

score 0 · Answer 5 · answered Mar 15 '16 at 05:51

0

Each element of list will be passed as an argument to s.Content.Contains and here s is an employee record.

var v = db.employee.Where(s => list.Any(s.Content.Contains));

answered Mar 15 '16 at 05:51

M.S.

4,283
1
19
42

score 0 · Answer 6 · answered Jul 16 '20 at 16:57

This might help you, list with name= {"john", "David", "Sam"} you want to check if John/David/Sam exists in the employee table. The below query is to check if the particular name exists in employee or not

var _data = _db.employee.Where(emp => list.Any(li => li.name == emp.name)).ToList();

score 0 · Answer 7 · answered Jul 16 '20 at 17:41

0

LINQ in query syntax:

var result = from o in db.employee
             where list.Contains(o.Content)
             select o;

answered Jul 16 '20 at 17:41

Meer

678
6
12

How to match a list of item to database in C#

7 Answers7