comment faire sous-requête dans LINQ

Question

Voici un exemple de la requête, je suis en train de convertir LINQ:

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

Il y a un FK relation entre CompanyRolesToUsers et Users, mais c'est un de nombreux de nombreux de la relation et de l' CompanyRolesToUsers est la table de jonction.

Nous avons déjà plus de notre site construit, et nous avons déjà plus de filtrage de travail par la construction d'Expressions à l'aide d'un PredicateExtensions classe.

Le code pour la simple filtres ressemble à quelque chose comme ceci:

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

e.Result = context.Users.Where(predicateAnd);

J'essaye d'ajouter un prédicat pour une sous-sélection d'une autre table. (CompanyRolesToUsers)

Ce que j'aimerais être en mesure d'ajouter, c'est quelque chose qui fait ceci:

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

Est-il possible de faire cela? C'est frustrant parce que je peux écrire la procédure stockée facilement, mais je suis nouveau sur ce LINQ chose et j'ai une date limite. Je n'ai pas été en mesure de trouver un exemple qui correspond à la hausse, mais je suis sûr qu'elle est là quelque part.

TIA,
Marcel

Answer 1

Voici une sous-requête pour vous!

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

---

Concernant cette partie de la question:

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

Je vous recommande fortement de l'extraction de la chaîne à partir de la zone de texte avant la création de la requête.

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

Vous souhaitez maintenir un bon contrôle sur ce qui est envoyé à la base de données. Dans le code d'origine, il est possible de lire n'est qu'un inculte chaîne est envoyé dans la base de données pour la coupe d' - ce qui n'est pas du bon travail pour la base de données à faire.

Answer 2

Il n’ya pas de sous-requête nécessaire avec cette déclaration, qui est mieux écrite comme suit:

 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)
 

ou

 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)
 

Cela étant dit, dans LINQ ce serait

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

ou

 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
 

Encore une fois, ce sont deux manières respectables de représenter cela. Je préfère la syntaxe explicite "join" dans les deux cas moi-même, mais la voici ...

Answer 3

C’est comme ça que j’ai fait les sous-requêtes dans LINQ, je pense que cela devrait donner ce que vous voulez. Vous pouvez remplacer l'explicite CompanyRoleId == 2 ... par une autre sous-requête pour les différents rôles souhaités ou bien la rejoindre également.

 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;
 

Answer 4

Ok, voici une requête de jointure de base qui obtient les enregistrements corrects:

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

Mais voici le code qui s'intègre le plus facilement à l'algorithme que nous avions déjà en 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) ); 
        }
 

grâce à une affiche sur le forum LINQtoSQL

Answer 5

Vous pouvez faire quelque chose comme ceci pour votre cas - (la syntaxe peut être un peu fausse). Regardez aussi ce lien

 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;