Dynamic query in a List

4

How to apply a filter on a generic List object using Lambda?

I know that with Reflection I can get an array with the names of the properties of my object and through Reflection itself retrieve the property of the object from its name but I do not know how to pass this array and compare the values of the properties within Lambda.

    
asked by anonymous 13.01.2015 / 21:09

3 answers

1

The intent of performing a dynamic query in List was to assemble a form that would display the List in a DataGridView and query all attributes of List objects, and the List inside the form could contain any object. The solution presented by Mateus proved to be viable, but when I presented it to the rest of the team we found some problems among them:

  • When an object has another object as an attribute;
  • You would not be able to customize the query, eg limit the fields the attributes to use.

Once I hit the head with the team for 2 hours I opted for the following approach: An interface called "IListaAuxiliar" with a method called "FilterList" that will be implemented by all classes that use the query form. This one by your will also have an attribute of the same interface. So when calling the form we pass the object (Application_BLL for example) and call the method FilterList without parameters to get the complete list. Then save the search term entered by the user and filter again. Calls to the FilterList method are not cumulative because they act on an unchanged list stored inside the FlexList object. Below is the code for the interface and object.

public class Aplicacao : Objeto_DTL
{
    private int _ID;
    public int ID
    {
        get { return _ID; }
        set { _ID = value; }
    }

    private string _Nome;
    public string Nome
    {
        get { return _Nome; }
        set { _Nome = value; }
    }

    private List<Versao> _versao;
    public List<Versao> Versao
    {
        get { return _versao; }
        set { _versao = value; }
    }    
}

public interface IListaAuxiliar
{
    List<Object> FiltrarLista(string valor);
}

public class Aplicacao_BLL : IListaAuxiliar
{
    private List<Aplicacao> _lista;

    public List<Object> FiltrarLista(string valor)
    {
        var lTemp = _lista.Cast<Object>().Where(
                x => (x as Aplicacao).ID.Value.ToString().Contains(valor)
                    || (x as Aplicacao).Nome.Value.ToString().Contains(valor)
                    || (x as Aplicacao).Descricao.Value.ToString().Contains(valor)
                    || (x as Aplicacao).Criacao.Value.ToString().Contains(valor)
            );

        return lTemp.ToList();
    }
}
    
26.01.2015 / 14:51
2

I made a small adaptation of How to: Use Expression Trees to Build Dynamic Queries (C # and Visual Basic ).

To make it easier to understand, I'll create the Cliente class.

public class Cliente {

    public string Nome { get; set;}
    public int RG { get; set;}
    public int CPF { get; set;}

    public Cliente () : this ("", 0, 0) { }

    public Cliente(string nome, int rg, int cpf) {
        this.Nome = nome;
        this.RG = rg;
        this.CPF = cpf;
    }

    public override string ToString() {
        return Nome + "\t" + RG + "\t" + CPF + "\n";
    }

}

Once you've created your clients, I'll filter back those with Name equal to Mario or have the 100 , done this I will sort them by RG , the expression would look like this:

clientes.Where(cliente => cliente.Nome == "Mario" || cliente.CPF == 100)
        .OrderBy(cliente => cliente.RG)

To create it dynamically, you can do it this way:

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        // cria os clientes
        Cliente cliente1 = new Cliente("Mario", 963, 222);
        Cliente cliente2 = new Cliente("Carlos", 183, 444);
        Cliente cliente3 = new Cliente("Pedro", 722, 666);
        Cliente cliente4 = new Cliente("Mario", 737, 888);
        Cliente cliente5 = new Cliente("Maria", 159, 100);
        Cliente cliente6 = new Cliente("Carla", 194, 240);
        Cliente cliente7 = new Cliente("Mario", 10, 19);

        // adiciona os clientes em uma lista
        List<Cliente> listClientes = new List<Cliente>();
        listClientes.Add(cliente1);
        listClientes.Add(cliente2);
        listClientes.Add(cliente3);
        listClientes.Add(cliente4);
        listClientes.Add(cliente5);
        listClientes.Add(cliente6);
        listClientes.Add(cliente7);

        // recupera o IQueryable da lista de clientes
        IQueryable<Cliente> queryableData = listClientes.AsQueryable<Cliente>();

        // cria o parâmetro a ser utilizado na consulta
        ParameterExpression pe = Expression.Parameter(typeof(Cliente), "cliente");

        // expressão 'cliente.Nome == "Mario"'
        Expression column = Expression.Property(pe, typeof(Cliente).GetProperty("Nome"));
        Expression value = Expression.Constant("Mario");
        Expression expr = Expression.Equal(column, value);

        // expressão 'cliente.CPF == 100'
        column = Expression.Property(pe, typeof(Cliente).GetProperty("CPF"));
        value = Expression.Constant(100, typeof(int));
        Expression expr2 = Expression.Equal(column, value);

        // expressão '(cliente.Nome == "Mario" || cliente.CPF == 100)'
        Expression predicateBody = Expression.OrElse(expr, expr2);

        // expressão 'queryableData.Where(cliente => 
        // (cliente.Nome == "Mario" || cliente.CPF == 100))'
        MethodCallExpression whereCallExpression = Expression.Call(
            typeof(Queryable),
            "Where",
            new Type[] { queryableData.ElementType },
            queryableData.Expression,
            Expression.Lambda<Func<Cliente, bool>>(predicateBody, 
                                                   new ParameterExpression[] { pe }));

        // expressão 'cliente.RG'
        Expression expr3 = Expression.Property(pe, typeof(Cliente).GetProperty("RG")); 

        // expressão 'whereCallExpression.OrderBy(cliente => cliente.RG)'
        MethodCallExpression orderByCallExpression = Expression.Call(
            typeof(Queryable),
            "OrderBy",
            new Type[] { queryableData.ElementType, typeof(int) },
            whereCallExpression,
            Expression.Lambda<Func<Cliente, int>>(expr3, new ParameterExpression[] { pe }));

        // cria e executa a query
        IQueryable<Cliente> results = queryableData.Provider
                                      .CreateQuery<Cliente>(orderByCallExpression);

        // imprime resultado
        Console.WriteLine("Nome\tRG\tCPF");
        foreach (Cliente cliente in results)
            Console.Write(cliente.ToString());
    }

}

