c# - not - LINQ a Entidades-donde... en cláusula con múltiples columnas

not in linq c#

Question

Estoy intentando consultar los datos del formulario con LINQ-to-EF:

class Location {
    string Country;
    string City;
    string Address;
    …
}

buscando una ubicación por la tupla (País, Ciudad, Dirección). Lo intenté

var keys = new[] {
    new {Country=…, City=…, Address=…},
    …
}

var result = from loc in Location
             where keys.Contains(new {
                 Country=loc.Country, 
                 City=loc.City, 
                 Address=loc.Address
             }

pero LINQ no quiere aceptar un tipo anónimo (lo que entiendo es la forma de expresar las tuplas en LINQ) como parámetro para Contains ().

¿Existe una forma "agradable" de expresar esto en LINQ, mientras se puede ejecutar la consulta en la base de datos? Alternativamente, si solo itero sobre las teclas y Unión () - corrí las consultas, ¿sería malo para el rendimiento?

Answer 1

¿Has probado simplemente usando la clase Tuple?

var keys = new[] {
    Tuple.Create("Country", "City", "Address"),
    …
}

var result = from loc in Location
             where keys.Contains(Tuple.Create(loc.Country, loc.City, loc.Address))

Answer 2

Aunque no pude hacer funcionar el código de @ YvesDarmaillac, me indicó esta solución.

Puedes construir una expresión y luego agregar cada condición por separado. Para hacer esto, puedes usar el PredicateBuilder Universal (fuente al final).

Aquí está mi código:

// First we create an Expression. Since we can''t create an empty one,
// we make it return false, since we''ll connect the subsequent ones with "Or".
// The following could also be: Expression<Func<Location, bool>> condition = (x => false); 
// but this is clearer.
var condition = PredicateBuilder.Create<Location>(x => false);

foreach (var key in keys)
{
    // each one returns a new Expression
    condition = condition.Or(
        x => x.Country == key.Country && x.City == key.City && x.Address == key.Address
    );
}

using (var ctx = new MyContext())
{
    var locations = ctx.Locations.Where(condition);
}

Sin embargo, hay que tener en cuenta que la lista de filtros (la variable de keys en este ejemplo) no puede ser demasiado grande, o puede alcanzar el límite de parámetros, con una excepción como esta:

SqlException: la solicitud entrante tiene demasiados parámetros. El servidor soporta un máximo de 2100 parámetros. Reduzca el número de parámetros y vuelva a enviar la solicitud.

Entonces, en este ejemplo (con tres parámetros por línea), no puede tener más de 700 ubicaciones para filtrar.

Usando dos elementos para filtrar, generará 6 parámetros en el SQL final. El SQL generado se verá a continuación (formateado para ser más claro):

exec sp_executesql N''
SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Country] AS [Country], 
    [Extent1].[City] AS [City], 
    [Extent1].[Address] AS [Address]
FROM [dbo].[Locations] AS [Extent1]
WHERE 
    (
        (
            ([Extent1].[Country] = @p__linq__0) 
            OR 
            (([Extent1].[Country] IS NULL) AND (@p__linq__0 IS NULL))
        )
        AND 
        (
            ([Extent1].[City] = @p__linq__1) 
            OR 
            (([Extent1].[City] IS NULL) AND (@p__linq__1 IS NULL))
        ) 
        AND 
        (
            ([Extent1].[Address] = @p__linq__2) 
            OR 
            (([Extent1].[Address] IS NULL) AND (@p__linq__2 IS NULL))
        )
    )
    OR
    (
        (
            ([Extent1].[Country] = @p__linq__3) 
            OR 
            (([Extent1].[Country] IS NULL) AND (@p__linq__3 IS NULL))
        )
        AND 
        (
            ([Extent1].[City] = @p__linq__4) 
            OR 
            (([Extent1].[City] IS NULL) AND (@p__linq__4 IS NULL))
        ) 
        AND 
        (
            ([Extent1].[Address] = @p__linq__5) 
            OR 
            (([Extent1].[Address] IS NULL) AND (@p__linq__5 IS NULL))
        )
    )
'',
N''
    @p__linq__0 nvarchar(4000),
    @p__linq__1 nvarchar(4000),
    @p__linq__2 nvarchar(4000),
    @p__linq__3 nvarchar(4000),
    @p__linq__4 nvarchar(4000),
    @p__linq__5 nvarchar(4000)
'',
@p__linq__0=N''USA'',
@p__linq__1=N''NY'',
@p__linq__2=N''Add1'',
@p__linq__3=N''UK'',
@p__linq__4=N''London'',
@p__linq__5=N''Add2''

