LINQ flavored IS IN Query

Question

There are quite a few other questions similiar to this but none of them seem to do what I'm trying to do. I'd like pass in a list of string and query

SELECT ownerid where sysid in ('', '', '') -- i.e. List<string>

or like

var chiLst = new List<string>();
var parRec = Lnq.attlnks.Where(a => a.sysid IN chiList).Select(a => a.ownerid);

I've been playing around with a.sysid.Contains() but haven't been able to get anywhere.

+1 if you think there should be a first answer to your question by Jon Skeet Badge lol — bumble_bee_tuna, Apr 16 '12 at 18:28

Jon Skeet · Accepted Answer · 2012-04-16T18:29:11.937

11

Contains is the way forward:

var chiLst = new List<string>();
var parRec = Lnq.attlnks.Where(a => chiList.Contains(a.sysid))
                        .Select(a => a.ownerid);

Although you'd be better off with a HashSet<string> instead of a list, in terms of performance, given all the contains checks. (That's assuming there will be quite a few entries... for a small number of values, it won't make much difference either way, and a List<string> may even be faster.)

Note that the performance aspect is assuming you're using LINQ to Objects for this - if you're using something like LINQ to SQL, it won't matter as the Contains check won't be done in-process anyway.

edited Apr 16 '12 at 18:29

answered Apr 16 '12 at 18:22

Jon Skeet

1,421,763
867
9,128
9,194

3

For something like Linq to SQL, HashSet won't make any difference, since it just takes all the elements of the list and generates the IN clause. – RobSiklos Apr 16 '12 at 18:25
@Jon Skeet What is the difference using HashSet Jon ? – bumble_bee_tuna Apr 16 '12 at 18:26
2

@mellamokb [dude, he's jon skeet](http://stackoverflow.com/questions/343852/whats-a-good-algorithm-to-determine-if-an-input-is-a-perfect-square#comment178355_343862) – GSerg Apr 16 '12 at 18:27
@Gserg: I know... Cool link btw – mellamokb Apr 16 '12 at 18:27
@RobSiklos: True - I was assuming this was being done in-process. Will edit to clarify. – Jon Skeet Apr 16 '12 at 18:28
@GSerg - He works hard to create good answers, though. It's not like he was *born* Jon Sk...oh, nevermind. – D'Arcy Rittich Apr 16 '12 at 18:29
1

@bumble_bee_tuna: `HashSet` is optimized for efficient containment checks - whereas for `List` it has to check each value in turn. – Jon Skeet Apr 16 '12 at 18:29
@Jon Skeet This does what I need and the HashSet is Faster, win win. Thanks – bumble_bee_tuna Apr 16 '12 at 18:40

score 1 · Answer 2 · answered Apr 16 '12 at 18:23

1

You wouldn't call a.sysid.Contains; the syntax for IN (SQL) is the reverse of the syntax for Contains (LINQ)

var parRec = Lnq.attlnks.Where(a => chiList.Contains(a.sysid))
                        .Select(a => a.ownerid);

answered Apr 16 '12 at 18:23

Adam Robinson

182,639
35
285
343

score 1 · Answer 3 · answered Apr 16 '12 at 18:28

1

In addition to the Contains approach, you could join:

var parRec = from a in Lnq.attlnks
             join sysid in chiLst
                 on a.sysid equals sysid
             select a.ownerid

I'm not sure whether this will do better than Contains with a HashSet, but it will at least have similar performance. It will certainly do better than using Contains with a list.

answered Apr 16 '12 at 18:28

phoog

42,068
6
79
117

Assuming LINQ to Objects, it will basically build a `HashSet` within the join implementation. (And it will be faster than a `List` *if the list grows large* - if the list is very short, then a hash-based implementation could be slower, as it needs to generate the hash of the target first. There are all kinds of variables in play, really.) – Jon Skeet Apr 16 '12 at 18:31

LINQ flavored IS IN Query

3 Answers3