How to use "in" where clause in LINQ where the string set will be supplied by a list?

Question

I have a need to search a data source using LINQ.

In essence, the query that I want would be something like:

select * from table where id in ("1","2","4");

The added "complexity" is that the values inside the parenthesis will be coming out of string list.

This is my code:

    public void getAdUserData(List<String> _inADUserDatas)
    {
        var records = from _adUserDatas in _adUserDataDBDataContex.ADUserDatas
            where
                new string[] { (char)34 + String.Join((char)34 + "," + (char)34, _inADUserDatas) + (char)34 }.Contains(_adUserDatas.fan)
            orderby _adUserDatas.fan
            select _adUserDatas;

        var test = records.ToList();
        Console.WriteLine((char)34 + String.Join((char)34 + "," + (char)34, _inADUserDatas) + (char)34);

as can be seen, I use String.join to convert my list into a comma-delimited string where every string is enclosed by a double-quote.

I've also tried using the code below without any luck.

  new string[] {String.Join(",", _inADUserDatas) }.Contains(_adUserDatas.fan)

I also tried manually typing some of the ID in the list into the query and I am getting records.

I believe I am doing it right but it's not returning any records. The variable test has a count of 0.

Any ideas what could be wrong? I've gotten the ideas to my code by combining several discussions here in stackoverflow particularly this link here and this other link.

Thanks a lot

score 2 · Answer 1 · answered Jul 08 '14 at 06:36

2

Call Contains on the list itself. Query provider should handle it out of the box:

var records = from _adUserDatas in _adUserDataDBDataContex.ADUserDatas
              where _inADUserDatas.Contains(_adUserDatas.fan)
              orderby _adUserDatas.fan
              select _adUserDatas;

answered Jul 08 '14 at 06:36

MarcinJuraszek

124,003
15
196
263

thank you. it works. I'll give the answer to the other guy as my screen says he answered it first. But your help is much appreciated :) – mrjayviper Jul 08 '14 at 06:38
1

@mrjayviper actually Marcin was 8 seconds faster =) – Uriil Jul 08 '14 at 06:39
1

@mrjayviper I'm glad I could help you solve your problem. And I don't really care if you're going to mark my answer as accepted or not. It's just points ;) – MarcinJuraszek Jul 08 '14 at 06:42

score 1 · Accepted Answer · answered Jul 08 '14 at 06:36

1

Just use contains on your collection:

var records = from _adUserDatas in _adUserDataDBDataContex.ADUserDatas
              where _inADUserDatas.Contains(_adUserDatas.fan)
              orderby _adUserDatas.fan
              select _adUserDatas;

answered Jul 08 '14 at 06:36

Uriil

11,948
11
47
68

it works. thanks. ps. I'm very new to c# so I'm still still a baby :) – mrjayviper Jul 08 '14 at 06:39

score 0 · Answer 3 · answered Jul 08 '14 at 06:39

0

you can use below mentioned code

var list=_adUserDataDBDataContex.ADUserDatas.Where(p=>p.Contains(_adUserDatas.fan));

answered Jul 08 '14 at 06:39

Dhaval Patel

7,471
6
37
70

Although this may answer the question, it doesn't provide an explanation as to why? Please explain why this works, and perhaps why it is the best way of doing it. If the question author new to do what you suggest they obviously would have done it already so explain it to them. – Chris Spittles Jul 08 '14 at 07:02

score 0 · Answer 4 · answered Jul 08 '14 at 06:41

What's the data type for ids?

a simple collection.Where(s=> ids.Contains(s)) will translate to "in" I'm not sure how linq to SQL translates these sorts of queries, but you don't need to sting join with commas in EF, try it without manually trying to create a comma separated list.

How to use "in" where clause in LINQ where the string set will be supplied by a list?

4 Answers4