How to make a subquery in LINQ

Question

Here is an example query that I am trying to convert to LINQ:

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

There is an FK relationship between CompanyRolesToUsers and Users , but this relationship is many, many, and CompanyRolesToUsers is a join table.

We already have most of our site, and we already have most of the filtering, working by creating expressions using the PredicateExtensions class.

The code for simple filters looks something like this:

  if (!string.IsNullOrEmpty(TextBoxLastName.Text)) { predicateAnd = predicateAnd.And(c => c.LastName.Contains( TextBoxLastName.Text.Trim())); } e.Result = context.Users.Where(predicateAnd); 

I am trying to add a predicate for a subquery in another table. ( CompanyRolesToUsers )

What I would like to add is what this does:

 int[] selectedRoles = GetSelectedRoles(); if( selectedRoles.Length > 0 ) { //somehow only select the userid from here ???: var subquery = from u in CompanyRolesToUsers where u.RoleID in selectedRoles select u.UserId; //somehow transform this into an Expression ???: var subExpression = Expression.Invoke(subquery); //and add it on to the existing expressions ???: predicateAnd = predicateAnd.And(subExpression); } 

Is there any way to do this? This is frustrating because I can easily write a stored procedure, but I am new to this LINQ function and I have a deadline. I could not find an example that matches, but I am sure that it is somewhere there.

Answer 1

Here is the subquery for you!

 List<int> IdsToFind = new List<int>() {2, 3, 4}; db.Users .Where(u => SqlMethods.Like(u.LastName, "%fra%")) .Where(u => db.CompanyRolesToUsers .Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId)) .Select(crtu => crtu.UserId) .Contains(u.Id) ) 

---

Regarding this part of the question:

 predicateAnd = predicateAnd.And(c => c.LastName.Contains( TextBoxLastName.Text.Trim())); 

I highly recommend extracting a string from a text box before creating a query.

 string searchString = TextBoxLastName.Text.Trim(); predicateAnd = predicateAnd.And(c => c.LastName.Contains( searchString)); 

You want to keep good control over what is sent to the database. In the source code, one possible read is that the raw string is sent to the database for trimming - which works poorly for the database.

Answer 2

There is no subquery in this statement that is better written as

 select u.* from Users u, CompanyRolesToUsers c where u.Id = c.UserId --join just specified here, perfectly fine and u.lastname like '%fra%' and c.CompanyRoleId in (2,3,4) 

or

 select u.* from Users u inner join CompanyRolesToUsers c on u.Id = c.UserId --explicit "join" statement, no diff from above, just preference where u.lastname like '%fra%' and c.CompanyRoleId in (2,3,4) 

In LINQ, it will be

 from u in Users from c in CompanyRolesToUsers where u.Id == c.UserId && u.LastName.Contains("fra") && selectedRoles.Contains(c.CompanyRoleId) select u 

or

 from u in Users join c in CompanyRolesToUsers on u.Id equals c.UserId where u.LastName.Contains("fra") && selectedRoles.Contains(c.CompanyRoleId) select u 

Which again, both respected ways to present this. I prefer the explicit join syntax in both cases myself, but there it is ...

Answer 3

This is how I did subqueries in LINQ, I think this should get what you want. You can replace the explicit CompanyRoleId == 2 ... with another subquery for the different roles that you want, or join it.

 from u in Users join c in ( from crt in CompanyRolesToUsers where CompanyRoleId == 2 || CompanyRoleId == 3 || CompanyRoleId == 4) on u.UserId equals c.UserId where u.lastname.Contains("fra") select u; 

Answer 4

You can do something similar for your case - (the syntax may be a bit off). Also look at the link

 subQuery = (from crtu in CompanyRolesToUsers where crtu.RoleId==2 || crtu.RoleId==3 select crtu.UserId).ToArrayList(); finalQuery = from u in Users where u.LastName.Contains('fra') && subQuery.Contains(u.Id) select u; 

Answer 5

Ok, here is a basic connection request that gets the correct entries:

  int[] selectedRolesArr = GetSelectedRoles(); if( selectedRolesArr != null && selectedRolesArr.Length > 0 ) { //this join version requires the use of distinct to prevent muliple records //being returned for users with more than one company role. IQueryable retVal = (from u in context.Users join c in context.CompanyRolesToUsers on u.Id equals c.UserId where u.LastName.Contains( "fra" ) && selectedRolesArr.Contains( c.CompanyRoleId ) select u).Distinct(); } 

But here is the code that most easily integrates with the algorithm that we already had in place:

 int[] selectedRolesArr = GetSelectedRoles(); if ( useAnd ) { predicateAnd = predicateAnd.And( u => (from c in context.CompanyRolesToUsers where selectedRolesArr.Contains(c.CompanyRoleId) select c.UserId).Contains(u.Id)); } else { predicateOr = predicateOr.Or( u => (from c in context.CompanyRolesToUsers where selectedRolesArr.Contains(c.CompanyRoleId) select c.UserId).Contains(u.Id) ); } 

which thanks to the poster on the LINQtoSQL forum

Answer 6

Here is the version of SQL that returns the correct records:

 select distinct u.* from Users u, CompanyRolesToUsers c where u.Id = c.UserId --join just specified here, perfectly fine and u.firstname like '%amy%' and c.CompanyRoleId in (2,3,4) 

Also note that (2,3,4) is a list selected from a list of checkboxes by a web application user, and I forgot to mention that I just hard-coded this for simplicity. Indeed, this is an array of CompanyRoleId values, so it can be (1) or (2.5) or (1,2,3,4,6,7,99).

In addition, another thing that I need to point out more clearly is that PredicateExtensions are used to dynamically add predicate sentences to the Where address for the request, depending on which form fields the user of the web application has filled out. So the hard part for me is how to convert a working query into a LINQ expression, which I can attach to a dynamic list of expressions.

I will give some of the LINQ query examples and see if I can integrate them with our code, and then publish my results. Thank!

Marcel