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

问题描述

这是我尝试转换为 LINQ 的查询示例:

SELECT *来自用户WHERE Users.lastname LIKE '%fra%'AND Users.Id IN (选择用户 ID从公司角色到用户WHERE CompanyRoleId in (2,3,4) )

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

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

简单过滤器的代码如下所示:

 if (!string.IsNullOrEmpty(TextBoxLastName.Text)){predicateAnd = predicateAnd.And(c => c.LastName.Contains(TextBoxLastName.Text.Trim()));}e.Result = context.Users.Where(predicateAnd);

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

我希望能够添加的是这样的东西:

int[] selectedRoles = GetSelectedRoles();if( selectedRoles.Length > 0 ){//不知何故只从这里选择用户 ID ???:var subquery = from u in CompanyRolesToUsers其中 u.RoleID 在 selectedRoles 中选择 u.UserId;//以某种方式将其转换为表达式 ???:var subExpression = Expression.Invoke(subquery);//并将其添加到现有表达式中 ???:predicateAnd = predicateAnd.And(subExpression);}

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

解决方案

这里有一个子查询给你！

ListIdsToFind = new List() {2, 3, 4};数据库用户.Where(u => SqlMethods.Like(u.LastName, "%fra%")).Where(u =>db.CompanyRolesToUsers.Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId)).Select(crtu => crtu.UserId).Contains(u.Id))

<小时>

关于问题的这一部分:

predicateAnd = predicateAnd.And(c => c.LastName.Contains(TextBoxLastName.Text.Trim()));

我强烈建议在创作查询之前从文本框中提取字符串.

string searchString = TextBoxLastName.Text.Trim();predicateAnd = predicateAnd.And(c => c.LastName.Contains(searchString));

您希望对发送到数据库的内容保持良好的控制.在原始代码中，一种可能的解读是未修剪的字符串被发送到数据库中进行修剪——这对数据库来说并不是一件好事.

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.

解决方案

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.

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