I need to mount a where clause with the field and the dynamic value, for example, in pure SQL would be:
WHERE :CAMPO = :VALOR
However, scouring the queries I could not find anything specific, I'm already doing queries in the database with a where clause fixed without problems. I can not dynamically change, my code snippet is as follows:
string campo = "NOME";
string valor = "JOAO";
listEmpresa = db.EMPRESA.Where(...).ToList();
As you want everything dynamic, there is the great System.Linq.Dynamic package that does this for you. There it would be:
listEmpresa = db.EMPRESA.Where(campo + "==@0", valor).ToList();
I'm working on this currently, and I recommend using System.Linq.Expressions.Expression , Lambda , and a little Reflection for this.
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);
EXAMPLE Equals
// 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);
Continuation code ...
// 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
You can also extend System.Linq by adding WhereIf . I used it once, and it works bacaninha.
First add the extension in 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 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;
}