c# - studio - ¿Cómo hacer una subconsulta en LINQ?

linq to sql vs entity framework

Question

Aquí hay un ejemplo de la consulta que intento convertir a LINQ:

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

Existe una relación FK entre CompanyRolesToUsers y los Users , pero es una relación de muchos a muchos y CompanyRolesToUsers es la mesa de conexiones.

Ya tenemos la mayor parte de nuestro sitio creado, y ya tenemos la mayor parte del filtrado trabajando creando expresiones usando una clase PredicateExtensions.

El código para los filtros directos se ve más o menos así:

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

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

Estoy tratando de agregar un predicado para una subselección en otra tabla. ( CompanyRolesToUsers )

Lo que me gustaría poder agregar es algo que hace esto:

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

¿Hay alguna manera de hacer esto? Es frustrante porque puedo escribir el procedimiento almacenado fácilmente, pero soy nuevo en esta cosa de LINQ y tengo una fecha límite. No he podido encontrar un ejemplo que coincida, pero estoy seguro de que está ahí en alguna parte.

Answer 1

¡Aquí hay una subconsulta para ti!

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

En cuanto a esta parte de la pregunta:

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

Recomiendo extraer la cadena del cuadro de texto antes de crear la consulta.

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

Desea mantener un buen control sobre lo que se envía a la base de datos. En el código original, una posible lectura es que una cadena no recortada se envía a la base de datos para recortar, lo cual no es un buen trabajo para la base de datos.

Answer 2

Aquí hay una versión del SQL que devuelve los registros correctos:

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)

Además, tenga en cuenta que (2,3,4) es una lista seleccionada de una lista de casilla de verificación por el usuario de la aplicación web, y olvidé mencionar que acabo de codificar eso por simplicidad. Realmente es una matriz de valores CompanyRoleId, por lo que podría ser (1) o (2,5) o (1,2,3,4,6,7,99).

Además, la otra cosa que debería especificar más claramente es que PredicateExtensions se usa para agregar dinámicamente cláusulas predicativas a Where para la consulta, dependiendo de qué campos de formulario haya rellenado el usuario de la aplicación web. Así que la parte más difícil para mí es cómo para transformar la consulta de trabajo en una expresión LINQ que puedo adjuntar a la lista dinámica de expresiones.

Daré algunas de las consultas de muestra de LINQ y veré si puedo integrarlas con nuestro código, y luego publicar mis resultados. ¡Gracias!

marcel

Answer 3

Así es como he estado haciendo subconsultas en LINQ, creo que esto debería conseguir lo que quieres. Puede reemplazar el CompanyRoleId explícito == 2 ... con otra subconsulta para los diferentes roles que desee o unirse también.

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

Bien, aquí hay una consulta de combinación básica que obtiene los registros correctos:

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

Pero aquí está el código que se integra más fácilmente con el algoritmo que ya teníamos en su lugar:

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

que es gracias a un póster en el foro LINQtoSQL

Answer 5

No se necesita subconsulta con esta declaración, que está mejor escrita como

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)

o

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)

Dicho esto, en LINQ sería

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

o

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

Que de nuevo, son formas respetables de representar esto. Prefiero la sintaxis explícita de "unión" en ambos casos, pero ahí está ...

Answer 6

Podrías hacer algo como esto para tu caso - (la sintaxis puede estar un poco desconectada). También mira este 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;