how to do subquery in LINQ

Here's an example of the query I'm 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 a FK relationship between CompanyRolesToUsers and Users, but it's a many to many relationship and CompanyRolesToUsers is the junction table.

We already have most of our site built, and we already have most of the filtering working by building Expressions using a PredicateExtensions class.

The code for the straightforward 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'm trying to add a predicate for a subselect in another table. (CompanyRolesToUsers)

What I'd like to be able to add is something that does this:

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? It's frustrating because I can write the stored procedure easily, but I'm new to this LINQ thing and I have a deadline. I haven't been able to find an example that matches up, but I'm sure it's there somewhere.

TIA,
Marcel

From stackoverflow

• This is how I've been doing 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 you want or join it as well.

  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;
  

  TheSoftwareJedi : No subquery needed here! Teach the man some sql! :)

  Noah : He asked for a subquery! But I'm sure LINQ changes it all to the same SQL anyway...

• There is no subquery needed with this statement, which 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)
  

  That being said, in LINQ it would 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, are both respectable ways to represent this. I prefer the explicit "join" syntax in both cases myself, but there it is...

  Pop Catalin : A very nice answer, but all these methods behave differently and usually produce very different sql, sql that might perform good or bad, usually using a straight join produces the best sql, but not always ...

  TheSoftwareJedi : not "very different sql", just "different sql". I won't argue the differences, but in my experience, there is none as it pertains to performance. Any optimizer can figure out the joins from the where clause.

  Pop Catalin : In my experience for reasonably complex queries, the way you write linq queries makes a difference sql server 2005 sometimes doesn't produce an optimal query plan for queries with deep levels of subquery nesting ... I had to optimize some complex linq queries more than a few times ...

  marcel_g : Thanks for the help. While this does help me understand how to translate SQL into LINQ queries, your starting queries return different results -> if a User has multiple roles, then their record will be returned multiple times, which is why I avoided the join in the first place.

  marcel_g : Actually, if I add a 'distinct' to the SQL, then I get the correct records. I'll add another post with the working join.

  David B : Hmm, why "filter" when you can "join and distinct". Is it because "join and distinct" are natural and exciting sql while "filter" just isn't exciting enough?

  TheSoftwareJedi : add a distinct... it's quite easy... don't be a whiner. Run the plans, give me facts, then come back and bitch.

• You could do something like this for your case - (syntax may be a bit off). Also look at this 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;
  

  TheSoftwareJedi : No subquery needed here! Teach the man some sql!

  Perpetualcoder : I just meant to show he wanted to do it..not enforce my own opinion

• Here's a version of the 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 checkbox list by the web app user, and I forgot to mention that I just hardcoded that for simplicity. Really it's an array of CompanyRoleId values, so it could be (1) or (2,5) or (1,2,3,4,6,7,99).

  Also the other thing that I should specify more clearly, is that the PredicateExtensions are used to dynamically add predicate clauses to the Where for the query, depending on which form fields the web app user has filled in. So the tricky part for me is how to transform the working query into a LINQ Expression that I can attach to the dynamic list of expressions.

  I'll give some of the sample LINQ queries a shot and see if I can integrate them with our code, and then get post my results. Thanks!

  marcel

• Here's a 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 portion of the question:

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

  I strongly recommend extracting the string from the textbox before authoring the query.

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

  You want to maintain good control over what gets sent to the database. In the original code, one possible reading is that an untrimmed string gets sent into the database for trimming - which is not good work for the database to be doing.

  marcel_g : Thanks for the helpful code. That certainly shows how things can be chained together. You are also probably right about performing the string operation before handing it to Linq, it certainly wouldn't hurt to keep things separated.

• Ok, here's a basic join query that gets the correct records:

     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's 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 is thanks to a poster at the LINQtoSQL forum