EF Core SQL Filter Translation

Estoy usando EF Core 5.0.1 con ASP NET 5.0.1 API web y quiero construir una consulta con PredicateBuilder utilizando LinqKit.Microsoft.EntityFrameworkCore 5.0.2.1

A los efectos de la pregunta simplificaré mi modelo para:

public class User
{
    public long IdUser { get; set; }
    public string Name { get; set; }
    public virtual ICollection UserDepartments { get; set; }
}

public class Department
{
    public long IdDepartament { get; set; }
    public string Name { get; set; }
    public virtual ICollection UsersDepartment { get; set; }
}

public UserDepartment
{
    public long IdUser { get; set; }
    public long IdDepartment { get; set; }
    public virtual Department { get; set; }
    public virtual User { get; set; }
}

Uno User puede tener muchos Departaments y uno Departament puede tener muchos Users

Tres modelos tienen su tabla corresponsal en SQL Server y IEntityTypeConfiguration clase con las relaciones apropiadas establecidas.

Todo lo que quiero lograr es buscar cualquier User que pertenece a cualquier Departament que Department.Name está en List.

El List contiene una lista de palabras clave, no el nombre exacto del Departamento

Department mesa tiene este tipo de filas:

IdDepartmentNombre
1Administración
2HHRR
3Ventas
4Marketing

Y el List puede ser cualquier palabra clave como "Admin", "Sal", "Mark" y así sucesivamente.

Primer intento

... era construir un predicado así:

List kwDepartments = new List {"mark","admin"};
var predicate = PredicateBuilder.New(true);

predicate = predicate.And(x => x.UserDepartments.Where(y => kwDepartments.Any(c => y.Department.Name.Equals(c))).Any());

Esto produce un SQL con el operador IN, así:

...[t].[Name] IN (N'mark', N'admin'))

Obviamente esto no es lo que quiero, pero si uso .Contains en lugar de .Equals, una excepción se lanza

La expresión LINQ no podría traducirse

Creo que esto es porque estoy tratando de evaluar un valor no primario.

Segundo intento

... era para iterar sobre kwDepartments y añadir un .Or para cada cuerda, así:

foreach (string dep in kwDepartments )
{
    predicateDep = predicateDep.Or(x => x.UserDepartments.Where(y=> y.Department.Name.Contains(dep)).Any());
}

predicate = predicate.And(predicateDep);

Esto devuelve el partido que espero, pero también dos problemas.

  1. La traducción SQL es un mal desempeño como EF Core anidates a INNER JOIN para cada palabra clave. No importa si kwDepartment tiene dos o tres elementos, pero con 100 o 1000 elementos serán inadmisibles.

  2. Cada uno INNER JOIN on Departments mesa SELECT con todos los campos de mesa y no necesitaba ninguno. He intentado poner un .Select(x=> x.Name) declaración en el predicado para tomar sólo dos campos pero no hace efecto.

Tercer intento

... estaba usando la búsqueda completa de texto usando EF.Functions.FreeText pero parece que no hay diferencia.

Mi objetivo es construir un predicado que se traduzca en algo similar a:

SELECT [c.IdUser]. [c.Name]
FROM [User] AS [c]
WHERE EXISTS (
    SELECT 1
    FROM [UserDepartment] AS [u]
    INNER JOIN (
        SELECT [c0].[IdDepartment], [c0].[Name] <--ONLY NEED TWO FIELDS INSTEAD OF ALL FIELDS
        FROM [Department] AS [c0]
               ) AS [t] ON [u].[IdDepartment] = [t].[IdDepartment]
    WHERE ([c].[IdUser] = [u].[IdUser]) AND ([t].[Name] like (N'admin%') or [t].[Name] like  (N'mark%')))

No es obligatorio utilizar el LIKE operador, pero lo puse para mejor comprensión.

¡Gracias de nuevo!

Pregunta hecha hace 3 años, 4 meses, 28 días - Por coderconqueror

3 Respuestas:

  • Estás en el camino correcto con Or predicado, pero en lugar de múltiples Or predicados en user.UserDepatments.Any(single_match) debe crear un solo Or basado predicado para ser utilizado dentro del único user.UserDepatments.Any(multi_or_match).

    Algo así:

    var departtmentPredicate = kwDepartments
    .Select(kw => Linq.Expr((Department d) => EF.Functions.Like(d.Name, "%" + kw + "%")))
    .Aggregate(PredicateBuilder.Or);

    y luego

    predicate = predicate.And(u => u.UserDepartments
    .Select(ud => ud.Department) // navigate to department
    .AsQueryable() // to be able to use departtmentPredicate expression directly
    .Any(departtmentPredicate));

    Con ese código y la lista de muestras, DbSet().Where(predicate) es traducido a algo así:

    DECLARE @__p_1 nvarchar(4000) = N'%mark%';
