linq dynamic where clause with or condition on a INT column

Question

I'm attempting to use a linq query to dynamically select records based on account id. account id is a int column in sql server.

var idList = new int[123, 456];   //these number change with each user.
foreach (var id in idList) {
      query = query.WHERE(cs => cs.pkID = id);
}

The above makes it a AND where clause. I'd rather have it be an OR. The above idList can contain 1 to 100. Varying with each user.

How can I make linq work with a OR clause. Contains doesn't work on INT columns.

Would like the select to look something like this.

 select * from table where id = 123 or id = 456

I have a bunch of other columns that could be appended to the where clause. Getting a dynamic OR working is giving me a headache.

Thanks.

@user3434990 While the link provided as duplicate for this question is very useful, I think using Contains in your case is a better option. — Reza Aghaei, Oct 02 '15 at 14:54

Reza Aghaei · Accepted Answer · 2015-10-02T14:52:29.227

3

You can use cs=>idList.Contains(cs.pkID)

var idList = new List<int>(){123, 456};   
var result = query.Where(cs => idList.Contains(cs.pkID));

This way you don't need any loop and it is equivalent to:

SELECT somecolumns FROM sometable WHERE pkId in (123, 456)

In your case, using Contains, makes more sense than creating dynamic where clause with OR.

edited Oct 02 '15 at 14:52

answered Oct 02 '15 at 14:44

Reza Aghaei

Contains is not an option for idList – user3434990 Oct 02 '15 at 14:46
@user3434990 it is an option when you use a List. – Reza Aghaei Oct 02 '15 at 14:47
i believe you can use `idList.Cast().Contains(cs.pkID)` if you have to use array – JamieD77 Oct 02 '15 at 14:56
Thanks Reza that worked – user3434990 Oct 02 '15 at 14:57
@user3434990 you are welcome:) – Reza Aghaei Oct 02 '15 at 14:57

score 0 · Answer 2 · answered Oct 02 '15 at 14:46

0

query = query.WHERE(cs => idList.Contains(cs.pkID);

You can use something like that.

Watch our with your original query: = is not == in C#.

answered Oct 02 '15 at 14:46

Carra

as mentioned above idList.Contains is not an option..what I mean is it isn't available in the list of properties. – user3434990 Oct 02 '15 at 14:48
Just add a .ToList()? – Carra Oct 02 '15 at 14:51

2 Answers2