LINQ에서 하위 쿼리를 수행하는 방법은 무엇입니까?

LINQ에서 하위 쿼리를 수행하는 방법은 무엇입니까?

---

다음은 LINQ로 변환하려는 쿼리의 예입니다.

SELECT *
FROM Users
WHERE Users.lastname LIKE '%fra%'
    AND Users.Id IN (
         SELECT UserId 
         FROM CompanyRolesToUsers 
         WHERE CompanyRoleId in (2,3,4) )

이 사이에 FK의 관계 CompanyRolesToUsers와는 Users하지만 많은 관계로 많은 그리고 CompanyRolesToUsers접합 테이블입니다.

우리는 이미 대부분의 사이트를 구축했으며 이미 PredicateExtensions 클래스를 사용하여 Expressions를 구축하여 대부분의 필터링 작업을 수행했습니다.

간단한 필터에 대한 코드는 다음과 같습니다.

 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 )
{
    //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에 익숙하지 않아 마감일이 있습니다. 일치하는 예를 찾지 못했지만 어딘가에있을 것입니다.

---

여기에 당신을위한 하위 쿼리가 있습니다!

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

쿼리를 작성하기 전에 텍스트 상자에서 문자열을 추출하는 것이 좋습니다.

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

데이터베이스로 전송되는 내용을 잘 제어하고 싶습니다. 원래 코드에서 한 가지 가능한 판독 값은 트리밍을 위해 트리밍되지 않은 문자열이 데이터베이스로 전송된다는 것입니다. 이는 데이터베이스가 수행하기에 좋은 작업이 아닙니다.

---

이 문에는 하위 쿼리가 필요하지 않습니다.

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)

또는

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)

즉, LINQ에서는

from u in Users
from c in CompanyRolesToUsers 
where u.Id == c.UserId &&
      u.LastName.Contains("fra") &&
      selectedRoles.Contains(c.CompanyRoleId)
select u

또는

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

---

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;

---

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

---

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

참고URL : https://stackoverflow.com/questions/418609/how-to-do-a-subquery-in-linq