Observe cómo la expresión "falsa" inicial se ignora correctamente y no se incluye en el SQL final por EntityFramework.

Finalmente, aquí está el código de Universal PredicateBuilder , para el registro.

/// <summary>
/// Enables the efficient, dynamic composition of query predicates.
/// </summary>
public static class PredicateBuilder
{
    /// <summary>
    /// Creates a predicate that evaluates to true.
    /// </summary>
    public static Expression<Func<T, bool>> True<T>() { return param => true; }

    /// <summary>
    /// Creates a predicate that evaluates to false.
    /// </summary>
    public static Expression<Func<T, bool>> False<T>() { return param => false; }

    /// <summary>
    /// Creates a predicate expression from the specified lambda expression.
    /// </summary>
    public static Expression<Func<T, bool>> Create<T>(Expression<Func<T, bool>> predicate) { return predicate; }

    /// <summary>
    /// Combines the first predicate with the second using the logical "and".
    /// </summary>
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.AndAlso);
    }

    /// <summary>
    /// Combines the first predicate with the second using the logical "or".
    /// </summary>
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.OrElse);
    }

    /// <summary>
    /// Negates the predicate.
    /// </summary>
    public static Expression<Func<T, bool>> Not<T>(this Expression<Func<T, bool>> expression)
    {
        var negated = Expression.Not(expression.Body);
        return Expression.Lambda<Func<T, bool>>(negated, expression.Parameters);
    }

    /// <summary>
    /// Combines the first expression with the second using the specified merge function.
    /// </summary>
    static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
    {
        // zip parameters (map from parameters of second to parameters of first)
        var map = first.Parameters
            .Select((f, i) => new { f, s = second.Parameters[i] })
            .ToDictionary(p => p.s, p => p.f);

        // replace parameters in the second lambda expression with the parameters in the first
        var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

        // create a merged lambda expression with parameters from the first expression
        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
    }

    class ParameterRebinder : ExpressionVisitor
    {
        readonly Dictionary<ParameterExpression, ParameterExpression> map;

        ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            ParameterExpression replacement;

            if (map.TryGetValue(p, out replacement))
            {
                p = replacement;
            }

            return base.VisitParameter(p);
        }
    }
}

Answer 3

Creo que la forma correcta de hacerlo es

var result = from loc in Location
             where loc.Country = _country
             where loc.City = _city
             where loc.Address = _address
             select loc

Parece no optimizado, pero el proveedor de consultas saldrá y realizará la optimización cuando transforme la consulta a SQL. Cuando se usan tuplas u otras clases, el proveedor de consultas no sabe cómo transformarlas en sql y eso es lo que causa la excepción NotSupportedException

-editar-

Si tienes varias tuplas clave, creo que debes recorrerlas todas y hacer la consulta anterior para cada una. de nuevo, eso puede parecer poco optimizado, pero la consulta para recuperar todas las ubicaciones en una sola consulta probablemente terminará siendo bastante larga:

select * from locations 
where (locations.Country = @country1 and locations.City = @city1, locations.Adress = @adress1)
or (locations.Country = @country2 and locations.City = @city2, locations.Adress = @adress2)
or ...

La forma más rápida de hacerlo es probablemente hacer las consultas simples, pero enviarlas como un solo script de SQL y utilizar múltiples conjuntos de resultados para obtener cada valor. No estoy seguro de que puedas conseguir que EF haga eso.

Answer 4

Existe una extensión EF que fue diseñada para un caso muy similar. Es EntityFrameworkCore.MemoryJoin (el nombre puede ser confuso, pero es compatible con EF6 y EF Core). Como se indica en el article del autor, modifica la consulta SQL pasada al servidor e inyecta la construcción de VALUES con datos de su lista local. Y la consulta se ejecuta en el servidor DB.

Así que para su caso, el uso podría ser así

var keys = new[] {
  new {Country=…, City=…, Address=…},
  …
}

// here is the important part!
var keysQueryable = context.FromLocalList(keys);

var result = from loc in Location
    join key in keysQueryable on new { loc.Country, loc.City, loc.Address } equals new { key.Country, key.City, key.Address }
    select loc

Answer 5

Mi solución es crear un nuevo método de extensión WhereOr que use un ExpressionVisitor para generar la consulta:

public delegate Expression<Func<TSource, bool>> Predicat<TCle, TSource>(TCle cle);

