How to do a subquery in LINQ

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   gt  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  gt  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

User · Accepted Answer

Here s a subquery for you   List lt int gt  IdsToFind   new List lt int gt     2  3  4    db Users  Where u   gt  SqlMethods Like u LastName    fra      Where u   gt      db CompanyRolesToUsers      Where crtu   gt  IdsToFind Contains crtu CompanyRoleId        Select crtu   gt   crtu UserId       Contains u Id        Regarding this portion of the question   predicateAnd   predicateAnd And c   gt  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   gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp  amp        u LastName Contains  fra    amp  amp        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    amp  amp        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

User · Answer

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

User · Answer

Ok  here s a basic join query that gets the correct records      int   selectedRolesArr   GetSelectedRoles        if  selectedRolesArr    null  amp  amp  selectedRolesArr Length  gt  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     amp  amp                              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   gt   from c in context CompanyRolesToUsers                         where selectedRolesArr Contains c CompanyRoleId                          select c UserId  Contains u Id                        else                        predicateOr   predicateOr Or  u   gt   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

User · Answer

Ok  here s a basic join query that gets the correct records      int   selectedRolesArr   GetSelectedRoles        if  selectedRolesArr    null  amp  amp  selectedRolesArr Length  gt  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     amp  amp                              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   gt   from c in context CompanyRolesToUsers                         where selectedRolesArr Contains c CompanyRoleId                          select c UserId  Contains u Id                        else                        predicateOr   predicateOr Or  u   gt   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

User · Answer

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

User · Answer

Ok  here s a basic join query that gets the correct records      int   selectedRolesArr   GetSelectedRoles        if  selectedRolesArr    null  amp  amp  selectedRolesArr Length  gt  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     amp  amp                              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   gt   from c in context CompanyRolesToUsers                         where selectedRolesArr Contains c CompanyRoleId                          select c UserId  Contains u Id                        else                        predicateOr   predicateOr Or  u   gt   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

User · Answer

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  amp  amp        u LastName Contains  fra    amp  amp        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    amp  amp        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

User · Answer

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

User · Answer

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  amp  amp        u LastName Contains  fra    amp  amp        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    amp  amp        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

User · Answer

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

User · Answer

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     amp  amp  subQuery Contains u Id  select u

User · Answer

Ok  here s a basic join query that gets the correct records      int   selectedRolesArr   GetSelectedRoles        if  selectedRolesArr    null  amp  amp  selectedRolesArr Length  gt  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     amp  amp                              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   gt   from c in context CompanyRolesToUsers                         where selectedRolesArr Contains c CompanyRoleId                          select c UserId  Contains u Id                        else                        predicateOr   predicateOr Or  u   gt   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

User · Answer

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  amp  amp        u LastName Contains  fra    amp  amp        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    amp  amp        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

User · Answer

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     amp  amp  subQuery Contains u Id  select u

User · Answer

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     amp  amp  subQuery Contains u Id  select u

User · Answer

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     amp  amp  subQuery Contains u Id  select u

[c#] How to do a subquery in LINQ?

Examples related to c#

Examples related to linq

Examples related to linq-to-sql