Can't translate LINQ In clause over EF

Question

I need to translate this simple SQL query into LINQ using EF entities:

SELECT * FROM Clientes WHERE ID_Cliente IN(SELECT ID_Objeto FROM Direcciones where ID_TipoDireccion=IDTipoDireccion)

Seems very simple, but it seems to be a very hard to achieve. I try this:

public List<Clientes> EnumEntity(int IDTipoDireccion)
{
    var dir = new DireccionesRepository(ref rep.Context);
    var misClientes = rep.ListEntity();

    var misDirColection = dir.ListEntity().ToList().Where(o => o.ID_TipoDireccion == IDTipoDireccion);

    foreach (var item in misDirColection)
    {        
        misClientes=misClientes.Where(p => p.ID_Cliente == item.ID_Objeto);
    }
    return misClientes.ToList();
}

The problem with above query is that use AND. I need it to use OR to include all clients that matches Direcciones object. I try PredicateBuilder class but it doesn't support EF. I found this adaptation of PredicateBuilder that seems to resolve this problem:

internal class SubstExpressionVisitor : System.Linq.Expressions.ExpressionVisitor
{
    public Dictionary<Expression, Expression> subst = new Dictionary<Expression, Expression>();

    protected override Expression VisitParameter(ParameterExpression node)
    {
        Expression newValue;
        if (subst.TryGetValue(node, out newValue))
        {
            return newValue;
        }
        return node;
    }
}

public static class PredicateBuilder
{
    /*public static Expression<Func<T,bool>> True<T>() { return f => true; }*/
    //public static Expression<Func<T, bool>> False<T>() { return f => false; }       

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> a, Expression<Func<T, bool>> b)
    {
        ParameterExpression p = a.Parameters[0];

        SubstExpressionVisitor visitor = new SubstExpressionVisitor();
        visitor.subst[b.Parameters[0]] = p;
        Expression body = Expression.AndAlso(a.Body, visitor.Visit(b.Body));
        return Expression.Lambda<Func<T, bool>>(body, p);            
    }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> a, Expression<Func<T, bool>> b)
    {
        ParameterExpression p = a.Parameters[0];

        SubstExpressionVisitor visitor = new SubstExpressionVisitor();
        visitor.subst[b.Parameters[0]] = p;
        Expression body = Expression.OrElse(a.Body, visitor.Visit(b.Body));
        return Expression.Lambda<Func<T, bool>>(body, p);
    }

}

But the problem is I don't know how to use it in my case. Can anybody help me?

Thanks!

EDIT

After adapting the suggested code, this is the result in case someone else need it:

public List<EnumeradorWCFModel> EnumEntity(int IDTipoDireccion)
    {
        // SELECT * FROM Clientes WHERE ID_Cliente IN(SELECT ID_Objeto FROM Direcciones where ID_TipoDireccion=IDTipoDireccion)
        // Get All directions of type IDTipoDireccion
        var dir = new DireccionesRepository(ref rep.Context);

        var misDirColection = dir.ListEntity().Where(x => x.ID_TipoDireccion == IDTipoDireccion)
                .Select(x => x.ID_Objeto);  // Do not iterate!

        // Itinerate Clients
        var misClientes = rep.ListEntity().Where(x => misDirColection.Contains(x.ID_Cliente)).ToList();                

        return misClientes.ToList();
}

Possible duplicate of [Linq IN Operator](http://stackoverflow.com/questions/8043151/linq-in-operator) — Robert McKee, Dec 01 '15 at 15:50

score 2 · Accepted Answer · answered Dec 01 '15 at 14:54

Use Contains().

SELECT * FROM Clientes WHERE ID_Cliente IN(SELECT ID_Objeto FROM Direcciones where ID_TipoDireccion=IDTipoDireccion)

Can be directly translated to:

int IDTipoDireccion = ...

// First, create a query for your subselect.
var direcciones = dbContext.Direcciones
    .Where(x => x.ID_TipoDireccion == IDTipoDireccion);
    .Select(x => x.ID_Objeto);  // Do not iterate!

// Then, use the first query in the WHERE of your second query.
var results = dbContext.Clientes
    .Where(x => direcciones.Contains(x.ID_Cliente))
    .ToList();

Ufff. I take my hat off. I adapt your code and it works just perfect. You are a genious! I post the result Code. Thanks! — ericpap, Dec 01 '15 at 15:01

Can't translate LINQ In clause over EF

1 Answers1