Linq query with Array in where clause?

Question

I have searched for this, but still can't seem to get this to work for me. I have an array of Id's associated with a user (their Organization Id). These are placed in an int[] as follows:

int[] OrgIds = (from oh in this.Database.OrganizationsHierarchies
                       join o in this.Database.Organizations on oh.OrganizationsId equals o.Id
                       where (oh.Hierarchy.Contains(@OrgId))
                          || (oh.OrganizationsId == Id)
                       select o.Id).ToArray();

The code there isn't very important, but it shows that I am getting an integer array from a Linq query.

From this, though, I want to run another Linq query that gets a list of Personnel, that code is as follows:

List<Personnel> query = (from p in this.Database.Personnels
                                where (search the array)
                                select p).ToList();

I want to add in the where clause a way to select only the users with the OrganizationId's in the array. So, in SQL where I would do something like "where OrganizationId = '12' or OrganizationId = '13' or OrganizatonId = '17'."

Can I do this fairly easily in Linq / .NET?

score 47 · Accepted Answer · answered May 07 '09 at 19:23

47

While this is probably better suited to a join, you can use this:

List<Personnel> query = 
    (from p in this.Database.Personnels 
    where OrgIds.Contains(p.OrgID) select p).ToList();

This will translate into SQL something like..

where OrgID in (1,2,...,n)

answered May 07 '09 at 19:23

Adam Robinson

182,639
35
285
343

1

Yes sir! You are correct. I, of course, figured this out about 2 minutes after I posted this (which I posted in frustration after 2 hours of attempts) Anyway, I ended up making the int[] a List. Not sure if that affects the above solution or not. But that's how I got it to work. – Matt Dell May 07 '09 at 19:32
1

This saved me a double foreach loop – The Muffin Man Jun 04 '11 at 01:18
Cool, can this be done using the object syntax instead of query syntax? – TheLegendaryCopyCoder Apr 18 '14 at 09:21
1

@TheLegendaryCopyCoder: By "object syntax" do you mean using the extension methods directly instead of using the query syntax? If so, sure. `var query = this.Database.Personnels.Where(p => OrgIds.Contains(p.OrgID)).ToList();` – Adam Robinson Apr 18 '14 at 12:18

score 7 · Answer 2 · answered May 07 '09 at 19:24

7

A check using the Contains method should do the job here.

var query = (from p in this.Database.Personnels
             where OrgIds.Contains(p.OrganisationId)
             select p).ToList();

answered May 07 '09 at 19:24

Noldorin

144,213
56
264
302

1

you submitted 1 minuted too slow to be marked as answer. :-) +1 – JoshYates1980 Apr 03 '15 at 19:26

score 2 · Answer 3 · answered May 07 '09 at 19:38

I wanted to give Adam credit for the answer, but I also wanted to share the code I used to make this work:

List<int> OrgIds= (from oh in this.Database.OrganizationsHierarchies
                       join o in this.Database.Organizations on oh.OrganizationsId equals o.Id
                       where (oh.Hierarchy.Contains(@OrgId))
                          || (oh.OrganizationsId == Id)
                       select o.Id).ToList();

List<Personnel> query = (from p in this.Database.Personnels
                                where (OrgIds.Contains(p.OrganizationId))
                                select p).ToList();

Thanks all,

-Matt

score 1 · Answer 4 · answered May 07 '09 at 19:23

It would be something like this, OrgIds.ToList.Contains(p.OrginizationID)

Though really I would do it more like this:

var OrgIds = (from oh in this.Database.OrganizationsHierarchies
                   join o in this.Database.Organizations on oh.OrganizationsId equals o.Id
                   where (oh.Hierarchy.Contains(@OrgId))
                      || (oh.OrganizationsId == Id)
                   select o.Id);
List<Personnel> query = (from p in this.Database.Personnels
                            where (OrgIds.Contains(p.OrigizationID)
                            select p).ToList();

That way the final query to get personnel will execute containing the combined query from both.

Linq query with Array in where clause?

4 Answers4

Thanks all,

Linked