linq subquery syntax explanation

Question

I'm trying to create a subquery on my code and was able to create a subquery but I cannot understand some parts.

Below is the subquery that I followed and my comments on what I understand.

List<int> IdsToFind = new List<int>() {2, 3, 4}; 
db.Users
.Where(u => SqlMethods.Like(u.LastName, "%fra%"))
.Where(u =>
       db.CompanyRolesToUsers // the table inside the subquery
      .Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId)) //the filter inside the subquery
      .Select(crtu =>  crtu.UserId) //I cannot understand why there is a select of column here 
      .Contains(u.Id) //I cannot understand the additional filter here
)

Below is the sample query of the above LINQ

SELECT * FROM Users WHERE Users.lastname LIKE '%fra%'AND Users.Id IN (
     SELECT UserId 
     FROM CompanyRolesToUsers 
     WHERE CompanyRoleId in (2,3,4))

The code is from How to do subquery in linq

PS: I used the codes of others so that I could give a simple sample of my question. My code is complicated. Thank you.

score 1 · Accepted Answer · answered Mar 21 '16 at 02:51

You're picking just the IDs out of the subquery because that's all you need, and then you're matching them into the bigger set.

.Select(crtu =>  crtu.UserId)

This is discarding all of the extra data you don't need. It's doing the exact same thing as SELECT UserId

.Contains(u.Id)

This is restricting the subquery to just entries that are in the list being searched for. It's analogous to WHERE CompanyRoleId in (2,3,4)) in the original.

Let me know if any part of that needs more clarification and I can try to help more.

thanks so much that was a stupid question I finally realizded that the `where` was for the foreign keys and the `select` and `contain` are for filter. — makoto, Mar 21 '16 at 03:45

linq subquery syntax explanation

1 Answers1