CODEWITHSUNDEEP
c#linqlambda
Hasitha Ruckshan Haththella
Hasitha Ruckshan Haththella

Reputation: 48

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

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?

Upvotes: 1

Views: 196

Answers (3)

Magnus
Magnus

Reputation: 46919

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

Upvotes: 2

Debajit Mukhopadhyay
Debajit Mukhopadhyay

Reputation: 4172

You can try this:

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

Upvotes: 1

Servy
Servy

Reputation: 203821

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;

Upvotes: 1

Related Questions