DECLARE @__p_2 nvarchar(4000) = N'%admin%';

SELECT [u].[IdUser], [u].[Name]
FROM [User] AS [u]
WHERE EXISTS (
    SELECT 1
    FROM [UserDepartment] AS [u0]
    INNER JOIN [Department] AS [d] ON [u0].[IdDepartment] = [d].[IdDepartament]
    WHERE ([u].[IdUser] = [u0].[IdUser]) AND (([d].[Name] LIKE @__p_1) OR ([d].[Name] LIKE @__p_2)))

    Respondida el Dec 19, 2020 a las 12:38 - por stackscripter

    Votos positivos: 0 | Votos negativos: 0

  • El problema que estás experimentando con la traducción del filtro SQL se debe a que estás tratando de utilizar operaciones que no se pueden traducir directamente a SQL en EF Core. Para lograr lo que deseas, puedes intentar lo siguiente:

    • Utiliza la función `Where` de `LinqKit` para construir dinámicamente tu predicado.
    • Realiza la consulta a través de las entidades relacionadas para evitar la necesidad de joins innecesarios.

    Aquí te dejo un ejemplo de cómo podrías construir tu consulta:

    csharp
List kwDepartments = new List {"mark","admin"};

var predicate = PredicateBuilder.New(true);

foreach (var dep in kwDepartments)
{
    string tempDep = dep; // Variable temporal para evitar alterar la referencia en el closure
    predicate = predicate.Or(u => u.UserDepartments
                                  .Any(ud => ud.Department.Name.Contains(tempDep)));
}

var users = context.Users
                   .AsExpandable() // Permite la expansión de predicados
                   .Where(predicate)
                   .Select(u => new { u.IdUser, u.Name }) // Solo seleccionar los campos requeridos
                   .ToList();

    Este código construye dinámicamente un predicado que busca usuarios cuyos departamentos contienen alguna de las palabras clave proporcionadas en kwDepartments. Luego, se seleccionan solo los campos necesarios de los usuarios en la consulta final.

    Espero que esta solución te ayude a resolver tu problema de traducción de filtro SQL en EF Core. ¡Déjame saber si tienes alguna otra pregunta!

    Respondida el Dec 20, 2020 a las 12:37 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Solución usando PredicateBuilder y método Contains

    Para utilizar el método Contains con PredicateBuilder para valores no primarios, puedes crear un predicado personalizado utilizando la clase CustomPredicateBuilder, como se muestra a continuación:

    
public static class CustomPredicateBuilder
{
    public static Expression> Contains(this Expression> property, IEnumerable values)
    {
        var parameter = Expression.Parameter(typeof(T));
        var propertyAccess = Expression.MakeMemberAccess(parameter, property);
        var anyMethod = typeof(Enumerable).GetMethod("Any").MakeGenericMethod(typeof(TProperty));
        var callAny = Expression.Call(anyMethod, propertyAccess, Expression.Constant(values));
        return Expression.Lambda>(callAny, parameter);
    }
}

    Luego, puedes utilizar este predicado personalizado para construir tu consulta:

    
List kwDepartments = new List { "mark", "admin" };
var predicate = PredicateBuilder.New(true);

predicate = predicate.And(x => x.UserDepartments.Any(y => kwDepartments.Contains(y.Department.Name)));

    Solución usando EF.Functions.FreeText

    También puedes utilizar EF.Functions.FreeText para realizar una búsqueda de texto completo, como se muestra a continuación:

    
List kwDepartments = new List { "mark", "admin" };

var predicate = PredicateBuilder.New(true);

foreach (var kw in kwDepartments)
{
    predicate = predicate.Or(x => EF.Functions.FreeText(x.UserDepartments.Select(y => y.Department.Name), kw));
}

    Consejos de optimización

    Para mejorar el rendimiento de tu consulta, puedes utilizar la proyección para seleccionar solo las columnas necesarias. Considera el uso de la indexación en la columna Department.Name para mejorar la eficiencia de la búsqueda. * Si la lista de palabras clave es grande, puedes dividirla en lotes y ejecutar consultas por separado para cada lote.

    Respondida el Dec 20, 2020 a las 12:47 - por Gemini

    Votos positivos: 0 | Votos negativos: 0