The result is:

Nome    RG      CPF
Mario   10      19
Maria   159     100
Mario   737     888
Mario   963     222

You can also create a generic method:

public static IQueryable<T> Find<T>(List<T> list, string column, object value, 
                                    string columnOrder)
{
    // recupera o IQueryable da lista
    IQueryable<T> queryable = list.AsQueryable<T>();

    // cria o parâmetro a ser utilizado na consulta
    ParameterExpression pe = Expression.Parameter(typeof(T), "param");

    // expressão 'param.column == value'
    Expression columnExp = Expression.Property(pe, typeof(T).GetProperty(column));
    Expression valueExp = Expression.Constant(value);
    Expression expr = Expression.Equal(columnExp, valueExp);

    // expressão 'queryable.Where(param => param.column == value)'
    MethodCallExpression whereCallExpression = Expression.Call(
        typeof(Queryable),
        "Where",
        new Type[] { queryable.ElementType },
        queryable.Expression,
        Expression.Lambda<Func<T, bool>>(expr, new ParameterExpression[] { pe }));

    // expressão 'param.columnOrder'
    Expression expr2 = Expression.Property(pe, typeof(T).GetProperty(columnOrder));

    // expressão 'whereCallExpression.OrderBy(param => param.columnOrder)'
    MethodCallExpression orderByCallExpression = Expression.Call(
        typeof(Queryable),
        "OrderBy",
        new Type[] { queryable.ElementType, expr2.Type },
        whereCallExpression,
        Expression.Lambda(expr2, new ParameterExpression[] { pe }));

    // cria e executa a query
    IQueryable<T> results = queryable.Provider.CreateQuery<T>(orderByCallExpression);

    return results;
}

To use it, enter the parameters as shown below:

IQueryable<Cliente> results = Find(listClientes, "Nome", "Mario", "CPF");

Resulting in:

Nome    RG  CPF
Mario   10  19
Mario   963 222
Mario   737 888

If you need to access more object attributes or call some method, you can do this:

// exemplo base, considerando que a classe Cliente possui o atributo Endereco,
// que por sua vez possui o atributo Logradouro, teríamos algo assim:
// param.Endereco.Logradouro.ToLower() == "avenida paulista"
// que pode ser feito conforme mostrado abaixo

[...]

// cria o parâmetro a ser utilizado na consulta
ParameterExpression pe = Expression.Parameter(typeof(Cliente), "param");

// expressão 'param.Endereco'
Expression columnEndereco = Expression.Property(pe, typeof(Cliente).GetProperty("Endereco"));
// expressão 'param.Endereco.Logradouro'
Expression columnLogradouro = Expression.Property(columnEndereco, typeof(Endereco)
    .GetProperty("Logradouro"));
// expressão 'param.Endereco.Logradouro.ToLower()'
Expression columnToLower = Expression.Call(columnLogradouro, typeof(string).GetMethod("ToLower"));
Expression columnValue = Expression.Constant("avenida paulista");
// expressão 'param.Endereco.Logradouro.ToLower() == "avenida paulista"'
Expression expr = Expression.Equal(columnToLower, columnValue);

[...]

I think with that you have already got a basis to do what you need.

Extras

Expression Members > How to compose Linq Expression to call OrderBy on a set of entities?
Generic Methods (C # Programming Guide)

    
16.01.2015 / 00:32
0

Let me see if I understand: you want to filter a generic List object (List) using lambda? For example, given a list of numbers, return only the pairs; or, given a list of objects of type Person, return only the elements where Person.Sexo is equal to "F"? If so, do not need reflection, just use the Where method:

// Para List<Int32> filtrando somente os pares
lista.Where(x => x % 2 == 0).ToList()

// Para List<Pessoa> filtrando somente pessoas do sexo feminino.
lista.Where(x => x.Sexo == "F").ToList()
    
14.01.2015 / 19:53