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.
This question is related to
c#
linq
linq-to-sql
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;
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;
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;
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
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...
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
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...
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;
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
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
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;
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
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...
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
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;
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
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...
Source: Stackoverflow.com