none
how to make 'where id in (subquery)' query into expression in linq? RRS feed

  • Question

  • 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 and don't have time to learn to do this properly.

    TIA,
    Marcel


    --
    Tuesday, January 6, 2009 9:11 PM

Answers

  • The query you want looks like this:

        from u in Users
        where u.lastname.Contains("fra") &&
              (from c in CompanyRolesToUsers where selectedRoles.Contains(c.CompanyRoleId) select c.UserId)
               .Contains(u.UserId)
        select u;

    Use the Enumerable.Contains method to get a SQL 'in' clause.  It will give you both a list of literals or a nested subquery, depending on whether the left-hand side is a local collection or is part of the query.

    If you need to conditionally add predicates you can do it w/o directly manipulating expressions.  Adding additional where clauses is the same as using 'And'.

    IQueryable<User> query = from u in Users select u;

    if (!string.IsNullOrEmpty(TextBoxLastName.Text))
    {
         query = query.Where(u => u.LastName.Contains(TextBoxLastName.Text.Trim());


    int[] selectedRoles = GetSelectedRoles();
    if( selectedRoles.Length > 0 )
    {
        query = query.Where(u => (from c in CompanyRolesToUsers
                                                    where selectedRoles.Contains(c.CompanyRoleId)
                                                    select c.UserId).Contains(u.UserId) );
    }

    e.Result = query;

    Wayward LINQ Lacky
    • Marked as answer by marcel_g Wednesday, January 7, 2009 3:01 PM
    Wednesday, January 7, 2009 7:21 AM
    Moderator

All replies

  • The query you want looks like this:

        from u in Users
        where u.lastname.Contains("fra") &&
              (from c in CompanyRolesToUsers where selectedRoles.Contains(c.CompanyRoleId) select c.UserId)
               .Contains(u.UserId)
        select u;

    Use the Enumerable.Contains method to get a SQL 'in' clause.  It will give you both a list of literals or a nested subquery, depending on whether the left-hand side is a local collection or is part of the query.

    If you need to conditionally add predicates you can do it w/o directly manipulating expressions.  Adding additional where clauses is the same as using 'And'.

    IQueryable<User> query = from u in Users select u;

    if (!string.IsNullOrEmpty(TextBoxLastName.Text))
    {
         query = query.Where(u => u.LastName.Contains(TextBoxLastName.Text.Trim());


    int[] selectedRoles = GetSelectedRoles();
    if( selectedRoles.Length > 0 )
    {
        query = query.Where(u => (from c in CompanyRolesToUsers
                                                    where selectedRoles.Contains(c.CompanyRoleId)
                                                    select c.UserId).Contains(u.UserId) );
    }

    e.Result = query;

    Wayward LINQ Lacky
    • Marked as answer by marcel_g Wednesday, January 7, 2009 3:01 PM
    Wednesday, January 7, 2009 7:21 AM
    Moderator
  • Thanks Matt, that worked!

    Here's the code that I ended up using:

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


    --
    Wednesday, January 7, 2009 3:06 PM
  • Wednesday, January 7, 2009 5:25 PM