如何做子查询在LINQ [英] how to do 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 和用户之间的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类建筑防爆pressions工作。

We already have most of our site built, and we already have most of the filtering working by building Expressions using a PredicateExtensions class.

在code为简单的过滤器看起来是这样的：

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

我想加predicate的另一个表中的子查询中。 （ 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.

推荐答案

下面是一个子查询为您服务！

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

---

---

就问题的这一部分：

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

您想保持什么获取发送到数据库良好的控制。在原来的code，一种可能的解读是，一个字符串修剪被发送到数据库中进行修整 - 这是不是数据库做善事

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屋！