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?

Adam Robinson · Accepted Answer · 2009-05-07 19:23:52Z

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 7, 2009 at 19:23

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Dell Over a year ago

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<int>. Not sure if that affects the above solution or not. But that's how I got it to work.

The Muffin Man Over a year ago

This saved me a double foreach loop

TheLegendaryCopyCoder Over a year ago

Cool, can this be done using the object syntax instead of query syntax?

Adam Robinson Over a year ago

@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();

Noldorin · Accepted Answer · 2009-05-07 19:24:37Z

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 7, 2009 at 19:24

Noldorin

148k56 gold badges273 silver badges308 bronze badges

1 Comment

JoshYates1980 Over a year ago

you submitted 1 minuted too slow to be marked as answer. :-) +1

Matt Dell · Accepted Answer · 2009-05-07 19:38:12Z

2

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

answered May 7, 2009 at 19:38

Matt Dell

9,60912 gold badges43 silver badges58 bronze badges

Comments

Solmead · Accepted Answer · 2009-05-07 19:23:56Z

1

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.

answered May 7, 2009 at 19:23

Solmead

4,2092 gold badges28 silver badges30 bronze badges

Collectives™ on Stack Overflow

Linq query with Array in where clause?

4 Answers 4

4 Comments

1 Comment

Thanks all,

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Thanks all,

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related