Linq query with Array in where clause?
Solution 1
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)
Solution 2
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();
Solution 3
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
Solution 4
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.
Matt Dell
I'm good enough, I'm smart enough, and doggone it, people like me.
Updated on August 17, 2022Comments
-
Matt Dell over 1 year
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?