How to write a LINQ query to select from a collection with given set of matching IDs

Question

I'm using a self tracking entity model. ProductInstallation is a DTO which contains all the details about the product installation for a company.

The UserRoles entity holds the relationship in-between the Product-System Role-UserID.

As an example:

Product: Inventory

System Role : PurchasingUser

User ID : hasithaH <- (Suppose me)

using the below LINQ query, I can get the distinct UserIDs.

string[] userIDs = productInstallation.UserRoles
                  .Select(u=>u.UserID).Distinct().ToArray();

now I need to get all the User Profiles for the UserIDs I queried in above steps.

productInstallation.SystemUsers = context.SystemUsers.Select(u=> u.UserID ..???

In SQL point of view, this is the query I want:

Select * from SystemUsers where UserID in ('UserA','UserB','UserC')

How should I write a LINQ query to get this done?

See this similar question: http://stackoverflow.com/questions/959752/where-in-clause-in-linq — Oscar, Jun 17 '13 at 16:38

Magnus · Accepted Answer · 2013-06-17T21:27:30.303

You write it as follows:

var result = context.SystemUsers.Where(su => 
               productInstallation.UserRoles.Any(ur => su.UserID == ur.UserId));

Or if both sources are not IQuerable from the same db:

string[] userIDs = productInstallation.UserRoles
                  .Select(u=>u.UserID).Distinct().ToArray();

var result = context.SystemUsers.Where(su => 
                   userIDs.Contains(su.UserID));

Debajit Mukhopadhyay · Answer 2 · 2013-06-17T18:44:13.323

1

You can try this:

productInstallation.SystemUsers = 
context.SystemUsers.FindAll(u=> userIDs.Contains(u.UserID))

edited Jun 17 '13 at 18:44

answered Jun 17 '13 at 17:55

Debajit Mukhopadhyay

4,072
1
17
22

3

Shouldn't that be `.Where`? – Reed Copsey Jun 17 '13 at 18:06
1

Yes where or we can use FindAll – Debajit Mukhopadhyay Jun 17 '13 at 18:44

score 1 · Answer 3 · answered Jun 17 '13 at 18:04

1

What you really want to do here is join the two tables. Using a Join you can do this in one query rather than executing two separate queries:

var systemUsers = from userRole in UserRoles
                  join systemUser in SystemUsers
                  on userRole.UserID equals systemUser.UserID
                  select systemUser;

answered Jun 17 '13 at 18:04

Servy

202,030
26
332
449

Doing a `join` does not give the same result as an `in` – Magnus Jun 17 '13 at 21:26
@Magnus In the general case, no, but they're very similar operations and often will have the same result. Based on the context it seems that he's logically trying to Join the data, so if that's the relationship between these data sets then either would work, but a Join would be more effective and semantically appropriate. – Servy Jun 18 '13 at 13:57
If the operation is done in the db the effectiveness of a semi join vs an inner join would be almost identical. – Magnus Jun 18 '13 at 21:03

How to write a LINQ query to select from a collection with given set of matching IDs

3 Answers3