如何在LINQ中进行子查询? [英] How to do a subquery in LINQ?

问题描述

这是我要转换为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) )

CompanyRolesToUsers和Users之间存在FK关系，但它是多对多关系，并且CompanyRolesToUsers是联结表.

There is a FK relationship between CompanyRolesToUsers and Users, but it's a many to many relationship and CompanyRolesToUsers is the junction table.

我们已经建立了大部分站点，并且已经通过使用PredicateExtensions类构建表达式来完成大部分过滤工作.

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

我正在尝试为另一个表中的子选择添加谓词. (CompanyRolesToUsers)

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

有没有办法做到这一点?这很令人沮丧，因为我可以轻松地编写存储过程，但是我对LINQ并不陌生，而且我有一个截止日期.我还找不到匹配的示例，但是我确定它在某个地方.

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.

推荐答案

这是您的子查询！

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.

这篇关于如何在LINQ中进行子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！