C# 如何在 LINQ 中执行子查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/418609/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do a subquery in LINQ?
提问by marcel_g
Here's an example of the query I'm trying to convert to LINQ:
这是我尝试转换为 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 CompanyRolesToUsersand Users, but it's a many to many relationship and CompanyRolesToUsersis the junction table.
CompanyRolesToUsers和之间存在 FK 关系Users，但它是多对多关系，并且CompanyRolesToUsers是连接表。
We already have most of our site built, and we already have most of the filtering working by building Expressions using a PredicateExtensions class.
我们已经构建了大部分站点，并且通过使用 PredicateExtensions 类构建表达式，我们已经完成了大部分过滤工作。
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)
我正在尝试为另一个表中的子选择添加谓词。( 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.
有没有办法做到这一点？这很令人沮丧，因为我可以轻松编写存储过程，但我对 LINQ 还不熟悉，而且我有一个截止日期。我一直无法找到匹配的示例，但我确定它在某个地方。
采纳答案by Amy B
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.
您希望对发送到数据库的内容保持良好的控制。在原始代码中，一种可能的解读是未修剪的字符串被发送到数据库中进行修剪——这对数据库来说并不是一件好事。
回答by Noah
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.
这就是我在 LINQ 中做子查询的方式，我认为这应该得到你想要的。您可以将显式 CompanyRoleId == 2... 替换为您想要的不同角色的另一个子查询，也可以加入它。
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;
回答by TheSoftwareJedi
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
话虽如此，在 LINQ 中它会是
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...
同样，这都是代表这一点的受人尊敬的方式。我自己在这两种情况下都更喜欢显式的“加入”语法，但它是......
回答by Perpetualcoder
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;
回答by marcel_g
Here's a version of the SQL that returns the correct records:
这是返回正确记录的 SQL 版本：
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).
另外，请注意 (2,3,4) 是 Web 应用程序用户从复选框列表中选择的列表，我忘了提到我只是为了简单起见对其进行了硬编码。实际上它是一个 CompanyRoleId 值的数组，因此它可以是 (1) 或 (2,5) 或 (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.
另外我应该更清楚地指定的另一件事是，PredicateExtensions 用于将谓词子句动态添加到查询的 Where 中，具体取决于 Web 应用程序用户填写的表单字段。所以对我来说棘手的部分是如何将工作查询转换为可以附加到动态表达式列表的 LINQ 表达式。
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!
我将尝试一些示例 LINQ 查询，看看是否可以将它们与我们的代码集成，然后发布我的结果。谢谢！
marcel
马塞尔
回答by marcel_g
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
这要归功于LINQtoSQL 论坛上的一张海报
