Where dynamic Linq to SQL

Question

I need to build a clause where with the field and dynamic value, for example, in pure SQL would be:

WHERE :CAMPO = :VALOR

However, searching the research I could not find anything specific, I’m already performing queries in the database with a clause where fixed without problems. I cannot change dynamically, my code snippet is as follows:

string campo = "NOME";
string valor = "JOAO";

listEmpresa = db.EMPRESA.Where(...).ToList();

Answer 1

I am working with this currently, and recommend using System.Linq.Expressions.Expression, Lambda and a little bit of Reflection for that reason.

Example:

string campo = "Campo";
string valor = "valor";

// recupera o objeto IQueryable da Context do EF
IQueryable<Empresa> query = db.EMPRESA;
// cria alias do objecto Lambda
ParameterExpression param = Expression.Parameter(typeof(Empresa), "x");
// obtem tipo da propriedade
Type type = typeof(Empresa).GetProperty(campo).PropertyType;
// cria Expression para o campo
MemberExpression propertyExpression = Expression.PropertyOrField(param, campo);
// cria Expression para o valor
ConstantExpression valueExpression = Expression.Constant(Convert.ChangeType(valor, type), type);

EQUALS EXAMPLE

// cria predicate Lambda Expression
var predicate = Expression.Lambda<Func<Empresa, bool>>(
    // aplica tipo de Filtro, no caso do exemplo Equal (campo == valor)
    Expression.Equal(propertyExpression, valueExpression)
, param);

EXAMPLE Contains (LIKE)

MethodInfo methodInfo = type.GetMethod("Contains", new[] { type });            
var predicate = Expression.Lambda<Func<Empresa, bool>>(
    // aplica tipo de Filtro, no caso do exemplo Contains (LIKE campo '%valor%')
    Expression.Call(propertyExpression, methodInfo, valueExpression)
, param);

Code continuation...

// adiciona predicate ao where da query
query = query.Where(predicate);
// executa a consulta no banco de dados
var result = query.ToList();

Source: Use string as field name in LINQ

Answer 2

As you want everything dynamic, there is the great package System.Linq.Dynamic who does it for you. Then with him it would be:

listEmpresa = db.EMPRESA.Where(campo + "==@0", valor).ToList();

Answer 3

You can also extend the System.Linq adding a WhereIf. I used it once, and it works fine.

First add extension to your project:

public static class LinqExtensions
{
    public static IQueryable<TSource> WhereIf<TSource>(
        this IQueryable<TSource> source, bool condition,
        Expression<Func<TSource, bool>> predicate)
    {
        if (!condition) return source;
        return source.Where(predicate);            
    }
}

Then use it wisely:

// a clausula WHERE somente será aplicado se variável 'condicao' for true
var resultado = MeusDados
                    .WhereIf(condicao == true, dado => dado.campo == "valor")
                    .ToList();

UPDATE:

Application example:

[HttpGet]
public async Task<IEnumerable<pessoa>> BuscarPessoas(string nome, int idade, DateTime dataNascimento)
{
    var pessoas = await Task.FromResult(_contexto.Pessoas
                      .WhereIf(!string.IsNullOrEmpty(nome), p => p.Nome.Contains(nome))
                      .WhereIf(idade > 0, p => p.Idade == idade)
                      .WhereIf(dataNascimento != null, p => p.DataNascimento = dataNascimento)
                      .ToList();
    return pessoas;
}