public static class Extensions
{
    public static IQueryable<TSource> WhereOr<TSource, TCle>(this IQueryable<TSource> source, IEnumerable<TCle> cles, Predicat<TCle, TSource> predicat)
        where TCle : ICle,new()
    {
        Expression<Func<TSource, bool>> clause = null;

        foreach (var p in cles)
        {
            clause = BatisseurFiltre.Or<TSource>(clause, predicat(p));
        }

        return source.Where(clause);
    }
}

class BatisseurFiltre : ExpressionVisitor
{
    private ParameterExpression _Parametre;
    private BatisseurFiltre(ParameterExpression cle)
    {
        _Parametre = cle;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return _Parametre;
    }

    internal static Expression<Func<T, bool>> Or<T>(Expression<Func<T, bool>> e1, Expression<Func<T, bool>> e2)
    {
        Expression<Func<T, bool>> expression = null;

        if (e1 == null)
        {
            expression = e2;
        }
        else if (e2 == null)
        {
            expression = e1;
        }
        else
        {
            var visiteur = new BatisseurFiltre(e1.Parameters[0]);
            e2 = (Expression<Func<T, bool>>)visiteur.Visit(e2);

            var body = Expression.Or(e1.Body, e2.Body);
            expression = Expression.Lambda<Func<T, bool>>(body, e1.Parameters[0]);
        }

        return expression;
    }
}

Lo siguiente genera código sql limpio ejecutado en la base de datos:

var result = locations.WhereOr(keys, k => (l => k.Country == l.Country && 
                                                k.City == l.City && 
                                                k.Address == l.Address
                                          )
                          );

Answer 6

No creo que eso funcione para usted, ya que cuando esté actualizando un objeto en el método Contains , creará un nuevo objeto cada vez. Dado que esos objetos son anónimos, la forma en que se compararán será contra su referencia, que será diferente para cada objeto.

También, mira la respuesta de Jacek.

Answer 7

Qué tal si:

var result = locations.Where(l => keys.Any(k => 
                    k.Country == l.Country && 
                    k.City == l.City && 
                    k.Address == l.Address));

ACTUALIZAR

Desafortunadamente, EF lanza NotSupportedException en eso, lo que descalifica esta respuesta si necesita que la consulta se ejecute en el lado de la base de datos.

ACTUALIZACIÓN 2

Probé todo tipo de combinaciones usando clases personalizadas y tuplas, ninguna de las dos funciona. ¿De qué volúmenes de datos estamos hablando? Si no es nada demasiado grande, puede procesarlo del lado del cliente (conveniente) o usar uniones (si no es más rápido, al menos se transmiten menos datos).

Answer 8

Reemplazaría Contains (que es un método específico para listas y matrices) con el método de extensión Any de IEnumerable más amplio:

var result = Location
    .Where(l => keys.Any(k => l.Country == k.Country && l.City = k.City && l.Address == k.Address);

Esto también se puede escribir:

var result = from l in Location
             join k in keys
             on l.Country == k.Country && l.City == k.City && l.Address == k.Address
             select l;

Answer 9

Si no va a necesitar muchas combinaciones de teclas, simplemente puede agregar una propiedad LocationKey a sus datos. Para evitar perder una gran cantidad de almacenamiento, tal vez sea el código hash de las propiedades combinadas.

Luego la consulta simplemente tendrá una condición en LocationKey . Finalmente, en el lado del cliente, filtre los resultados para eliminar entidades que tenían el mismo hash pero no la misma ubicación.

Se vería algo así como:

class Location 
{
    private string country;
    public string Country
    {
        get { return country; }
        set { country = value; UpdateLocationKey(); }
    }

    private string city;
    public string City
    {
        get { return city; }
        set { city = value; UpdateLocationKey(); }
    }

    private string address;
    public string Address
    {
        get { return address; }
        set { address = value; UpdateLocationKey(); }
    }

    private void UpdateLocationKey()
    {
        LocationKey = Country.GetHashCode() ^ City.GetHashCode() ^ Address.GetHashCode();
    }

    int LocationKey;
    …
}

Luego simplemente consulta en la propiedad LocationKey.

No es ideal, pero debería funcionar.

Answer 10

var result = from loc in Location
             where keys.Contains(new {
                 Country=l.Country, 
                 City=l.City, 
                 Address=l.Address
             }

tendría que ser:

var result = from loc in Location
             where keys.Contains(new {
                 Country=loc.Country, 
                 City=loc.City, 
                 Address=loc.Address
             }
             select loc;

Answer 11

    var keys = new[] {
        new {Country=…, City=…, Address=…},
        …
    }    
    var result = from loc in Location
                 where keys.Any(k=>k.Country == loc.Country 
&& k.City == loc.City 
&& k.Address == loc.Address) 
select loc

Prueba esto.